[Home] [Help]
PACKAGE BODY: APPS.PO_EDA_IDX_FILE_GEN
Source
1 PACKAGE BODY po_eda_idx_file_gen AS
2 /* $Header: POEDAIXB.pls 120.7.12020000.3 2013/02/10 20:54:09 vegajula ship $ */
3
4 PROCEDURE gen_eda_idx
5 (
6 errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 P_IS_MOD IN VARCHAR2,
9 p_clm_document_number IN VARCHAR,
10 p_timestamp IN VARCHAR2,
11 p_system_name IN varchar2
12 ) AS
13
14
15 CURSOR c_getmoddetails IS
16 SELECT h.po_header_id documentId, h.draft_id draftId
17 FROM po_headers_draft_all h, po_drafts d
18 WHERE d.modification_number = p_clm_document_number
19 AND d.document_id = h.po_header_id
20 AND d.draft_id = h.draft_id;
21
22 CURSOR c_getdocdetails IS
23 SELECT po_header_id documentId
24 FROM po_headers_all
25 WHERE clm_document_number = p_clm_document_number;
26
27 TransactionCode VARCHAR2(1) := 'A';
28 SystemName VARCHAR2(8):= p_system_name;
29 RecordID NUMBER;
30 ContractNumber VARCHAR2(19);
31 DeliveryOrder VARCHAR2(19);
32 ACOMod VARCHAR2(6);
33 PCOMod VARCHAR2(6);
34 IssueDate DATE;
35 IssueDateInChar varchar2(100);
36 IssuingDoDAAC VARCHAR2(6);
37 AdminDoDAAC VARCHAR2(6);
38 PayingDoDAAC VARCHAR2(6);
39 CAGECode VARCHAR2(5);
40 DUNS VARCHAR2(20);
41 IndexDate varchar2(100);
42
43
44 CURSOR c_isorderoffidv IS
45 SELECT 'Y'
46 FROM po_headers_all
47 WHERE clm_award_type IN ('BPA_CALL','DELIVERY_ORD')
48 AND po_header_id = p_doc_id;
49
50 CURSOR c_issuedate IS
51 SELECT CLM_EFFECTIVE_DATE
52 FROM PO_HEADERS_ALL
53 WHERE po_header_id = p_doc_id;
54
55 l_isorderoffidv VARCHAR2(100):='N';
56 l_modiftype VARCHAR2(100);
57 l_address_type VARCHAR2(50);
58 l_timestamp VARCHAR2(100);
59 v_out_file UTL_FILE.FILE_TYPE;
60 v_out_dir varchar2(2000);
61
62 BEGIN
63
64 l_timestamp := p_timestamp;
65 IF l_timestamp IS NULL THEN
66 l_timestamp := To_Char(SYSDATE,'yyyyMMddHHmmssSSS');
67 END IF;
68
69
70 SELECT Trim(FND_PROFILE.VALUE('PO_EDA_FILES_LOCATION')) INTO v_out_dir FROM dual;
71 v_out_file := UTL_FILE.FOPEN(v_out_dir,p_clm_document_number||'_'||l_timestamp||'.idx', 'w',32000);
72
73 fnd_file.put_line(FND_FILE.LOG,' ');
74 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
75 fnd_file.put_line(FND_FILE.LOG,'Output Directory: ' || v_out_dir);
76 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
77 fnd_file.put_line(FND_FILE.LOG,'CLM Document Number '||p_clm_document_number);
78 fnd_file.put_line(FND_FILE.LOG,'Is Mod '||P_IS_MOD);
79 fnd_file.put_line(FND_FILE.LOG,'System Name '||p_system_name);
80
81
82 p_draft_id := -1;
83
84 IF P_IS_MOD = 'Y' THEN
85 OPEN c_getmoddetails;
86 FETCH c_getmoddetails INTO p_doc_id,p_draft_id;
87 CLOSE c_getmoddetails;
88 ELSE
89 OPEN c_getdocdetails;
90 FETCH c_getdocdetails INTO p_doc_id;
91 CLOSE c_getdocdetails;
92
93 END IF;
94
95 fnd_file.put_line(FND_FILE.LOG,'p_doc_id '||p_doc_id);
96 fnd_file.put_line(FND_FILE.LOG,'p_draft_id '||p_draft_id);
97
98 RecordID := p_doc_id;
99
100 OPEN c_isorderoffidv;
101 FETCH c_isorderoffidv INTO l_isorderoffidv;
102 CLOSE c_isorderoffidv;
103
104 fnd_file.put_line(FND_FILE.LOG,'l_isorderoffidv '||l_isorderoffidv);
105
106 IF l_isorderoffidv = 'N' THEN
107 ContractNumber := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
108 pk1_value => p_doc_id,
109 pk2_value => -1,
110 p_attr_grp_int_name => 'DOD_AWD',
111 p_attr_int_name => 'DOC_NUMBER') ;
112
113 ContractNumber := REPLACE(ContractNumber,'-','');
114
115 fnd_file.put_line(FND_FILE.LOG,'ContractNumber '||ContractNumber);
116
117 l_modiftype := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
118 pk1_value => p_doc_id,
119 pk2_value => p_draft_id,
120 p_attr_grp_int_name => 'DOD_AWD_MOD',
121 p_attr_int_name => 'MODIF_TYPE');
122
123 fnd_file.put_line(FND_FILE.LOG,'l_modiftype '||l_modiftype);
124
125 IF l_modiftype = 'ACO' THEN
126 ACOMod := 'A' || PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
127 pk1_value => p_doc_id,
128 pk2_value => p_draft_id,
129 p_attr_grp_int_name => 'DOD_AWD_MOD',
130 p_attr_int_name => 'SERIAL_NUMBER') ;
131
132 fnd_file.put_line(FND_FILE.LOG,'ACOMod '||ACOMod);
133
134 ELSIF l_modiftype = 'PCO' THEN
135 PCOMod := 'P' || PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
136 pk1_value => p_doc_id,
137 pk2_value => p_draft_id,
138 p_attr_grp_int_name => 'DOD_AWD_MOD',
139 p_attr_int_name => 'SERIAL_NUMBER') ;
140
141 fnd_file.put_line(FND_FILE.LOG,'PCOMod '||PCOMod);
142
143 END IF;
144
145 ELSE
146 ContractNumber := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
147 pk1_value => p_doc_id,
148 pk2_value => -1,
149 p_attr_grp_int_name => 'DOD_ORD_OW',
150 p_attr_int_name => 'SOURCE_DOC_NUM') ;
151
152 ContractNumber := REPLACE(ContractNumber,'-','');
153
154 fnd_file.put_line(FND_FILE.LOG,'ContractNumber '||ContractNumber);
155
156 l_modiftype := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
157 pk1_value => p_doc_id,
158 pk2_value => p_draft_id,
159 p_attr_grp_int_name => 'DOD_ORD_MOD',
160 p_attr_int_name => 'MODIF_TYPE');
161
162 fnd_file.put_line(FND_FILE.LOG,'l_modiftype '||l_modiftype);
163
164
165 IF l_modiftype = 'ACO' THEN
166 ACOMod := 'A' || PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
167 pk1_value => p_doc_id,
168 pk2_value => p_draft_id,
169 p_attr_grp_int_name => 'DOD_ORD_MOD',
170 p_attr_int_name => 'SERIAL_NUMBER') ;
171
172 fnd_file.put_line(FND_FILE.LOG,'ACOMod '||ACOMod);
173
174 ELSIF l_modiftype = 'PCO' THEN
175 PCOMod := 'P' || PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
176 pk1_value => p_doc_id,
177 pk2_value => p_draft_id,
178 p_attr_grp_int_name => 'DOD_ORD_MOD',
179 p_attr_int_name => 'SERIAL_NUMBER') ;
180
181 fnd_file.put_line(FND_FILE.LOG,'PCOMod '||PCOMod);
182
183 END IF;
184
185 END IF;
186
187
188 DeliveryOrder := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
189 pk1_value => p_doc_id,
190 pk2_value => p_draft_id,
191 p_attr_grp_int_name => 'DOD_ORD_OW',
192 p_attr_int_name => 'SERIAL_NUMBER') ;
193 fnd_file.put_line(FND_FILE.LOG,'DeliveryOrder '||DeliveryOrder);
194
195 OPEN c_issuedate;
196 FETCH c_issuedate INTO IssueDate;
197 CLOSE c_issuedate;
198 IssueDateInChar := to_char(IssueDate,'YYYYMMDD');
199
200 fnd_file.put_line(FND_FILE.LOG,'IssueDate '||IssueDateInChar);
201
202 IF P_IS_MOD = 'Y' THEN
203
204 IssuingDoDAAC := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(p_entity_code => 'PO_HEADER_EXT_ATTRS',
205 pk1_value => p_doc_id,
206 pk2_value => p_draft_id,
207 p_attr_grp_int_name => 'addresses',
208 p_attr_int_name => 'addresscode',
209 p_address_type => 'MOD_ISSUING_OFFICE') ;
210
211 ELSE
212
213 IssuingDoDAAC := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(p_entity_code => 'PO_HEADER_EXT_ATTRS',
214 pk1_value => p_doc_id,
215 pk2_value => p_draft_id,
216 p_attr_grp_int_name => 'addresses',
217 p_attr_int_name => 'addresscode',
218 p_address_type => 'ISSUING_OFFICE') ;
219 END IF;
220
221 fnd_file.put_line(FND_FILE.LOG,'IssuingDoDAAC '||IssuingDoDAAC);
222
223 IF P_IS_MOD = 'Y' THEN
224
225 AdminDoDAAC := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(p_entity_code => 'PO_HEADER_EXT_ATTRS',
226 pk1_value => p_doc_id,
227 pk2_value => p_draft_id,
228 p_attr_grp_int_name => 'addresses',
229 p_attr_int_name => 'addresscode',
230 p_address_type => 'MOD_ADMIN_OFFICE') ;
231
232
233 ELSE
234
235 AdminDoDAAC := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(p_entity_code => 'PO_HEADER_EXT_ATTRS',
236 pk1_value => p_doc_id,
237 pk2_value => p_draft_id,
238 p_attr_grp_int_name => 'addresses',
239 p_attr_int_name => 'addresscode',
240 p_address_type => 'ADMIN_OFFICE') ;
241 END IF;
242 fnd_file.put_line(FND_FILE.LOG,'AdminDoDAAC '||AdminDoDAAC);
243
244
245 PayingDoDAAC := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(p_entity_code => 'PO_HEADER_EXT_ATTRS',
246 pk1_value => p_doc_id,
247 pk2_value => p_draft_id,
248 p_attr_grp_int_name => 'addresses',
249 p_attr_int_name => 'addresscode',
250 p_address_type => 'PAY_OFFICE') ;
251
252
253 fnd_file.put_line(FND_FILE.LOG,'PayingDoDAAC '||PayingDoDAAC);
254
255
256 CAGECode := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
257 pk1_value => p_doc_id,
258 pk2_value => p_draft_id,
259 p_attr_grp_int_name => 'SUPPLIER_DTLS',
260 p_attr_int_name => 'CAGE_CODE') ;
261 fnd_file.put_line(FND_FILE.LOG,'CAGECode '||CAGECode);
262
263 DUNS := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
264 pk1_value => p_doc_id,
265 pk2_value => p_draft_id,
266 p_attr_grp_int_name => 'SUPPLIER_DTLS',
267 p_attr_int_name => 'DUNS_NUM') ;
268 fnd_file.put_line(FND_FILE.LOG,'DUNS '||DUNS);
269
270 IF DUNS IS NULL THEN
271 DUNS := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
272 pk1_value => p_doc_id,
273 pk2_value => p_draft_id,
274 p_attr_grp_int_name => 'SUPPLIER_DTLS',
275 p_attr_int_name => 'DUNS_4_NUM') ;
276 fnd_file.put_line(FND_FILE.LOG,'DUNS4 '||DUNS);
277
278 END IF;
279
280 IndexDate := to_char(SYSDATE,'YYYYMMDDHH:MM:SS');
281
282 fnd_file.put_line(FND_FILE.OUTPUT,TransactionCode ||','||
283 SystemName ||','||
284 RecordID ||','||
285 ContractNumber ||','||
286 DeliveryOrder ||','||
287 ACOMod ||','||
288 PCOMod ||','||
289 IssueDateInChar ||','||
290 IssuingDoDAAC ||','||
291 AdminDoDAAC ||','||
292 PayingDoDAAC ||','||
293 CAGECode ||','||
294 DUNS ||','||
295 IndexDate);
296
297 --v_out_file := UTL_FILE.FOPEN('/tmp',p_clm_document_number||'_'||l_timestamp||'.idx', 'w',32000);
298 UTL_FILE.PUT_LINE(v_out_file,TransactionCode ||','||
299 SystemName ||','||
300 RecordID ||','||
301 ContractNumber ||','||
302 DeliveryOrder ||','||
303 ACOMod ||','||
304 PCOMod ||','||
305 IssueDateInChar ||','||
306 IssuingDoDAAC ||','||
307 AdminDoDAAC ||','||
308 PayingDoDAAC ||','||
309 CAGECode ||','||
310 DUNS ||','||
311 IndexDate);
312 UTL_FILE.FFLUSH(v_out_file);
313 UTL_FILE.FCLOSE(v_out_file);
314
315
316 EXCEPTION WHEN OTHERS THEN
317
318 fnd_file.put_line(FND_FILE.LOG,'Exception'||SQLERRM);
319
320 END gen_eda_idx;
321
322
323
324 PROCEDURE gen_eda_files
325 (
326 errbuf OUT NOCOPY VARCHAR2,
327 retcode OUT NOCOPY VARCHAR2,
328 P_IS_MOD IN VARCHAR2,
329 p_clm_document_number IN VARCHAR2,
330 p_clm_document_number2 IN VARCHAR2 DEFAULT NULL,
331 p_approved_date IN VARCHAR2 DEFAULT NULL,
332 p_approved_date2 IN VARCHAR2 DEFAULT NULL,
333 p_effective_date IN VARCHAR2 DEFAULT NULL,
334 p_effective_date2 IN VARCHAR2 DEFAULT NULL,
335 p_doc_status IN VARCHAR2 DEFAULT NULL,
336 p_eda_status IN VARCHAR2 DEFAULT NULL,
337 p_schemaversionused IN VARCHAR2,
338 p_systemadministratordodaac IN VARCHAR2,
339 p_datatemplatecode IN VARCHAR2,
340 p_ditprnumber IN NUMBER,
341 p_doc_purpose IN VARCHAR2,
342 p_system_name IN varchar2
343 ) AS
344
345 p_timestamp VARCHAR2(30);
346
347 l_req_id NUMBER;
348 l_req_ids VARCHAR2(32767):='reqids';
349
350 v_phase VARCHAR2(240);
351 v_status VARCHAR2(240);
352 v_request_phase VARCHAR2(240);
353 v_request_status VARCHAR2(240);
354 v_finished BOOLEAN;
355 v_message VARCHAR2(240);
356
357
358 v_phase_idx VARCHAR2(240);
359 v_status_idx VARCHAR2(240);
360 v_request_phase_idx VARCHAR2(240);
361 v_request_status_idx VARCHAR2(240);
362 v_finished_idx BOOLEAN;
363 v_message_idx VARCHAR2(240);
364
365 v_phase_xml VARCHAR2(240);
366 v_status_xml VARCHAR2(240);
367 v_request_phase_xml VARCHAR2(240);
368 v_request_status_xml VARCHAR2(240);
369 v_finished_xml BOOLEAN;
370 v_message_xml VARCHAR2(240);
371
372 v_phase_pdf VARCHAR2(240);
373 v_status_pdf VARCHAR2(240);
374 v_request_phase_pdf VARCHAR2(240);
375 v_request_status_pdf VARCHAR2(240);
376 v_finished_pdf BOOLEAN;
377 v_message_pdf VARCHAR2(240);
378
379 req_data VARCHAR2(32767);
380
381 startpos NUMBER:=1;
382
383 TYPE ARRAY IS TABLE OF po_headers_all.clm_document_number%TYPE;
384 l_doc_numbers ARRAY;
385
386 l_approved_date DATE := to_date(p_approved_date,'YYYY/MM/DD HH24:MI:SS');
387 l_approved_date2 DATE := to_date(p_approved_date2,'YYYY/MM/DD HH24:MI:SS');
388 l_effective_date DATE := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
389 l_effective_date2 DATE := to_date(p_effective_date2,'YYYY/MM/DD HH24:MI:SS');
390 l_warning_flag varchar2(1);
391
392 CURSOR csr_get_doc_nbr IS
393 SELECT
394 clm_document_number
395 FROM
396 (SELECT CLM_DOCUMENT_NUMBER CLM_DOCUMENT_NUMBER, 'N' ismod,CLM_EFFECTIVE_DATE effdate,APPROVED_DATE APPROVED_DATE,CLM_EDAGEN_DATE
397 FROM po_headers_all
398 WHERE uda_template_id IS NOT NULL
399 UNION
400 SELECT modification_number CLM_DOCUMENT_NUMBER, 'Y' ismod,MOD_EFFECTIVE_DATE effdate,(select APPROVED_DATE from po_headers_merge_v where
401 po_header_id = a.DOCUMENT_ID and draft_id = a.DRAFT_ID) APPROVED_DATE,CLM_EDAGEN_DATE
402 FROM po_drafts a)
403 WHERE (decode(P_IS_MOD,'Base Documents','N','Modifications','Y') = ismod or P_IS_MOD = 'Both')
404 AND
405 (
406 (l_approved_date is null and l_approved_date2 is null )
407 or (l_approved_date2 is null and l_approved_date is not null and APPROVED_DATE >= l_approved_date)
408 or (l_approved_date is null and l_approved_date2 is not null and APPROVED_DATE <= l_approved_date)
409 or (l_approved_date is not null and l_approved_date2 is not null and APPROVED_DATE between l_approved_date and l_approved_date2)
410 ) and (
411 (l_effective_date is null and l_effective_date2 is null )
412 or (l_effective_date2 is null and l_effective_date is not null and effdate >= l_effective_date)
413 or (l_effective_date is null and l_effective_date2 is not null and effdate <= l_effective_date)
414 or (l_effective_date is not null and l_effective_date2 is not null and effdate between l_effective_date and l_effective_date2)
415 ) and (
416 (p_doc_status = 'Approved' and p_eda_status = 'Generated' and CLM_EDAGEN_DATE >= APPROVED_DATE)
417 or (p_doc_status = 'Draft' and p_eda_status = 'Generated' and (
418 (CLM_EDAGEN_DATE IS NOT NULL AND APPROVED_DATE IS NULL) or
419 (CLM_EDAGEN_DATE < APPROVED_DATE)
420 ))
421 or (p_doc_status = 'Approved' and p_eda_status = 'Not Generated' and (
422 (CLM_EDAGEN_DATE is null and APPROVED_DATE is not null) or
423 (CLM_EDAGEN_DATE < APPROVED_DATE)
424 ))
425 or (p_doc_status = 'Draft' and p_eda_status = 'Not Generated' and CLM_EDAGEN_DATE is null and APPROVED_DATE is null)
426 or (p_doc_status = 'All' and p_eda_status = 'Generated' and CLM_EDAGEN_DATE is not null)
427 or (p_doc_status = 'All' and p_eda_status = 'Not Generated' and CLM_EDAGEN_DATE is null)
428 or (p_doc_status = 'Approved' and p_eda_status = 'All' and APPROVED_DATE is not null)
429 or (p_doc_status = 'Draft' and p_eda_status = 'All' and APPROVED_DATE is null)
430 or (p_doc_status = 'All' and p_eda_status = 'All')
431 ) and
432 (
433 (
434 To_Number(SubStr(p_clm_document_number, InStr(p_clm_document_number,'-',1,1)+1,InStr(p_clm_document_number,'-',1,2) -
435 InStr(p_clm_document_number,'-',1,1) - 1)) =
436 To_Number(SubStr(p_clm_document_number2, InStr(p_clm_document_number2,'-',1,1)+1,InStr(p_clm_document_number2,'-',1,2) -
437 InStr(p_clm_document_number2,'-',1,1) - 1))
438 AND
439 To_Number(SubStr(p_clm_document_number, InStr(p_clm_document_number,'-',1,1)+1,InStr(p_clm_document_number,'-',1,2) -
440 InStr(p_clm_document_number,'-',1,1) - 1)) =
441 To_Number(SubStr(CLM_DOCUMENT_NUMBER, InStr(CLM_DOCUMENT_NUMBER,'-',1,1)+1,InStr(CLM_DOCUMENT_NUMBER,'-',1,2) -
442 InStr(CLM_DOCUMENT_NUMBER,'-',1,1) - 1))
443 AND
444
445 SubStr(p_clm_document_number, 1,InStr(p_clm_document_number,'-',1,1)-1) = SubStr(CLM_DOCUMENT_NUMBER,
446 1,InStr(CLM_DOCUMENT_NUMBER,'-',1,1)-1)
447 AND
448 SubStr(p_clm_document_number, 1,InStr(p_clm_document_number,'-',1,1)-1) = SubStr(p_clm_document_number2,
449 1,InStr(p_clm_document_number2,'-',1,1)-1)
450 AND
451 SubStr(p_clm_document_number, InStr(p_clm_document_number,'-',1,2)+1,InStr(p_clm_document_number,'-',1,3) -
452 InStr(p_clm_document_number,'-',1,2) - 1) =
453 SubStr(CLM_DOCUMENT_NUMBER, InStr(CLM_DOCUMENT_NUMBER,'-',1,2)+1,InStr(CLM_DOCUMENT_NUMBER,'-',1,3) -
454 InStr(CLM_DOCUMENT_NUMBER,'-',1,2) - 1)
455 AND
456 SubStr(p_clm_document_number, InStr(p_clm_document_number,'-',1,2)+1,InStr(p_clm_document_number,'-',1,3) -
457 InStr(p_clm_document_number,'-',1,2) - 1) =
458 SubStr(p_clm_document_number2, InStr(p_clm_document_number2,'-',1,2)+1,InStr(p_clm_document_number2,'-',1,3) -
459 InStr(p_clm_document_number2,'-',1,2) - 1)
460 AND
461
462 To_Number(Decode(InStr(CLM_DOCUMENT_NUMBER,'-',1,4),0,SubStr(CLM_DOCUMENT_NUMBER,
463 InStr(CLM_DOCUMENT_NUMBER,'-',1,3)+1),SubStr(CLM_DOCUMENT_NUMBER, InStr(CLM_DOCUMENT_NUMBER,'-',1,3)+1,
464 InStr(CLM_DOCUMENT_NUMBER,'-',1,4) - InStr(CLM_DOCUMENT_NUMBER,'-',1,3)-1 ))) BETWEEN
465 To_Number(Decode(InStr(p_clm_document_number,'-',1,4),0,SubStr(p_clm_document_number,
466 InStr(p_clm_document_number,'-',1,3)+1),substr(p_clm_document_number,
467 instr(p_clm_document_number,'-',1,3)+1,instr(p_clm_document_number,'-',1,4) - instr(p_clm_document_number,'-',1,3)-1))) AND
468 To_Number(Decode(InStr(p_clm_document_number2,'-',1,4),0,SubStr(p_clm_document_number2,
469 InStr(p_clm_document_number2,'-',1,3)+1),substr(p_clm_document_number2,
470 instr(p_clm_document_number2,'-',1,3)+1,instr(p_clm_document_number2,'-',1,4) - instr(p_clm_document_number2,'-',1,3)-1)))
471 )
472 OR
473 (
474 SubStr(p_clm_document_number, 1,InStr(p_clm_document_number,'-',1,1)-1) = SubStr(CLM_DOCUMENT_NUMBER,
475 1,InStr(CLM_DOCUMENT_NUMBER,'-',1,1)-1)
476 AND
477 SubStr(p_clm_document_number, 1,InStr(p_clm_document_number,'-',1,1)-1) = SubStr(p_clm_document_number2,
478 1,InStr(p_clm_document_number2,'-',1,1)-1)
479 AND
480 SubStr(p_clm_document_number, InStr(p_clm_document_number,'-',1,2)+1,InStr(p_clm_document_number,'-',1,3) -
481 InStr(p_clm_document_number,'-',1,2) - 1) =
482 SubStr(CLM_DOCUMENT_NUMBER, InStr(CLM_DOCUMENT_NUMBER,'-',1,2)+1,InStr(CLM_DOCUMENT_NUMBER,'-',1,3) - InStr(CLM_DOCUMENT_NUMBER,'-',1,2)
483 - 1)
484 AND
485 SubStr(p_clm_document_number, InStr(p_clm_document_number,'-',1,2)+1,
486 InStr(p_clm_document_number,'-',1,3) - InStr(p_clm_document_number,'-',1,2) - 1) =
487 SubStr(p_clm_document_number2, InStr(p_clm_document_number2,'-',1,2)+1,
488 InStr(p_clm_document_number2,'-',1,3) - InStr(p_clm_document_number2,'-',1,2) - 1)
489 AND
490
491 To_Number(SubStr(p_clm_document_number, InStr(p_clm_document_number,'-',1,1)+1,InStr(p_clm_document_number,'-',1,2) -
492 InStr(p_clm_document_number,'-',1,1) - 1)) <
493 To_Number(SubStr(p_clm_document_number2, InStr(p_clm_document_number2,'-',1,1)+1,InStr(p_clm_document_number2,'-',1,2) -
494 InStr(p_clm_document_number2,'-',1,1) - 1))
495 AND
496 (
497 To_Number(Decode(InStr(CLM_DOCUMENT_NUMBER,'-',1,4),0,SubStr(CLM_DOCUMENT_NUMBER,
498 InStr(CLM_DOCUMENT_NUMBER,'-',1,3)+1),SubStr(CLM_DOCUMENT_NUMBER, InStr(CLM_DOCUMENT_NUMBER,'-',1,3)+1,InStr(CLM_DOCUMENT_NUMBER,'-',1,4)
499 - InStr(CLM_DOCUMENT_NUMBER,'-',1,3)-1 ))) >=
500 To_Number(Decode(InStr(p_clm_document_number,'-',1,4),0,SubStr(p_clm_document_number,
501 InStr(p_clm_document_number,'-',1,3)+1),substr(p_clm_document_number,
502 instr(p_clm_document_number,'-',1,3)+1,instr(p_clm_document_number,'-',1,4) - instr(p_clm_document_number,'-',1,3)-1)))
503 AND
504 To_Number(Decode(InStr(CLM_DOCUMENT_NUMBER,'-',1,4),0,SubStr(CLM_DOCUMENT_NUMBER,
505 InStr(CLM_DOCUMENT_NUMBER,'-',1,3)+1),SubStr(CLM_DOCUMENT_NUMBER, InStr(CLM_DOCUMENT_NUMBER,'-',1,3)+1,InStr(CLM_DOCUMENT_NUMBER,'-',1,4)
506 - InStr(CLM_DOCUMENT_NUMBER,'-',1,3)-1 ))) <=
507 To_Number(Decode(InStr(p_clm_document_number2,'-',1,4),0,SubStr(p_clm_document_number2,
508 InStr(p_clm_document_number2,'-',1,3)+1),substr(p_clm_document_number2,
509 instr(p_clm_document_number2,'-',1,3)+1,instr(p_clm_document_number2,'-',1,4) - instr(p_clm_document_number2,'-',1,3)-1)))
510 )
511 )
512 OR ( p_clm_document_number2 is null and CLM_DOCUMENT_NUMBER = p_clm_document_number )
513 OR ( p_clm_document_number is null and CLM_DOCUMENT_NUMBER = p_clm_document_number2 )
514 ) ;
515
516
517 l_iteration NUMBER := 1;
518
519 l_max_doc_in_batch NUMBER;
520 l_batch_start NUMBER;
521 l_batch_end NUMBER;
522
523 l_base_or_mod VARCHAR2(1);
524
525 CURSOR csr_req_stats(p_parent_id VARCHAR2) IS
526 SELECT ARGUMENT1,ARGUMENT2,to_timestamp(ARGUMENT8,'YYYYMMDDHH24MISSSSS')
527 FROM FND_CONCURRENT_REQUESTS t WHERE PARENT_REQUEST_ID =p_parent_id AND ARGUMENT1 = 'N' AND NUMBER_OF_ARGUMENTS = 8
528 AND 3 = (SELECT Count(1) FROM FND_CONCURRENT_REQUESTS i WHERE i.ARGUMENT2 = t.ARGUMENT2 AND i.STATUS_CODE = 'C' AND
529 t.PARENT_REQUEST_ID = i.PARENT_REQUEST_ID);
530
531 l_doc_type varchar2(10);
532 l_doc_number varchar2(100);
533 l_timestamp timestamp;
534
535 v_tmp_file UTL_FILE.FILE_TYPE;
536 v_out_dir varchar2(2000);
537
538 BEGIN
539 --CHECK whether the provided directory in the profile option PO_EDA_FILES_LOCATION is proper or not
540 SELECT Trim(FND_PROFILE.VALUE('PO_EDA_FILES_LOCATION')) INTO v_out_dir FROM dual;
541 v_tmp_file := UTL_FILE.FOPEN(v_out_dir,'t.tmp', 'w',32000);
542 UTL_FILE.FREMOVE(v_out_dir,'t.tmp');
543
544 -- Check if the concurrent program is being restarted due to completion of child request
545 begin
546 SELECT FND_PROFILE.VALUE('PO_EDA_BATCH_SIZE') into l_max_doc_in_batch FROM dual;
547 exception
548 when others then
549 l_max_doc_in_batch := 5;
550 end;
551
552 fnd_file.put_line(FND_FILE.LOG,'batch size: '|| l_max_doc_in_batch);
553
554 OPEN csr_get_doc_nbr;
555 FETCH csr_get_doc_nbr BULK COLLECT INTO l_doc_numbers;
556 CLOSE csr_get_doc_nbr;
557
558 req_data := fnd_conc_global.request_data;
559
560 IF req_data IS NOT NULL THEN
561
562 l_iteration:= SubStr(req_data,4,InStr(req_data,'reqids')-4);
563 l_iteration := l_iteration + 1;
564
565 END IF;
566
567 l_batch_start := l_max_doc_in_batch * ( l_iteration - 1) + 1;
568 l_batch_end := l_max_doc_in_batch * l_iteration;
569
570 IF l_batch_end > l_doc_numbers.Count THEN
571 l_batch_end := l_doc_numbers.Count;
572 END IF;
573
574
575 FOR i IN l_batch_start..l_batch_end LOOP
576
577 fnd_file.put_line(FND_FILE.LOG,'CLM Document Numbers collected');
578 fnd_file.put_line(FND_FILE.LOG,'CLM Document Numbers ' || l_doc_numbers(i));
579
580 END LOOP ;
581
582 IF req_data IS NULL THEN
583
584
585
586 fnd_file.put_line(FND_FILE.LOG,' ');
587 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
588 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
589 fnd_file.put_line(FND_FILE.LOG,'Is Mod '||P_IS_MOD);
590 fnd_file.put_line(FND_FILE.LOG,'CLM Document Number '||p_clm_document_number);
591 fnd_file.put_line(FND_FILE.LOG,' ');
592 fnd_file.put_line(FND_FILE.LOG,'------Range Parameters------');
593 fnd_file.put_line(FND_FILE.LOG,' ');
594 fnd_file.put_line(FND_FILE.LOG,'CLM Document Number To '||p_clm_document_number2);
595 fnd_file.put_line(FND_FILE.LOG,'Approved Date From'||p_approved_date);
596 fnd_file.put_line(FND_FILE.LOG,'Approved Date To '||p_approved_date2);
597 fnd_file.put_line(FND_FILE.LOG,'Effective Date From '||p_effective_date);
598 fnd_file.put_line(FND_FILE.LOG,'Effective Date To '||p_effective_date2);
599 fnd_file.put_line(FND_FILE.LOG,'Document Status : '||p_doc_status);
600 fnd_file.put_line(FND_FILE.LOG,'EDA Status : '||p_eda_status);
601
602 fnd_file.put_line(FND_FILE.LOG,' ');
603 fnd_file.put_line(FND_FILE.LOG,'------Mandatory Parameters------');
604 fnd_file.put_line(FND_FILE.LOG,' ');
605 fnd_file.put_line(FND_FILE.LOG,'Schema Version Used '||p_schemaversionused);
606 fnd_file.put_line(FND_FILE.LOG,'System Admin DODAAC '||p_systemadministratordodaac);
607 fnd_file.put_line(FND_FILE.LOG,'Data Template Code '||p_datatemplatecode);
608 fnd_file.put_line(FND_FILE.LOG,'DITPR Number '||p_ditprnumber);
609 fnd_file.put_line(FND_FILE.LOG,'Doc Purpose '||p_doc_purpose);
610 fnd_file.put_line(FND_FILE.LOG,'System Name '||p_system_name);
611
612 l_req_ids :='reqids';
613
614 FOR i IN l_batch_start..l_batch_end LOOP
615
616 fnd_file.put_line(FND_FILE.LOG,'CLM Document Numbers ' || l_doc_numbers(i));
617 SELECT ismod INTO l_base_or_mod FROM po_eda_clm_doc_nbr_v WHERE CLM_DOCUMENT_NUMBER = l_doc_numbers(i);
618 p_timestamp := To_Char(SYSDATE,'YYYYMMDDHH24MISSSSS');
619 fnd_file.put_line(FND_FILE.LOG,'Time Stamp '||p_timestamp);
620
621 fnd_file.put_line(FND_FILE.LOG,'----------------------------------------------');
622 fnd_file.put_line(FND_FILE.LOG,'CLM Document Number :'||l_doc_numbers(i));
623 fnd_file.put_line(FND_FILE.LOG,'----------------------------------------------');
624
625 ---------------------------------------------------------------------------------------------------------------
626 ----- Submit 'Generate Index File for EDA' Concurrent program
627 ---------------------------------------------------------------------------------------------------------------
628
629
630 l_req_id := fnd_request.submit_request
631 (application => 'PO',
632 program => 'POEDAIDXGEN',
633 description => NULL,
634 start_time => NULL,
635 sub_request => TRUE,
636 argument1 => l_base_or_mod,
637 argument2 => l_doc_numbers(i),
638 argument3 => p_timestamp,
639 argument4 => p_system_name
640 );
641
642 fnd_file.put_line(FND_FILE.LOG,'Request Id of EDA Index File generation '||l_req_id);
643
644 IF l_req_ids = 'reqids' THEN
645 l_req_ids := l_req_ids || l_req_id;
646 ELSE
647 l_req_ids := l_req_ids || '~' || l_req_id;
648 END IF;
649
650 ---------------------------------------------------------------------------------------------------------------
651 ----- Submit 'Generate XML File for EDA' Concurrent program
652 ---------------------------------------------------------------------------------------------------------------
653
654 l_req_id := fnd_request.submit_request
655 (application => 'PO',
656 program => 'POEDAXMLGEN',
657 description => NULL,
658 start_time => NULL,
659 sub_request => TRUE,
660 argument1 => l_base_or_mod,
661 argument2 => l_doc_numbers(i),
662 argument3 => p_schemaversionused,
663 argument4 => p_systemadministratordodaac,
664 argument5 => p_datatemplatecode,
665 argument6 => p_ditprnumber,
666 argument7 => p_doc_purpose,
667 argument8 => p_timestamp
668 );
669 fnd_file.put_line(FND_FILE.LOG,'Request Id of EDA XML File generation '||l_req_id);
670
671 l_req_ids := l_req_ids || '~' || l_req_id;
672
673
674 ---------------------------------------------------------------------------------------------------------------
675 ----- Submit 'Generate PDF File for EDA' Concurrent program
676 ---------------------------------------------------------------------------------------------------------------
677
678 l_req_id := fnd_request.submit_request
679 (application => 'PO',
680 program => 'POEDAPDFGEN',
681 description => NULL,
682 start_time => NULL,
683 sub_request => TRUE,
684 argument1 => l_base_or_mod,
685 argument2 => l_doc_numbers(i),
686 argument3 => p_timestamp
687 );
688 fnd_file.put_line(FND_FILE.LOG,'Request Id of EDA PDF File generation '||l_req_id);
689
690 l_req_ids := l_req_ids || '~' || l_req_id;
691
692 COMMIT;
693
694 END LOOP ;
695
696 fnd_conc_global.set_req_globals
697 ( conc_status => 'PAUSED',
698 request_data => 'itr'||l_iteration||l_req_ids
699 );
700
701 ELSE
702
703
704 startpos := InStr(req_data,l_req_ids)+6;
705
706 WHILE startpos < Length(req_data) LOOP
707
708 IF InStr(req_data,'~',startpos) > 0 THEN
709 l_req_id := SubStr(req_data,startpos,InStr(req_data,'~',startpos)- startpos);
710 ELSE
711 l_req_id := SubStr(req_data,startpos);
712 END IF;
713 startpos := startpos+length(l_req_id)+1;
714
715 v_finished := fnd_concurrent.get_request_status
716 (
717 request_id => l_req_id,
718 phase => v_phase,
719 status => v_status,
720 dev_phase => v_request_phase,
721 dev_status => v_request_status,
722 message => v_message
723 );
724 fnd_file.put_line(FND_FILE.LOG,'Request Id ' || l_req_id || ' completed with phase '||v_phase);
725 fnd_file.put_line(FND_FILE.LOG,'Request Id ' || l_req_id || ' completed with status '||v_status);
726
727 END LOOP;
728
729
730
731 l_req_ids :='reqids';
732
733
734 FOR i IN l_batch_start..l_batch_end LOOP
735
736 fnd_file.put_line(FND_FILE.LOG,'CLM Document Numbers ' || l_doc_numbers(i));
737 SELECT ismod INTO l_base_or_mod FROM po_eda_clm_doc_nbr_v WHERE CLM_DOCUMENT_NUMBER = l_doc_numbers(i);
738 p_timestamp := To_Char(SYSDATE,'YYYYMMDDHH24MISSSSS');
739 fnd_file.put_line(FND_FILE.LOG,'Time Stamp '||p_timestamp);
740
741 fnd_file.put_line(FND_FILE.LOG,'----------------------------------------------');
742 fnd_file.put_line(FND_FILE.LOG,'CLM Document Number :'||l_doc_numbers(i));
743 fnd_file.put_line(FND_FILE.LOG,'----------------------------------------------');
744
745
746 ------------------------------------------------------------------------------------------------------------
747 ----- Submit 'Generate Index File for EDA' Concurrent program
748 ---------------------------------------------------------------------------------------------------------------
749
750
751 l_req_id := fnd_request.submit_request
752 (application => 'PO',
753 program => 'POEDAIDXGEN',
754 description => NULL,
755 start_time => NULL,
756 sub_request => TRUE,
757 argument1 => l_base_or_mod,
758 argument2 => l_doc_numbers(i),
759 argument3 => p_timestamp,
760 argument4 => p_system_name
761 );
762
763 fnd_file.put_line(FND_FILE.LOG,'Request Id of EDA Index File generation '||l_req_id);
764
765 IF l_req_ids = 'reqids' THEN
766 l_req_ids := l_req_ids || l_req_id;
767 ELSE
768 l_req_ids := l_req_ids || '~' || l_req_id;
769 END IF;
770
771 ---------------------------------------------------------------------------------------------------------------
772 ----- Submit 'Generate XML File for EDA' Concurrent program
773 ---------------------------------------------------------------------------------------------------------------
774
775 l_req_id := fnd_request.submit_request
776 (application => 'PO',
777 program => 'POEDAXMLGEN',
778 description => NULL,
779 start_time => NULL,
780 sub_request => TRUE,
781 argument1 => l_base_or_mod,
782 argument2 => l_doc_numbers(i),
783 argument3 => p_schemaversionused,
784 argument4 => p_systemadministratordodaac,
785 argument5 => p_datatemplatecode,
786 argument6 => p_ditprnumber,
787 argument7 => p_doc_purpose,
788 argument8 => p_timestamp
789 );
790 fnd_file.put_line(FND_FILE.LOG,'Request Id of EDA XML File generation '||l_req_id);
791
792 l_req_ids := l_req_ids || '~' || l_req_id;
793
794
795 ---------------------------------------------------------------------------------------------------------------
796 ----- Submit 'Generate PDF File for EDA' Concurrent program
797 ---------------------------------------------------------------------------------------------------------------
798
799 l_req_id := fnd_request.submit_request
800 (application => 'PO',
801 program => 'POEDAPDFGEN',
802 description => NULL,
803 start_time => NULL,
804 sub_request => TRUE,
805 argument1 => l_base_or_mod,
806 argument2 => l_doc_numbers(i),
807 argument3 => p_timestamp
808 );
809 fnd_file.put_line(FND_FILE.LOG,'Request Id of EDA PDF File generation '||l_req_id);
810
811 l_req_ids := l_req_ids || '~' || l_req_id;
812
813 COMMIT;
814
815 END LOOP ;
816
817 IF l_batch_start < l_batch_end THEN
818 fnd_conc_global.set_req_globals
819 ( conc_status => 'PAUSED',
820 request_data => 'itr'||l_iteration||l_req_ids
821 );
822 END IF;
823
824 END IF;
825
826 OPEN csr_req_stats(FND_GLOBAL.CONC_REQUEST_ID);
827 LOOP
828 FETCH csr_req_stats INTO l_doc_type,l_doc_number,l_timestamp;
829 EXIT WHEN csr_req_stats%NOTFOUND;
830 IF(l_doc_type = 'N') THEN
831 UPDATE po_headers_all SET CLM_EDAGEN_DATE = l_timestamp WHERE CLM_DOCUMENT_NUMBER = l_doc_number;
832 ELSE
833 UPDATE po_drafts SET CLM_EDAGEN_DATE = l_timestamp WHERE MODIFICATION_NUMBER = l_doc_number;
834 END IF;
835 END LOOP;
836 CLOSE csr_req_stats;
837
838 --checking whether any child program has failed
839 l_warning_flag := 'N';
840 BEGIN
841 SELECT 'Y' into l_warning_flag
842 FROM dual where EXISTS(
843 SELECT 1
844 FROM FND_CONCURRENT_REQUESTS t WHERE PARENT_REQUEST_ID =FND_GLOBAL.CONC_REQUEST_ID AND ARGUMENT1 = 'N' AND NUMBER_OF_ARGUMENTS = 8
845 AND 3 > (SELECT Count(1) FROM FND_CONCURRENT_REQUESTS i WHERE i.ARGUMENT2 = t.ARGUMENT2 AND i.STATUS_CODE = 'C' AND
846 t.PARENT_REQUEST_ID = i.PARENT_REQUEST_ID));
847 EXCEPTION
848 WHEN NO_DATA_FOUND THEN
849 l_warning_flag := 'N';
850 END;
851 IF l_warning_flag <> 'Y' THEN
852 fnd_conc_global.set_req_globals( conc_status => 'WARNING' );
853 END IF;
854
855 EXCEPTION WHEN OTHERS THEN
856
857 fnd_file.put_line(FND_FILE.LOG,'Exception in gen_eda_files - Details '||SQLERRM);
858
859 END gen_eda_files;
860
861 END po_eda_idx_file_gen;