1 PACKAGE "AHL_PRD_PRINT_PVT" AUTHID CURRENT_USER AS
2 /* $Header: AHLVPPRS.pls 120.7.12020000.2 2012/12/07 11:41:58 sareepar ship $ */
3
4 TYPE WORKORDER_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6 ------------------------------------------------------------------------------------------------
7 -- Procedure to generate XML data for workorder(s).
8 ------------------------------------------------------------------------------------------------
9 -- Start of Comments
10 -- Procedure name :
11 -- Type : Private
12 -- Pre-reqs :
13 -- Function : Gen_Wo_Xml
14 -- Parameters :
15 --
16 -- Standard IN Parameters :
17 -- p_api_version NUMBER Required
18 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
19 -- p_commit VARCHAR2 Default FND_API.G_FALSE
20 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
21 -- p_default VARCHAR2 Default FND_API.G_TRUE
22 -- p_module_type VARCHAR2 Default NULL
23 --
24 -- Standard OUT Parameters :
25 -- x_return_status VARCHAR2 Required
26 -- x_msg_count NUMBER Required
27 -- x_msg_data VARCHAR2 Required
28 --
29 -- IN parameters:
30 -- p_workorder_id NUMBER Required
31 -- p_visit_id NUMBER Required
32 --
33 -- IN OUT parameters:
34 -- None
35 --
36 -- OUT parameters:
37 -- x_xml_data CLOB
38 --
39 -- Version :
40 -- Current version 1.0
41 --
42 -- Return Parameter type
43 -- CLOB
44 -- End of Comments
45
46 PROCEDURE Gen_Wo_Xml(
47 p_api_version IN NUMBER :=1.0,
48 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
49 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
50 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
51 p_default IN VARCHAR2 :=FND_API.G_FALSE,
52 p_module_type IN VARCHAR2 :=NULL,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2,
56 p_workorders_tbl IN WORKORDER_TBL_TYPE,
57 p_employee_id IN NUMBER,
58 p_user_role IN VARCHAR2,-- required for resource transactions
59 p_material_req_flag IN VARCHAR2 := 'N',--not required any more
60 x_xml_data OUT NOCOPY CLOB,
61 p_concurrent_flag IN VARCHAR2 := 'N'-- pass as N non concurrent programs
62 );
63
64 ------------------------------------------------------------------------------------------------
65 -- Procedure to generate XML data for Workcard concurrent program
66 ------------------------------------------------------------------------------------------------
67 -- Start of Comments
68 -- Procedure name :
69 -- Type : Private
70 -- Pre-reqs :
71 -- Function : Gen_Workcard_Xml
72 -- Parameters :
73 --
74 -- Standard IN Parameters :
75 -- p_api_version NUMBER Required
76 --
77 -- Standard OUT Parameters :
78 -- errbuf OUT NOCOPY VARCHAR2,
79 -- retcode OUT NOCOPY NUMBER,
80 --
81 -- IN parameters:
82 -- p_visit_id IN NUMBER Required
83 -- p_stage_id IN NUMBER Required
84 -- p_wo_no_from IN VARCHAR2 Required
85 -- p_wo_no_to IN VARCHAR2 Required
86 -- p_sch_start_from IN VARCHAR2 Required
87 -- p_sch_start_to IN VARCHAR2 Required
88 -- p_employee_id IN NUMBER Required
89 --
90 -- IN OUT parameters:
91 -- None
92 --
93 -- OUT parameters:
94 -- None.
95 --
96 -- Version :
97 -- Current version 1.0
98 --
99 -- Return Parameter
100 -- CLOB
101 -- End of Comments
102
103 PROCEDURE Gen_Workcard_Xml(
104 errbuf OUT NOCOPY VARCHAR2,
105 retcode OUT NOCOPY NUMBER,
106 p_api_version IN NUMBER,
107 p_visit_id IN NUMBER,
108 p_stage_id IN NUMBER,
109 p_wo_no_from IN VARCHAR2,
110 p_wo_no_to IN VARCHAR2,
111 p_sch_start_from IN VARCHAR2,
112 p_sch_start_to IN VARCHAR2,
113 p_employee_id IN NUMBER
114 );
115
116 ------------------------------------------------------------------------------------------------
117 -- Procedure to Process the Job Card Generation Request.
118 ------------------------------------------------------------------------------------------------
119 -- Start of Comments
120 -- Procedure name :
121 -- Type : Private
122 -- Pre-reqs :
123 -- Function : Process_JCG_Request
124 -- Parameters :
125 --
126 -- Standard IN Parameters :
127 -- p_api_version NUMBER Required
128 --
129 -- Standard OUT Parameters :
130 -- x_return_status VARCHAR2 Required
131 -- x_msg_count NUMBER Required
132 -- x_msg_data VARCHAR2 Required
133 --
134 -- IN parameters:
135 -- p_operation_flag IN VARCHAR2 Required,
136 -- p_x_jcg_id IN OUT NOCOPY NUMBER Not Required for Create,
137 -- p_job_card_id IN NUMBER ,
138 -- p_object_type IN VARCHAR2 Required for Create,
139 -- p_object_id IN NUMBER Required for Create,
140 -- p_status_code IN VARCHAR2 Required,
141 -- p_file_location IN Varchar2,
142 -- p_split_flag IN VARCHAR2,
143 -- p_xml_data_filename IN VARCHAR2 Required for Create,
144 --
145 -- IN OUT parameters:
146 -- p_x_jcg_id : For update, this value would be required.
147 --
148 -- OUT parameters:
149 -- None.
150 --
151 -- Version :
152 -- Current version 1.0
153 --
154 -- Procedure Process_JCG_Request will be called from an internal API to insert a new row into a table.
155 -- This process will also be called to updated an existing row.
156 -- End of Comments
157
158 PROCEDURE Process_JCG_Request(
159 p_api_version IN NUMBER :=1.0,
160 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
161 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
162 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
163 p_default IN VARCHAR2 :=FND_API.G_FALSE,
164 p_module_type IN VARCHAR2 :=NULL,
165 x_return_status OUT NOCOPY VARCHAR2,
166 x_msg_count OUT NOCOPY NUMBER,
167 x_msg_data OUT NOCOPY VARCHAR2,
168 p_operation_flag IN VARCHAR2,
169 p_x_jcg_id IN OUT NOCOPY NUMBER,
170 p_object_type IN VARCHAR2 := NULL,
171 p_object_id IN NUMBER := NULL,
172 p_status_code IN VARCHAR2,
173 p_file_location IN Varchar2 := NULL,
174 p_split_flag IN VARCHAR2 := 'N',
175 p_xml_data_filename IN VARCHAR2 := NULL,
176 p_error_message IN VARCHAR2 := NULL,
177 p_concurrent_request_id IN NUMBER := NULL
178 );
179
180 ------------------------------------------------------------------------------------------------
181 -- Procedure to create the xml data for jobcard.
182 ------------------------------------------------------------------------------------------------
183 -- Start of Comments
184 -- Procedure name : Generate_JCG_XML
185 -- Type : Private
186 -- Parameters :
187 --
188 -- Standard IN Parameters :
189 -- p_api_version NUMBER Required
190 -- p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE
191 -- p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
192
193 -- Standard OUT Parameters :
194 -- x_return_status VARCHAR2 Required
195 -- x_msg_count NUMBER Required
196 -- x_msg_data VARCHAR2 Required
197 --
198 -- IN parameters:
199 -- p_object_type IN VARCHAR2 Required for Create,
200 -- p_object_id IN NUMBER Required for Create,
201 -- p_split_flag IN VARCHAR2,
202 --
203 -- IN OUT parameters:
204 -- None.
205 --
206 -- OUT parameters:
207 -- x_file_name OUT NOCOPY VARCHAR2 name of file created
208 --
209 -- Version :
210 -- Current version 1.0
211 --
212 -- Procedure Process_JCG_Request will be called from an internal API to insert a new row into a table.
213 -- This process will also be called to updated an existing row.
214 -- End of Comments
215
216 PROCEDURE Generate_JCG_XML
217 (p_api_version IN NUMBER :=1.0,
218 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
219 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
220 p_object_type IN VARCHAR2,
221 p_object_id IN NUMBER,
222 p_split_flag IN VARCHAR2,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_data OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER ,
226 x_file_name OUT NOCOPY VARCHAR2
227 );
228 ------------------------------------------------------------------------------------------------
229 -- FUNCTION to Know if the Job Card is Generated for a given Object
230 ------------------------------------------------------------------------------------------------
231 -- Start of Comments
232 -- Procedure name :
233 -- Type : Private
234 -- Pre-reqs :
235 -- Function : Generated_JobCard_Exists
236 -- Parameters :
237 -- IN parameters:
238 -- p_object_type IN VARCHAR2 Required,
239 -- p_object_id IN NUMBER Required
240 -- OUT parameters:
241 -- x_jcg_exists OUT NOCOPY VARCHAR2 Required,
242 -- This parameter will have value 'Y' is a job card exists for the particular Object. Else 'N'.
243 -- Version :
244 -- Current version 1.0
245 --
246 -- Procedure Job_Card_Generated_Exists will be called to notify the calling API of the existance of the Job Card belonging to a particular Object (WO, VISIT, MR etc).
247 --
248 -- End of Comments
249
250 FUNCTION Generated_JobCard_Exists(
251 p_object_type IN VARCHAR2,
252 p_object_id IN NUMBER
253 ) RETURN VARCHAR2;
254
255 ------------------------------------------------------------------------------------------------
256 -- FUNCTION to get file location of the generated Job Card
257 ------------------------------------------------------------------------------------------------
258 -- Start of Comments
259 -- Procedure name :
260 -- Type : Private
261 -- Pre-reqs :
262 -- Function : Get_JC_File_Location
263 -- Parameters :
264 -- IN parameters:
265 -- p_object_type IN VARCHAR2 Required,
266 -- p_object_id IN NUMBER Required
267 --
268 -- OUT parameters:
269 -- x_file_location OUT NOCOPY VARCHAR2 Required,
270 --
271 -- Version :
272 -- Current version 1.0
273 --
274 -- Procedure Get_JC_File_Location will be giving back the file location stored in the table for a particular Object (WO, VISIT, MR etc).
275 -- In case when Job Card is generated at Visit level with Split Flag = 'Y', the corresponding WOs belonging to the visit will also have their respective job cards generated.
276 -- In any case, latest job card generated details will be given back.
277 --
278 -- End of Comments
279
280 FUNCTION Get_JC_File_Location(
281 p_object_type IN VARCHAR2,
282 p_object_id IN NUMBER
283 ) RETURN VARCHAR2;
284
285 FUNCTION Get_Latest_JCG_Status(
286 p_object_type IN VARCHAR2,
287 p_object_id IN NUMBER
288 )
289 RETURN VARCHAR2;
290
291 FUNCTION Get_JCG_Error_Msg(
292 p_object_type IN VARCHAR2,
293 p_object_id IN NUMBER
294 )
295 RETURN VARCHAR2;
296
297
298
299 FUNCTION get_tz_offset
300 RETURN VARCHAR2;
301
302 --- Function to handle Enigma JCG Web Service response
303 FUNCTION HANDLE_ENIGMAJCG_RESP(p_subscription_guid in raw,
304 p_event in out NOCOPY WF_EVENT_T) return varchar2;
305
306 --- Function to handle Enigma JCG Web Service faults
307 FUNCTION HANDLE_ENIGMAJCG_FAULTS(p_subscription_guid in raw,
308 p_event in out NOCOPY WF_EVENT_T) return varchar2;
309
310 --- Function to handle Enigma JCG Web Service response from BPEL
311 FUNCTION HANDLE_JCG_RESPONSE(p_request_id IN VARCHAR2,
312 p_enigma_response IN CLOB) return varchar2;
313
314 --- Function to handle Enigma JCG Web Service flow errors
315 FUNCTION HANDLE_JCG_ERRORS(p_request_id IN VARCHAR2,
316 p_error_msg in VARCHAR2) return varchar2;
317
318
319 -- Start of Comments --
320 -- Procedure name : generate_jcg_request
321 -- Type : Private/called from concurrent program
322 -- Function : Creates Job Cards for CMRO Visits, MRs or Work Order. Prepares the XML data
323 -- and fires the request to Enigma Web Service using Oracle Workflow Event.
324 -- Pre-reqs :
325
326 -- generate_jcg_request Parameters :
327 -- p_object_type - idenifier for object type i.e VISIT, MR, WORKORDER
328 -- p_object_id - unique key for corresponding visit id, unit_effectivity_id or work order id
329 -- p_user_id FND user id,
330 -- p_resp_id Responsibility ID);
331
332 PROCEDURE Generate_JCG_Request(
333 errbuf OUT NOCOPY VARCHAR2,
334 retcode OUT NOCOPY NUMBER,
335 p_jcg_id IN NUMBER,
336 p_user_id IN NUMBER,
337 p_resp_id IN NUMBER);
338
339 -- Start of Comments --
340 -- Procedure name : print_workcard
341 -- Type : Private
342 -- Function : Handles front end requests to print Job Cards for CMRO Visits, MRs or Work Order
343 -- Pre-reqs :
344 -- Parameters :
345 --
346 -- Standard IN Parameters :
347 -- p_api_version IN NUMBER Required
348 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
349 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
350 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
351 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
352 -- Based on this flag, the API will set the default attributes.
353 -- p_module_type In VARCHAR2 Default NULL
354 -- This will be null.
355 -- Standard OUT Parameters :
356 -- x_return_status OUT VARCHAR2 Required
357 -- x_msg_count OUT NUMBER Required
358 -- x_msg_data OUT VARCHAR2 Required
359 --
360 -- print_workcard Parameters :
361 -- p_object_type - idenifier for object type i.e VISIT, MR, WORKORDER
362 -- p_object_id - unique key for corresponding visit id, unit_effectivity_id or work order id
363
364 PROCEDURE print_workcard (
365 p_api_version IN NUMBER :=1.0,
366 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
367 p_commit IN VARCHAR2 := FND_API.G_FALSE,
368 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
369 p_default IN VARCHAR2 := FND_API.G_TRUE,
370 p_module_type IN VARCHAR2 := NULL,
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_msg_count OUT NOCOPY NUMBER,
373 x_msg_data OUT NOCOPY VARCHAR2,
374 p_object_type IN VARCHAR2,
375 p_object_id IN NUMBER,
376 p_split_flag IN VARCHAR2 := 'N');
377
378 -- method to appp_xsl_full_path IN VARCHAR2,ly transformation on XML clob
379 PROCEDURE APPLY_TRANSFORMATION(
380 p_indoc IN CLOB,
381 --p_xsl_full_path IN VARCHAR2,
382 p_xsl_doc IN CLOB,
383 p_xml_output_dir IN VARCHAR2,
384 p_xml_output_filename IN VARCHAR2,
385 x_return_status OUT NOCOPY VARCHAR2,
386 x_msg_count OUT NOCOPY NUMBER,
387 x_msg_data OUT NOCOPY VARCHAR2
388 );
389
390 -- overloaded method to apply transformation on file
391 PROCEDURE APPLY_TRANSFORMATION(
392 p_xml_input_fullpath IN VARCHAR2,
393 p_xsl_doc IN CLOB,
394 p_xml_output_dir IN VARCHAR2,
395 p_xml_output_filename IN VARCHAR2,
396 x_return_status OUT NOCOPY VARCHAR2,
397 x_msg_count OUT NOCOPY NUMBER,
398 x_msg_data OUT NOCOPY VARCHAR2
399 );
400
401 FUNCTION get_xsl_clob RETURN CLOB;
402
403 END AHL_PRD_PRINT_PVT;
[Home] [Help]