1 PACKAGE AHL_LTP_MATRL_AVAL_PUB AS
2 /* $Header: AHLPMTAS.pls 120.1 2007/12/31 11:40:35 rnahata ship $ */
3 --
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 -- anraj : added columns TASK_STATUS_CODE and TASK_STATUS_MEANING , for Material Availabilty UI
8 TYPE Material_Availability_Rec IS RECORD (
9 SCHEDULE_MATERIAL_ID NUMBER ,
10 OBJECT_VERSION_NUMBER NUMBER ,
11 INVENTORY_ITEM_ID NUMBER ,
12 ITEM VARCHAR2(80) ,
13 MR_ROUTE_ID NUMBER ,
14 VISIT_ID NUMBER ,
15 VISIT_TASK_ID NUMBER ,
16 ORGANIZATION_ID NUMBER ,
17 TASK_NAME VARCHAR2(80) ,
18 TASK_STATUS_CODE VARCHAR2(30) ,
19 TASK_STATUS_MEANING VARCHAR2(80) ,
20 REQ_ARRIVAL_DATE DATE ,
21 QUANTITY_AVAILABLE NUMBER ,
22 QUANTITY NUMBER ,
23 SCHEDULED_DATE DATE ,
24 UOM VARCHAR2(30) ,
25 ERROR_CODE NUMBER ,
26 ERROR_MESSAGE VARCHAR2(2000) ,
27 PLAN_NAME VARCHAR2(30)
28 );
29 -- anraj : added columns TASK_STATUS_CODE and TASK_STATUS_MEANING , for Material Availabilty UI
30 TYPE Task_Req_Matrl_Rec IS RECORD (
31 SCHEDULE_MATERIAL_ID NUMBER ,
32 OBJECT_VERSION_NUMBER NUMBER ,
33 VISIT_TASK_ID NUMBER ,
34 TASK_NAME VARCHAR2(80) ,
35 TASK_STATUS_CODE VARCHAR2(30) ,
36 TASK_STATUS_MEANING VARCHAR2(80) ,
37 MR_ROUTE_ID NUMBER ,
38 INVENTORY_ITEM_ID NUMBER ,
39 ITEM_GROUP_ID NUMBER ,
40 ITEM VARCHAR2(80) ,
41 REQ_ARRIVAL_DATE DATE ,
42 UOM_CODE VARCHAR2(30) ,
43 QUANTITY_AVAILABLE NUMBER ,
44 SCHEDULED_DATE DATE ,
45 PLANNED_ORDER VARCHAR2(240) ,
46 QUANTITY NUMBER
47 );
48
49 TYPE Planned_Matrl_Rec IS RECORD (
50 SCHEDULE_MATERIAL_ID NUMBER ,
51 OBJECT_VERSION_NUMBER NUMBER ,
52 INVENTORY_ITEM_ID NUMBER ,
53 ITEM_DESCRIPTION VARCHAR2(80) ,
54 VISIT_ID NUMBER ,
55 VISIT_TASK_ID NUMBER ,
56 ORGANIZATION_ID NUMBER ,
57 PLANNED_ORDER VARCHAR2(240) ,
58 TASK_NAME VARCHAR2(80) ,
59 TASK_STATUS_CODE VARCHAR2(30) ,
60 TASK_STATUS_MEANING VARCHAR2(80) ,
61 REQUESTED_DATE DATE ,
62 REQUIRED_QUANTITY NUMBER ,
63 QUANTITY_AVAILABLE NUMBER ,
64 SCHEDULED_DATE DATE ,
65 PRIMARY_UOM_CODE VARCHAR2(3) ,
66 PRIMARY_UOM VARCHAR2(25) ,
67 -- Added by sowsubra
68 MAT_STATUS AHL_SCHEDULE_MATERIALS.STATUS%TYPE,
69 ERROR_CODE NUMBER ,
70 ERROR_MESSAGE VARCHAR2(2000)
71 );
72
73 ----------------------------------------------
74 -- Define Table Type for records structures --
75 ----------------------------------------------
76 TYPE Material_Availability_Tbl IS TABLE OF Material_Availability_Rec
77 INDEX BY BINARY_INTEGER;
78 TYPE Task_Req_Matrl_Tbl IS TABLE OF Task_Req_Matrl_Rec
79 INDEX BY BINARY_INTEGER;
80 TYPE Planned_Matrl_Tbl IS TABLE OF Planned_Matrl_Rec
81 INDEX BY BINARY_INTEGER;
82
83 ------------------------
84 -- Declare Procedures --
85 ------------------------
86
87 -- Start of Comments --
88 -- Procedure name : Check_Material_Aval
89 -- Type : Public
90 -- Function : This procedure calls ATP to check inventory item is available
91 -- for Routine jobs derived requested quantity and task start date
92 -- Pre-reqs :
93 -- Parameters :
94 --
95 -- Standard IN Parameters :
96 -- p_api_version IN NUMBER Required
97 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
98 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
99 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
100 -- Based on this flag, the API will set the default attributes.
101 -- p_module_type In VARCHAR2 Default NULL
102 -- This will be null.
103 -- Standard OUT Parameters :
104 -- x_return_status OUT VARCHAR2 Required
105 -- x_msg_count OUT NUMBER Required
106 -- x_msg_data OUT VARCHAR2 Required
107 --
108 -- Check_Material_Aval Parameters :
109 -- p_x_material_avl_tbl IN OUT NOCOPY Material_Availability_Tbl,Required
110 -- List of item attributes associated to visit task
111 --
112 PROCEDURE Check_Material_Aval (
113 p_api_version IN NUMBER,
114 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
115 p_commit IN VARCHAR2 := FND_API.g_false,
116 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
117 p_module_type IN VARCHAR2 := 'JSP',
118 p_x_material_avl_tbl IN OUT NOCOPY Material_Availability_Tbl,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2
122 );
123
124 -- Start of Comments --
125 -- Procedure name : Get_Visit_Task_Materials
126 -- Type : Public
127 -- Function : This procedure derives material information associated to scheduled
128 -- visit, which are defined at Route Operation level
129 -- Pre-reqs :
130 -- Parameters :
131 --
132 -- Standard IN Parameters :
133 -- p_api_version IN NUMBER Required
134 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
135 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
136 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
137 -- Based on this flag, the API will set the default attributes.
138 -- p_module_type In VARCHAR2 Default NULL
139 -- This will be null.
140 -- Standard OUT Parameters :
141 -- x_return_status OUT VARCHAR2 Required
142 -- x_msg_count OUT NUMBER Required
143 -- x_msg_data OUT VARCHAR2 Required
144 --
145 -- Get_Visit_Task_Materials :
146 -- p_visit_id IN NUMBER,Required
147 -- x_task_req_matrl_tbl OUT NOCOPY Task_Req_Matrl_Tbl,
148 --
149 PROCEDURE Get_Visit_Task_Materials (
150 p_api_version IN NUMBER,
151 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
152 p_commit IN VARCHAR2 := FND_API.g_false,
153 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
154 p_module_type IN VARCHAR2 := 'JSP',
155 p_visit_id IN NUMBER,
156 x_task_req_matrl_tbl OUT NOCOPY Task_Req_Matrl_Tbl,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2
160 );
161
162 -- Start of Comments --
163 -- Procedure name : Check_Materials_For_All
164 -- Type : Public
165 -- Function : This procedure calls ATP to check inventory item is available
166 -- for Routine jobs associated to a visit
167 -- Pre-reqs :
168 -- Parameters :
169 --
170 -- Standard IN Parameters :
171 -- p_api_version IN NUMBER Required
172 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
173 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
174 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
175 -- Based on this flag, the API will set the default attributes.
176 -- p_module_type In VARCHAR2 Default NULL
177 -- This will be null.
178 -- Standard OUT Parameters :
179 -- x_return_status OUT VARCHAR2 Required
180 -- x_msg_count OUT NUMBER Required
181 -- x_msg_data OUT VARCHAR2 Required
182 --
183 -- Check_Materials_For_All Parameters :
184 -- p_visit_id IN NUMBER, Required
185 -- x_material_avl_tbl OUT NOCOPY Material_Availability_Tbl,
186 -- List of item attributes associated to visit task
187 --
188 PROCEDURE Check_Materials_For_All (
189 p_api_version IN NUMBER,
190 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
191 p_commit IN VARCHAR2 := FND_API.g_false,
192 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
193 p_module_type IN VARCHAR2 := 'JSP',
194 p_visit_id IN NUMBER,
195 x_task_matrl_aval_tbl OUT NOCOPY Material_Availability_Tbl,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2
199 );
200
201 -- Start of Comments --
202 -- Procedure name : Schedule_Planned_Mtrls
203 -- Type : Public
204 -- Function : This procedure calls ATP to schedule planned materials
205 -- for Routine jobs derived requested quantity and task start date
206 -- Pre-reqs :
207 -- Parameters :
208 --
209 -- Standard IN Parameters :
210 -- p_api_version IN NUMBER Required
211 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
212 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
213 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
214 -- Based on this flag, the API will set the default attributes.
215 -- p_module_type In VARCHAR2 Default NULL
216 -- This will be null.
217 -- Standard OUT Parameters :
218 -- x_return_status OUT VARCHAR2 Required
219 -- x_msg_count OUT NUMBER Required
220 -- x_msg_data OUT VARCHAR2 Required
221 --
222 -- Schedule_Planned_Matrls Parameters :
223 -- p_x_planned_matrls_tbl IN OUT NOCOPY Planned_Matrls_Tbl,Required
224 -- List of item attributes associated to visit task
225 --
226 PROCEDURE Schedule_Planned_Matrls (
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_validation_level IN NUMBER := FND_API.g_valid_level_full,
231 p_module_type IN VARCHAR2 := 'JSP',
232 p_x_planned_matrl_tbl IN OUT NOCOPY Planned_Matrl_Tbl,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2
236 );
237
238 -- Start of Comments --
239 -- Procedure name : Schedule_All_Materials
240 -- Type : Public
241 -- Function : This procedure calls ATP to schedule planned materials
242 -- for Routine jobs derived requested quantity and task start date
243 -- Pre-reqs :
244 -- Parameters :
245 --
246 -- Standard IN Parameters :
247 -- p_api_version IN NUMBER Required
248 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
249 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
250 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
251 -- Based on this flag, the API will set the default attributes.
252 -- p_module_type In VARCHAR2 Default NULL
253 -- This will be null.
254 -- Standard OUT Parameters :
255 -- x_return_status OUT VARCHAR2 Required
256 -- x_msg_count OUT NUMBER Required
257 -- x_msg_data OUT VARCHAR2 Required
258 --
259 -- Schedule_All_Materials Parameters :
260 -- p_visit_id IN NUMBER Required,
261 -- List of item attributes associated to visit task
262 --
263 PROCEDURE Schedule_All_Materials (
264 p_api_version IN NUMBER,
265 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
266 p_commit IN VARCHAR2 := FND_API.g_false,
270 x_planned_matrl_tbl OUT NOCOPY Planned_Matrl_Tbl,
267 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
268 p_module_type IN VARCHAR2 := 'JSP',
269 p_visit_id IN NUMBER,
271 x_return_status OUT NOCOPY VARCHAR2,
272 x_msg_count OUT NOCOPY NUMBER,
273 x_msg_data OUT NOCOPY VARCHAR2
274 );
275
276 END AHL_LTP_MATRL_AVAL_PUB;
[Home] [Help]