[Home] [Help]
PACKAGE BODY: APPS.ECE_POO_TRANSACTION
Source
1 PACKAGE BODY ece_poo_transaction AS
2 -- $Header: ECEPOOB.pls 120.10.12010000.1 2008/07/25 07:23:27 appldev ship $
3
4 iOutput_width INTEGER := 4000; -- 2823215
5 iKey_count NUMBER := 0;
6 xHeaderCount NUMBER := 0;
7 i_path VARCHAR2(1000);
8 i_filename VARCHAR2(1000);
9 PROCEDURE extract_poo_outbound(errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY VARCHAR2,
11 cOutput_Path IN VARCHAR2,
12 cOutput_Filename IN VARCHAR2,
13 cPO_Number_From IN VARCHAR2,
14 cPO_Number_To IN VARCHAR2,
15 cCDate_From IN VARCHAR2,
16 cCDate_To IN VARCHAR2,
17 cPC_Type IN VARCHAR2,
18 cVendor_Name IN VARCHAR2,
19 cVendor_Site_Code IN VARCHAR2,
20 v_debug_mode IN NUMBER DEFAULT 0) IS
21
22 xProgress VARCHAR2(80);
23 iRun_id NUMBER := 0;
24 iOutput_width INTEGER := 4000;
25 cTransaction_Type VARCHAR2(120) := 'POO';
26 cCommunication_Method VARCHAR2(120) := 'EDI';
27 cHeader_Interface VARCHAR2(120) := 'ECE_PO_INTERFACE_HEADERS';
28 cLine_Interface VARCHAR2(120) := 'ECE_PO_INTERFACE_LINES';
29 cShipment_Interface VARCHAR2(120) := 'ECE_PO_INTERFACE_SHIPMENTS';
30 cDistribution_Interface VARCHAR2(120) := 'ECE_PO_DISTRIBUTIONS'; --Bug 1891291
31 l_line_text VARCHAR2(2000);
32
33 cCreat_Date_From DATE := TO_DATE(cCDate_From,'YYYY/MM/DD HH24:MI:SS');
34 cCreat_Date_To DATE := TO_DATE(cCDate_To ,'YYYY/MM/DD HH24:MI:SS') + 1;
35 cEnabled VARCHAR2(1) := 'Y';
36 ece_transaction_disabled EXCEPTION;
37 --xHeaderCount NUMBER;
38 cFilename VARCHAR2(30) := NULL; --2430822
39
40
41 CURSOR c_output IS
42 SELECT text
43 FROM ece_output
44 WHERE run_id = iRun_id
45 ORDER BY line_id;
46
47 BEGIN
48 xProgress := 'POO-10-1000';
49 ec_debug.enable_debug(v_debug_mode);
50 if ec_debug.G_debug_level >= 2 then
51 ec_debug.push('ECE_POO.Extract_POO_Outbound');
52 ec_debug.pl(3,'cOutput_Path: ' ,cOutput_Path);
53 ec_debug.pl(3,'cOutput_Filename: ' ,cOutput_Filename);
54 ec_debug.pl(3,'cPO_Number_From: ' ,cPO_Number_From);
55 ec_debug.pl(3,'cPO_Number_To: ' ,cPO_Number_To);
56 ec_debug.pl(3,'cCDate_From: ' ,cCDate_From);
57 ec_debug.pl(3,'cCDate_To: ' ,cCDate_To);
58 ec_debug.pl(3,'cPC_Type: ' ,cPC_Type);
59 ec_debug.pl(3,'cVendor_Name: ' ,cVendor_Name);
60 ec_debug.pl(3,'cVendor_Site_Code: ',cVendor_Site_Code);
61 ec_debug.pl(3,'v_debug_mode: ' ,v_debug_mode);
62 end if;
63 /* Check to see if the transaction is enabled. If not, abort */
64 xProgress := 'POO-10-1005';
65 fnd_profile.get('ECE_' || cTransaction_Type || '_ENABLED',cEnabled);
66
67 xProgress := 'POO-10-1010';
68 IF cEnabled = 'N' THEN
69 xProgress := 'POO-10-1015';
70 RAISE ece_transaction_disabled;
71 END IF;
72
73 xProgress := 'POO-10-1020';
74 BEGIN
75 SELECT ece_output_runs_s.NEXTVAL
76 INTO iRun_id
77 FROM DUAL;
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN
81 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_OUTPUT_RUNS_S');
82
83 END;
84
85 xProgress := 'POO-10-1025';
86 if ec_debug.G_debug_level >= 1 then
87 ec_debug.pl(3,'iRun_id: ',iRun_id);
88 ec_debug.pl(1,'EC','ECE_POO_START',NULL);
89 ec_debug.pl(1,'EC','ECE_RUN_ID','RUN_ID',iRun_id);
90 end if;
91
92
93 xProgress := 'POO-10-1026';
94
95 ece_poo_transaction.project_sel_c:=0; --Bug 2490109
96
97 IF cOutput_Filename IS NULL THEN --Bug 2430822
98 cFilename := 'POO' || iRun_id || '.dat';
99 ELSE
100 cFilename := cOutput_Filename;
101 END IF;
102
103 -- Open the file for write.
104 xProgress := 'POO-10-1030';
105 if ec_debug.G_debug_level = 1 then
106 ec_debug.pl(1,'Output File:',cFilename);
107 ec_debug.pl(1,'Open Output file'); --Bug 2034376
108 end if;
109 i_path := cOutput_Path;
110 i_filename := cFilename;
111 -- ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767); --Bug 2887790
112
113 xProgress := 'POO-10-1040';
114 if ec_debug.G_debug_level = 1 then
115 ec_debug.pl(1,'Call Populate Poo Trx procedure'); --Bug 2034376
116 end if;
117
118 ece_poo_transaction.populate_poo_trx(
119 cCommunication_Method,
120 cTransaction_Type,
121 iOutput_width,
122 SYSDATE,
123 iRun_id,
124 cHeader_Interface,
125 cLine_Interface,
126 cShipment_Interface,
127 cDistribution_Interface,
128 cCreat_Date_From,
129 cCreat_Date_To,
130 cVendor_Name,
131 cVendor_Site_Code,
132 cPC_Type,
133 cPO_Number_From,
134 cPO_Number_To);
135
136 /* xProgress := 'POO-10-1035';
137 if ec_debug.G_debug_level = 1 then
138 ec_debug.pl(1,'Call Put To Output Table procedure'); --Bug 2034376
139 end if;
140
141 select count(*)
142 into xHeaderCount
143 from ECE_PO_INTERFACE_HEADERS
144 where run_id = iRun_id;
145 */
146 if ec_debug.G_debug_level = 1 then
147 ec_debug.pl(1,'NUMBER OF RECORDS PROCESSED IS ',xHeaderCount);
148 end if;
149
150
151
152
153
154 /*
155 xProgress := 'POO-10-1041';
156
157 ece_poo_transaction.put_data_to_output_table(
158 cCommunication_Method,
159 cTransaction_Type,
160 iOutput_width,
161 iRun_id,
162 cHeader_Interface,
163 cLine_Interface,
164 cShipment_Interface,
165 cDistribution_Interface);
166 */
167
168 xProgress := 'POO-10-1042';
169 if (utl_file.is_open(ece_poo_transaction.uFile_type)) then
170 utl_file.fclose(ece_poo_transaction.uFile_type);
171 end if;
172
173 IF ec_mapping_utils.ec_get_trans_upgrade_status(cTransaction_Type) = 'U' THEN
174 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
175 retcode := 1;
176 END IF;
177
178 if ec_debug.G_debug_level >= 2 then
179 ec_debug.pop('ECE_POO.Extract_POO_Outbound');
180 end if;
181
182 ec_debug.disable_debug;
183
184 COMMIT;
185
186
187 EXCEPTION
188 WHEN ece_transaction_disabled THEN
189 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED','TRANSACTION',cTransaction_type);
190 retcode := 1;
191 ec_debug.disable_debug;
192 ROLLBACK;
193
194 WHEN utl_file.write_error THEN
195 ec_debug.pl(0,'EC','ECE_UTL_WRITE_ERROR',NULL);
196 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
197
198 retcode := 2;
199 ec_debug.disable_debug;
200 if (utl_file.is_open(ece_poo_transaction.uFile_type))
201 then
202 utl_file.fclose(ece_poo_transaction.uFile_type);
203 end if;
204 ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767);
205 utl_file.fclose(ece_poo_transaction.uFile_type);
206 ROLLBACK;
207
208 WHEN utl_file.invalid_path THEN
209 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_PATH',NULL);
210 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
211
212 retcode := 2;
213 ec_debug.disable_debug;
214 ROLLBACK;
215
216 WHEN utl_file.invalid_operation THEN
217 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_OPERATION',NULL);
218 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
219
220 retcode := 2;
221 ec_debug.disable_debug;
222 if (utl_file.is_open(ece_poo_transaction.uFile_type))
223 then
224 utl_file.fclose(ece_poo_transaction.uFile_type);
225 end if;
226 ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767);
227 utl_file.fclose(ece_poo_transaction.uFile_type);
228 ROLLBACK;
229
230 WHEN OTHERS THEN
231 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
232 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
233
234 retcode := 2;
235 ec_debug.disable_debug;
236 if (utl_file.is_open(ece_poo_transaction.uFile_type))
237 then
238 utl_file.fclose(ece_poo_transaction.uFile_type);
239 end if;
240 ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767);
241 utl_file.fclose(ece_poo_transaction.uFile_type);
242 ROLLBACK;
243
244 END extract_poo_outbound;
245
246 PROCEDURE populate_poo_trx(cCommunication_Method IN VARCHAR2,
247 cTransaction_Type IN VARCHAR2,
248 iOutput_width IN INTEGER,
249 dTransaction_date IN DATE,
250 iRun_id IN INTEGER,
251 cHeader_Interface IN VARCHAR2,
252 cLine_Interface IN VARCHAR2,
253 cShipment_Interface IN VARCHAR2,
254 cDistribution_Interface IN VARCHAR2,
255 cCreate_Date_From IN DATE,
256 cCreate_Date_To IN DATE,
257 cSupplier_Name IN VARCHAR2,
258 cSupplier_Site IN VARCHAR2,
259 cDocument_Type IN VARCHAR2,
260 cPO_Number_From IN VARCHAR2,
261 cPO_Number_To IN VARCHAR2) IS
262
263 xProgress VARCHAR2(30);
264 v_LevelProcessed VARCHAR2(40);
265
266 cAtt_Header_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_HEADERS';
267 cAtt_Detail_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_DETAILS';
268
269 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
270 l_line_tbl ece_flatfile_pvt.Interface_tbl_type;
271 l_shipment_tbl ece_flatfile_pvt.Interface_tbl_type;
272 l_key_tbl ece_flatfile_pvt.Interface_tbl_type;
273
274 l_hdr_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
275 l_hdr_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
276 l_ln_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
277 l_ln_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
278 l_mi_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
279 l_mi_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
280 l_msi_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
281 l_msi_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
282 l_shp_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
283 l_shp_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
284
285 iAtt_hdr_pos NUMBER := 0;
286 iAtt_ln_pos NUMBER := 0;
287 iAtt_mi_pos NUMBER := 0;
288 iAtt_msi_pos NUMBER := 0;
289 iAtt_shp_pos NUMBER := 0;
290
291 v_project_acct_installed BOOLEAN;
292 v_project_acct_short_name VARCHAR2(2) := 'PA';
293 v_project_acct_status VARCHAR2(120);
294 v_project_acct_industry VARCHAR2(120);
295 v_project_acct_schema VARCHAR2(120);
296
297 v_att_enabled VARCHAR2(10);
298 v_header_att_enabled VARCHAR2(10);
299 v_line_att_enabled VARCHAR2(10);
300 v_mitem_att_enabled VARCHAR2(10);
301 v_iitem_att_enabled VARCHAR2(10);
302 v_ship_att_enabled VARCHAR2(10);
303 n_att_seg_size NUMBER;
304
305 v_entity_name VARCHAR2(120);
306 v_pk1_value VARCHAR2(120);
307 v_pk2_value VARCHAR2(120);
308
309 header_sel_c INTEGER;
310 line_sel_c INTEGER;
311 shipment_sel_c INTEGER;
312
313 cHeader_select VARCHAR2(32000);
314 cLine_select VARCHAR2(32000);
315 cShipment_select VARCHAR2(32000);
316
317 cHeader_from VARCHAR2(32000);
318 cLine_from VARCHAR2(32000);
319 cShipment_from VARCHAR2(32000);
320
321 cHeader_where VARCHAR2(32000);
322 cLine_where VARCHAR2(32000);
323 cShipment_where VARCHAR2(32000);
324
325 iHeader_count NUMBER := 0;
326 iLine_count NUMBER := 0;
327 iShipment_count NUMBER := 0;
328 --iKey_count NUMBER := 0;
329
330 l_header_fkey NUMBER;
331 l_line_fkey NUMBER;
332 l_shipment_fkey NUMBER;
333
334 nHeader_key_pos NUMBER;
335 nLine_key_pos NUMBER;
336 nShipment_key_pos NUMBER;
337
338 nTrans_code_pos NUMBER; -- 2823215
339
340 c_file_common_key VARCHAR2(255); -- 2823215
341
342 dummy INTEGER;
343 n_trx_date_pos NUMBER;
344 nDocument_type_pos NUMBER;
345 nPO_Number_pos NUMBER;
346 nPO_Type_pos NUMBER;
347 nRelease_num_pos NUMBER;
348 nRelease_ID_pos NUMBER;
349 nLine_num_pos NUMBER;
350 nLine_Location_ID_pos NUMBER;
351 -- Bug 2823215
352 nShip_Line_Location_ID_pos NUMBER;
353 nShip_Release_Num_pos NUMBER;
354 nLine_uom_code_pos NUMBER;
355 nLine_Location_uom_pos NUMBER;
356 nLp_att_cat_pos NUMBER;
357 nLp_att1_pos NUMBER;
358 nLp_att2_pos NUMBER;
359 nLp_att3_pos NUMBER;
360 nLp_att4_pos NUMBER;
361 nLp_att5_pos NUMBER;
362 nLp_att6_pos NUMBER;
363 nLp_att7_pos NUMBER;
364 nLp_att8_pos NUMBER;
365 nLp_att9_pos NUMBER;
366 nLp_att10_pos NUMBER;
367 nLp_att11_pos NUMBER;
368 nLp_att12_pos NUMBER;
369 nLp_att13_pos NUMBER;
370 nLp_att14_pos NUMBER;
371 nLp_att15_pos NUMBER;
372 nSt_cust_name_pos NUMBER;
373 nSt_cont_name_pos NUMBER;
374 nSt_cont_phone_pos NUMBER;
375 nSt_cont_fax_pos NUMBER;
376 nSt_cont_email_pos NUMBER;
377 nShipping_Instruct_pos NUMBER;
378 nPacking_Instruct_pos NUMBER;
379 nShipping_method_pos NUMBER;
380 nCust_po_num_pos NUMBER;
381 nCust_po_line_num_pos NUMBER;
382 nCust_po_ship_num_pos NUMBER;
383 nCust_prod_desc_pos NUMBER;
384 nDeliv_cust_loc_pos NUMBER;
385 nDeliv_cust_name_pos NUMBER;
386 nDeliv_cont_name_pos NUMBER;
387 nDeliv_cont_phone_pos NUMBER;
388 nDeliv_cont_fax_pos NUMBER;
389 nDeliv_cust_addr_pos NUMBER;
390 nDeliv_cont_email_pos NUMBER;
391 --Bug 2823215
392 nShp_uom_pos NUMBER;
393 nLine_uom_pos NUMBER;
394 l_document_type VARCHAR2(30);
395 nOrganization_ID NUMBER;
396 nItem_ID_pos NUMBER;
397
398 -- Timezone enhancement
399 nRel_date_pos pls_integer;
400 nRel_dt_tz_pos pls_integer;
401 nRel_dt_off_pos pls_integer;
402 nCrtn_date_pos pls_integer;
403 nCrtn_dt_tz_pos pls_integer;
404 nCrtn_dt_off_pos pls_integer;
405 nRev_date_pos pls_integer;
406 nRev_dt_tz_pos pls_integer;
407 nRev_dt_off_pos pls_integer;
408 nAcc_due_dt_pos pls_integer;
409 nAcc_due_tz_pos pls_integer;
410 nAcc_due_off_pos pls_integer;
411 nBlkt_srt_dt_pos pls_integer;
412 nBlkt_srt_tz_pos pls_integer;
413 nBlkt_srt_off_pos pls_integer;
414 nBlkt_end_dt_pos pls_integer;
415 nBlkt_end_tz_pos pls_integer;
416 nBlkt_end_off_pos pls_integer;
417 nPcard_exp_dt_pos pls_integer;
418 nPcard_exp_tz_pos pls_integer;
419 nPcard_exp_off_pos pls_integer;
420 nLine_can_dt_pos pls_integer;
421 nLine_can_tz_pos pls_integer;
422 nLine_can_off_pos pls_integer;
423 nExprn_dt_pos pls_integer;
424 nExprn_tz_pos pls_integer;
425 nExprn_off_pos pls_integer;
426 nShip_need_dt_pos pls_integer;
427 nShip_need_tz_pos pls_integer;
428 nShip_need_off_pos pls_integer;
429 nShip_prom_dt_pos pls_integer;
430 nShip_prom_tz_pos pls_integer;
431 nShip_prom_off_pos pls_integer;
432 nShip_accept_dt_pos pls_integer;
433 nShip_accept_tz_pos pls_integer;
434 nShip_accept_off_pos pls_integer;
435 nShp_can_dt_pos pls_integer;
436 nShp_can_tz_pos pls_integer;
437 nShp_can_off_pos pls_integer;
438 nShp_strt_dt_pos pls_integer;
439 nShp_strt_tz_pos pls_integer;
440 nShp_strt_off_pos pls_integer;
441 nShp_end_dt_pos pls_integer;
442 nShp_end_tz_pos pls_integer;
443 nShp_end_off_pos pls_integer;
444 -- Timezone enhancement
445
446 v_drop_ship_flag NUMBER;
447 rec_order_line_info OE_DROP_SHIP_GRP.Order_Line_Info_Rec_Type; --2887790
448 nHeader_Cancel_Flag_pos NUMBER;
449 nLine_Cancel_Flag_pos NUMBER;
450 nShipment_Cancel_Flag_pos NUMBER;
451 v_header_cancel_flag VARCHAR2(10);
452 v_line_cancel_flag VARCHAR2(10);
453 v_shipment_cancel_flag VARCHAR2(10);
454
455 init_msg_list VARCHAR2(20);
456 simulate VARCHAR2(20);
457 validation_level VARCHAR2(20);
458 commt VARCHAR2(20);
459 return_status VARCHAR2(20);
460 msg_count NUMBER;
461 msg_data VARCHAR2(2000); -- 3650215
462
463 line_part_number VARCHAR2(80);
464 line_part_attrib_category VARCHAR2(80);
465
466 -- bug 6511409
467 line_part_attribute1 MTL_ITEM_FLEXFIELDS.ATTRIBUTE1%TYPE;
468 line_part_attribute2 MTL_ITEM_FLEXFIELDS.ATTRIBUTE2%TYPE;
469 line_part_attribute3 MTL_ITEM_FLEXFIELDS.ATTRIBUTE3%TYPE;
470 line_part_attribute4 MTL_ITEM_FLEXFIELDS.ATTRIBUTE4%TYPE;
471 line_part_attribute5 MTL_ITEM_FLEXFIELDS.ATTRIBUTE5%TYPE;
472 line_part_attribute6 MTL_ITEM_FLEXFIELDS.ATTRIBUTE6%TYPE;
473 line_part_attribute7 MTL_ITEM_FLEXFIELDS.ATTRIBUTE7%TYPE;
474 line_part_attribute8 MTL_ITEM_FLEXFIELDS.ATTRIBUTE8%TYPE;
475 line_part_attribute9 MTL_ITEM_FLEXFIELDS.ATTRIBUTE9%TYPE;
476 line_part_attribute10 MTL_ITEM_FLEXFIELDS.ATTRIBUTE10%TYPE;
477 line_part_attribute11 MTL_ITEM_FLEXFIELDS.ATTRIBUTE11%TYPE;
478 line_part_attribute12 MTL_ITEM_FLEXFIELDS.ATTRIBUTE12%TYPE;
479 line_part_attribute13 MTL_ITEM_FLEXFIELDS.ATTRIBUTE13%TYPE;
480 line_part_attribute14 MTL_ITEM_FLEXFIELDS.ATTRIBUTE14%TYPE;
481 line_part_attribute15 MTL_ITEM_FLEXFIELDS.ATTRIBUTE15%TYPE;
482
483 d_dummy_date DATE;
484
485 iMap_ID NUMBER;
486 c_header_common_key_name VARCHAR2(40);
487 c_line_common_key_name VARCHAR2(40);
488 c_shipment_key_name VARCHAR2(40);
489 n_header_common_key_pos NUMBER;
490 n_line_common_key_pos NUMBER;
491 n_ship_common_key_pos NUMBER;
492
493 fail_convert_to_ext EXCEPTION;
494
495 CURSOR c_org_id(p_line_id NUMBER) IS
496 SELECT DISTINCT ship_to_organization_id
497 FROM po_line_locations
498 WHERE po_line_id = p_line_id;
499
500 BEGIN
501 if ec_debug.G_debug_level >= 2 then
502 ec_debug.push('ECE_POO_TRANSACTION.POPULATE_POO_TRX');
503 ec_debug.pl(3,'cCommunication_Method: ',cCommunication_Method);
504 ec_debug.pl(3,'cTransaction_Type: ' ,cTransaction_Type);
505 ec_debug.pl(3,'iOutput_width: ' ,iOutput_width);
506 ec_debug.pl(3,'dTransaction_date: ' ,dTransaction_date);
507 ec_debug.pl(3,'iRun_id: ' ,iRun_id);
508 ec_debug.pl(3,'cHeader_Interface: ' ,cHeader_Interface);
509 ec_debug.pl(3,'cLine_Interface: ' ,cLine_Interface);
510 ec_debug.pl(3,'cShipment_Interface: ' ,cShipment_Interface);
511 ec_debug.pl(3,'cDistribution_Interface: ' ,cDistribution_Interface);
512 ec_debug.pl(3,'cCreate_Date_From: ' ,cCreate_Date_From);
513 ec_debug.pl(3,'cCreate_Date_To: ' ,cCreate_Date_To);
514 ec_debug.pl(3,'cSupplier_Name: ' ,cSupplier_Name);
515 ec_debug.pl(3,'cSupplier_Site: ' ,cSupplier_Site);
516 ec_debug.pl(3,'cDocument_Type: ' ,cDocument_Type);
517 ec_debug.pl(3,'cPO_Number_From: ' ,cPO_Number_From);
518 ec_debug.pl(3,'cPO_Number_To: ' ,cPO_Number_To);
519 end if;
520
521 xProgress := 'POOB-10-1000';
522 BEGIN
523 SELECT inventory_organization_id
524 INTO norganization_id
525 FROM financials_system_parameters;
526
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 ec_debug.pl(0,
530 'EC',
531 'ECE_NO_ROW_SELECTED',
532 'PROGRESS_LEVEL',
533 xProgress,
534 'INFO',
535 'INVENTORY ORGANIZATION ID',
536 'TABLE_NAME',
537 'FINANCIALS_SYSTEM_PARAMETERS');
538 END;
539 if ec_debug.G_debug_level = 3 then
540 ec_debug.pl(3,'norganization_id: ',norganization_id);
541 end if;
542 --Bug 1891291 begin
543 -- Let's See if Project Accounting is Installed
544 xProgress := 'POOB-10-1001';
545 /* v_project_acct_installed := fnd_installation.get_app_info(
546 v_project_acct_short_name, -- i.e. 'PA'
547 v_project_acct_status, -- 'I' means it's installed
548 v_project_acct_industry,
549 v_project_acct_schema);
550
551 v_project_acct_status := NVL(v_project_acct_status,'X');
552 ec_debug.pl(3,'v_project_acct_status: ' ,v_project_acct_status);
553 ec_debug.pl(3,'v_project_acct_industry: ',v_project_acct_industry);
554 ec_debug.pl(3,'v_project_acct_schema: ' ,v_project_acct_schema);
555 */
556 --Bug 1891291 end
557 -- Get Profile Option Values for Attachments
558 xProgress := 'POOB-10-1002';
559 fnd_profile.get('ECE_' || cTransaction_Type || '_HEAD_ATT',v_header_att_enabled);
560 fnd_profile.get('ECE_' || cTransaction_Type || '_LINE_ATT',v_line_att_enabled);
561 fnd_profile.get('ECE_' || cTransaction_Type || '_MITEM_ATT',v_mitem_att_enabled);
562 fnd_profile.get('ECE_' || cTransaction_Type || '_IITEM_ATT',v_iitem_att_enabled);
563 fnd_profile.get('ECE_' || cTransaction_Type || '_SHIP_ATT',v_ship_att_enabled);
564 fnd_profile.get('ECE_' || cTransaction_Type || '_ATT_SEG_SIZE',n_att_seg_size);
565
566 -- Check to see if any attachments are enabled
567 xProgress := 'POOB-10-1004';
568 IF NVL(v_header_att_enabled,'N') = 'Y' OR
569 NVL(v_mitem_att_enabled,'N') = 'Y' OR
570 NVL(v_iitem_att_enabled,'N') = 'Y' OR
571 NVL(v_ship_att_enabled,'N') = 'Y' THEN
572 v_att_enabled := 'Y';
573 END IF;
574
575 IF v_att_enabled = 'Y' THEN
576 BEGIN
577 IF n_att_seg_size < 1 OR n_att_seg_size > G_MAX_ATT_SEG_SIZE OR n_att_seg_size IS NULL THEN
578 RAISE invalid_number;
579 END IF;
580
581 EXCEPTION
582 WHEN value_error OR invalid_number THEN
583 ec_debug.pl(0,'EC','ECE_INVALID_SEGMENT_NUM','SEGMENT_VALUE',n_att_seg_size,'SEGMENT_DEFAULT',G_DEFAULT_ATT_SEG_SIZE);
584 n_att_seg_size := G_DEFAULT_ATT_SEG_SIZE;
585 END;
586 END IF;
587 if ec_debug.G_debug_level = 3 then
588 ec_debug.pl(3,'v_header_att_enabled: ',v_header_att_enabled);
589 ec_debug.pl(3,'v_line_att_enabled: ' ,v_line_att_enabled);
590 ec_debug.pl(3,'v_mitem_att_enabled: ' ,v_mitem_att_enabled);
591 ec_debug.pl(3,'v_iitem_att_enabled: ' ,v_iitem_att_enabled);
592 ec_debug.pl(3,'v_ship_att_enabled: ' ,v_ship_att_enabled);
593 ec_debug.pl(3,'v_att_enabled: ' ,v_att_enabled);
594 ec_debug.pl(3,'n_att_seg_size: ' ,n_att_seg_size);
595 end if;
596
597 xProgress := 'POOB-10-1010';
598 ece_flatfile_pvt.init_table(cTransaction_Type,cHeader_Interface,NULL,FALSE,l_header_tbl,l_key_tbl);
599
600 xProgress := 'POOB-10-1020';
601 l_key_tbl := l_header_tbl;
602
603 xProgress := 'POOB-10-1025';
604 --iKey_count := l_header_tbl.COUNT;
605 --ec_debug.pl(3,'iKey_count: ',iKey_count );
606
607 xProgress := 'POOB-10-1030';
608 ece_flatfile_pvt.init_table(cTransaction_Type,cLine_Interface,NULL,TRUE,l_line_tbl,l_key_tbl);
609
610 xProgress := 'POOB-10-1040';
611 ece_flatfile_pvt.init_table(cTransaction_Type,cShipment_Interface,NULL,TRUE,l_shipment_tbl,l_key_tbl);
612
613 -- ****************************************************************************
614 -- Here, I am building the SELECT, FROM, and WHERE clauses for the dynamic SQL
615 -- call. The ece_extract_utils_pub.select_clause uses the EDI data dictionary
616 -- for the build.
617 -- ****************************************************************************
618
619 BEGIN
620 SELECT map_id
621 INTO iMap_ID
622 FROM ece_mappings
623 WHERE map_code = 'EC_' || cTransaction_Type || '_FF';
624 EXCEPTION
625 WHEN OTHERS THEN
626 NULL;
627 END;
628 xProgress := 'POOB-10-1050';
629 ece_extract_utils_pub.select_clause(cTransaction_Type,
630 cCommunication_Method,
631 cHeader_Interface,
632 l_header_tbl,
633 cHeader_select,
634 cHeader_from,
635 cHeader_where);
636 BEGIN
637 SELECT eit.key_column_name
638 INTO c_header_common_key_name
639 FROM ece_interface_tables eit
640 WHERE eit.transaction_type = cTransaction_Type AND
641 eit.interface_table_name = cHeader_Interface AND
642 eit.map_id = iMap_ID;
643 EXCEPTION
644 WHEN OTHERS THEN
645 NULL;
646 END;
647
648 xProgress := 'POOB-10-1060';
649 ece_extract_utils_pub.select_clause(cTransaction_Type,
650 cCommunication_Method,
651 cLine_Interface,
652 l_line_tbl,
653 cLine_select,
654 cLine_from,
655 cLine_where);
656 BEGIN
657 SELECT eit.key_column_name
658 INTO c_line_common_key_name
659 FROM ece_interface_tables eit
660 WHERE eit.transaction_type = cTransaction_Type AND
661 eit.interface_table_name = cLine_Interface AND
662 eit.map_id = iMap_ID;
663 EXCEPTION
664 WHEN OTHERS THEN
665 NULL;
666 END;
667
668 xProgress := 'POOB-10-1070';
669 ece_extract_utils_pub.select_clause(cTransaction_Type,
670 cCommunication_Method,
671 cShipment_Interface,
672 l_shipment_tbl,
673 cShipment_select,
674 cShipment_from,
675 cShipment_where);
676 BEGIN
677 SELECT eit.key_column_name
678 INTO c_shipment_key_name
679 FROM ece_interface_tables eit
680 WHERE eit.transaction_type = cTransaction_Type AND
681 eit.interface_table_name = cShipment_Interface AND
682 eit.map_id = iMap_ID;
683 EXCEPTION
684 WHEN OTHERS THEN
685 NULL;
686 END;
687
688 -- **************************************************************************
689 -- Here, I am customizing the WHERE clause to join the Interface tables together.
690 -- i.e. Headers -- Lines -- Line Details
691 --
692 -- Select Data1, Data2, Data3...........
693 -- From Header_View
694 -- Where A.Transaction_Record_ID = D.Transaction_Record_ID (+)
695 -- and B.Transaction_Record_ID = E.Transaction_Record_ID (+)
696 -- and C.Transaction_Record_ID = F.Transaction_Record_ID (+)
697 -- ******* (Customization should be added here) ********
698 -- and A.Communication_Method = 'EDI'
699 -- and A.xxx = B.xxx ........
700 -- and B.yyy = C.yyy .......
701 -- **************************************************************************
702 -- **************************************************************************
703 -- :po_header_id is a place holder for foreign key value.
704 -- A PL/SQL table (list of values) will be used to store data.
705 -- Procedure ece_flatfile.Find_pos will be used to locate the specific
706 -- data value in the PL/SQL table.
707 -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
708 -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
709 --
710 -- Let's use the above example:
711 --
712 -- 1. Execute dynamic SQL 1 for headers (A) data
713 -- Get value of A.xxx (foreign key to B)
714 --
715 -- 2. bind value A.xxx to variable B.xxx
716 --
717 -- 3. Execute dynamic SQL 2 for lines (B) data
718 -- Get value of B.yyy (foreigh key to C)
719 --
720 -- 4. bind value B.yyy to variable C.yyy
721 --
722 -- 5. Execute dynamic SQL 3 for line_details (C) data
723 -- **************************************************************************
724 -- **************************************************************************
725 -- Change the following few lines as needed
726 -- **************************************************************************
727 xProgress := 'POOB-10-1080';
728 cHeader_where := cHeader_where ||
729 'ece_poo_headers_v.communication_method =' ||
730 ':cComm_Method';
731
732
733 xProgress := 'POOB-10-1090';
734 IF cCreate_Date_From IS NOT NULL THEN
735 cHeader_where := cHeader_where ||
736 ' AND ' ||
737 'ece_poo_headers_v.creation_date >=' ||
738 ':cCreate_Dt_From';
739 END IF;
740
741 xProgress := 'POOB-10-1100';
742 IF cCreate_Date_To IS NOT NULL THEN
743 cHeader_where := cHeader_where ||
744 ' AND ' ||
745 'ece_poo_headers_v.creation_date <=' ||
746 ':cCreate_Dt_To';
747 END IF;
748
749 xProgress := 'POOB-10-1110';
750 IF cSupplier_Name IS NOT NULL THEN
751 cHeader_where := cHeader_where ||
752 ' AND ' ||
753 'ece_poo_headers_v.supplier_number =' ||
754 ':cSuppl_Name';
755 END IF;
756
757 xProgress := 'POOB-10-1120';
758 IF cSupplier_Site IS NOT NULL THEN
759 cHeader_where := cHeader_where ||
760 ' AND ' ||
761 'ece_poo_headers_v.vendor_site_id =' ||
762 ':cSuppl_Site';
763 END IF;
764
765 xProgress := 'POOB-10-1130';
766 IF cDocument_Type IS NOT NULL THEN
767 cHeader_where := cHeader_where ||
768 ' AND ' ||
769 'ece_poo_headers_v.document_type =' ||
770 ':cDoc_Type';
771 END IF;
772
773 xProgress := 'POOB-10-1140';
774 IF cPO_Number_From IS NOT NULL THEN
775 cHeader_where := cHeader_where ||
776 ' AND ' ||
777 'ece_poo_headers_v.po_number >=' ||
778 ':cPO_Num_From';
779 END IF;
780
781 xProgress := 'POOB-10-1150';
782 IF cPO_Number_To IS NOT NULL THEN
783 cHeader_where := cHeader_where ||
784 ' AND ' ||
785 'ece_poo_headers_v.po_number <=' ||
786 ':cPO_Num_To';
787 END IF;
788
789 xProgress := 'POOB-10-1160';
790 cHeader_where := cHeader_where ||
791 ' ORDER BY po_number, por_release_num';
792
793 xProgress := 'POOB-10-1170';
794 cLine_where := cLine_where ||
795 ' ece_poo_lines_v.po_header_id = :po_header_id AND' ||
796 ' ece_poo_lines_v.por_release_num = :por_release_num ' ||
797 ' ORDER BY line_num';
798
799 xProgress := 'POOB-10-1180';
800
801 cShipment_where := cShipment_where ||
802 ' ece_poo_shipments_v.po_header_id = :po_header_id AND' ||
803 ' ece_poo_shipments_v.po_line_id = :po_line_id AND' ||
804 ' ece_poo_shipments_v.por_release_id = :por_release_id' ||
805 ' ORDER BY shipment_number'; --2823215
806
807 -- 3957851
808 -- ' ece_poo_shipments_v.por_release_id = :por_release_id AND' ||
809 -- ' ((ece_poo_shipments_v.por_release_id = 0) OR' ||
810 -- ' (ece_poo_shipments_v.por_release_id <> 0 AND' ||
811 -- ' ece_poo_shipments_v.shipment_number = :shipment_number))' ||
812 -- ' ORDER BY shipment_number'; --2823215
813
814
815 xProgress := 'POOB-10-1190';
816 cHeader_select := cHeader_select ||
817 cHeader_from ||
818 cHeader_where;
819
820 cLine_select := cLine_select ||
821 cLine_from ||
822 cLine_where;
823
824 cShipment_select := cShipment_select ||
825 cShipment_from ||
826 cShipment_where;
827 if ec_debug.G_debug_level = 3 then
828 ec_debug.pl(3,'cHeader_select: ',cHeader_select);
829 ec_debug.pl(3,'cLine_select: ',cLine_select);
830 ec_debug.pl(3,'cShipment_select: ',cShipment_select);
831 end if;
832 -- ***************************************************
833 -- *** Get data setup for the dynamic SQL call.
834 -- *** Open a cursor for each of the SELECT call
835 -- *** This tells the database to reserve spaces
836 -- *** for the data returned by the SQL statement
837 -- ***************************************************
838 xProgress := 'POOB-10-1200';
839 header_sel_c := dbms_sql.open_cursor;
840
841 xProgress := 'POOB-10-1210';
842 line_sel_c := dbms_sql.open_cursor;
843
844 xProgress := 'POOB-10-1220';
845 shipment_sel_c := dbms_sql.open_cursor;
846
847 -- ***************************************************
848 -- Parse each of the SELECT statement
849 -- so the database understands the command
850 -- ***************************************************
851 xProgress := 'POOB-10-1230';
852 BEGIN
853 dbms_sql.parse(header_sel_c,
854 cHeader_select,
855 dbms_sql.native);
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
860 cHeader_select);
861 app_exception.raise_exception;
862 END;
863
864 xProgress := 'POOB-10-1240';
865 BEGIN
866 dbms_sql.parse(line_sel_c,
867 cLine_select,
868 dbms_sql.native);
869
870 EXCEPTION
871 WHEN OTHERS THEN
872 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
873 cLine_select);
874 app_exception.raise_exception;
875 END;
876
877 xProgress := 'POOB-10-1250';
878 BEGIN
879 dbms_sql.parse(shipment_sel_c,
880 cShipment_select,
881 dbms_sql.native);
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
886 cShipment_select);
887 app_exception.raise_exception;
888 END;
889
890 -- ************
891 -- set counter
892 -- ************
893 xProgress := 'POOB-10-1260';
894 iHeader_count := l_header_tbl.COUNT;
895
896
897 xProgress := 'POOB-10-1270';
898 iLine_count := l_line_tbl.COUNT;
899
900
901 xProgress := 'POOB-10-1280';
902 iShipment_count := l_shipment_tbl.COUNT;
903 if ec_debug.G_debug_level = 3 then
904 ec_debug.pl(3,'iHeader_count: ',iHeader_count);
905 ec_debug.pl(3,'iLine_count: ',iLine_count);
906 ec_debug.pl(3,'iShipment_count: ',iShipment_count);
907 end if;
908
909 -- ******************************************************
910 -- Define TYPE for every columns in the SELECT statement
911 -- For each piece of the data returns, we need to tell
912 -- the database what type of information it will be.
913 -- e.g. ID is NUMBER, due_date is DATE
914 -- However, for simplicity, we will convert
915 -- everything to varchar2.
916 -- ******************************************************
917 xProgress := 'POOB-10-1290';
918 ece_flatfile_pvt.define_interface_column_type(header_sel_c,cHeader_select,ece_extract_utils_PUB.G_MaxColWidth,l_header_tbl);
919
920 xProgress := 'POOB-10-1300';
921 ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_extract_utils_PUB.G_MaxColWidth,l_line_tbl);
922
923 xProgress := 'POOB-10-1310';
924 ece_flatfile_pvt.define_interface_column_type(shipment_sel_c,cShipment_select,ece_extract_utils_PUB.G_MaxColWidth,l_shipment_tbl);
925
926 -- **************************************************************
927 -- *** The following is custom tailored for this transaction
928 -- *** It finds the values and use them in the WHERE clause to
929 -- *** join tables together.
930 -- **************************************************************
931 -- ***************************************************
932 -- To complete the Line SELECT statement,
933 -- we will need values for the join condition.
934 -- ***************************************************
935 -- Header Level Positions
936 xProgress := 'POOB-10-1320';
937 ece_extract_utils_pub.find_pos(l_header_tbl,ece_extract_utils_pub.G_TRANSACTION_DATE,n_trx_date_pos);
938
939 xProgress := 'POOB-10-1330';
940 ece_extract_utils_pub.find_pos(l_header_tbl,'PO_HEADER_ID',nHeader_key_pos);
941
942 xProgress := 'POOB-10-1340';
943 ece_extract_utils_pub.find_pos(l_header_tbl,'DOCUMENT_TYPE',nDocument_type_pos);
944
945 xProgress := 'POOB-10-1350';
946 ece_extract_utils_pub.find_pos(l_header_tbl,'PO_NUMBER',nPO_Number_pos);
947
948 xProgress := 'POOB-10-1360';
949 ece_extract_utils_pub.find_pos(l_header_tbl,'PO_TYPE',nPO_Type_pos);
950
951 xProgress := 'POOB-10-1370';
952 ece_extract_utils_pub.find_pos(l_header_tbl,'POR_RELEASE_NUM',nRelease_num_pos);
953
954 xProgress := 'POOB-10-1380';
955 ece_extract_utils_pub.find_pos(l_header_tbl,'POR_RELEASE_ID',nRelease_id_pos);
956
957 xProgress := 'POOB-10-1381';
958 ece_extract_utils_pub.find_pos(l_header_tbl,'CANCEL_FLAG',nHeader_Cancel_Flag_pos);
959
960 xProgress := 'POOB-10-1382';
961 ece_flatfile_pvt.find_pos(l_header_tbl,ece_flatfile_pvt.G_Translator_Code,nTrans_code_pos); --2823215
962
963 -- Line Level Positions
964 xProgress := 'POOB-10-1390';
965 ece_extract_utils_pub.find_pos(l_line_tbl,'PO_LINE_LOCATION_ID',nLine_Location_ID_pos);
966
967 xProgress := 'POOB-10-1400';
968 ece_extract_utils_pub.find_pos(l_line_tbl,'LINE_NUM',nLine_num_pos);
969
970 xProgress := 'POOB-10-1402';
971 ece_extract_utils_pub.find_pos(l_line_tbl,'PO_LINE_ID',nLine_key_pos);
972
973 xProgress := 'POOB-10-1404';
974 ece_extract_utils_pub.find_pos(l_line_tbl,'ITEM_ID',nItem_id_pos);
975
976 xProgress := 'POOB-10-1405';
977 ece_extract_utils_pub.find_pos(l_line_tbl,'CANCEL_FLAG',nLine_Cancel_Flag_pos);
978 -- 2823215
979 xProgress := 'POOB-10-1406';
980 ece_extract_utils_pub.find_pos(l_line_tbl,'UOM_CODE',nLine_uom_code_pos);
981
982 xProgress := 'POOB-10-1407';
983 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE_CATEGORY',nLp_att_cat_pos);
984
985 xProgress := 'POOB-10-1408';
986 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE1',nLp_att1_pos);
987
988 xProgress := 'POOB-10-1409';
989 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE2',nLp_att2_pos);
990
991 xProgress := 'POOB-10-1410';
992 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE3',nLp_att3_pos);
993
994 xProgress := 'POOB-10-1411';
995 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE4',nLp_att4_pos);
996
997 xProgress := 'POOB-10-1412';
998 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE5',nLp_att5_pos);
999
1000 xProgress := 'POOB-10-1413';
1001 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE6',nLp_att6_pos);
1002
1003 xProgress := 'POOB-10-1414';
1004 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE7',nLp_att7_pos);
1005
1006 xProgress := 'POOB-10-1415';
1007 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE8',nLp_att8_pos);
1008
1009 xProgress := 'POOB-10-1416';
1010 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE9',nLp_att9_pos);
1011
1012 xProgress := 'POOB-10-1417';
1013 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE10',nLp_att10_pos);
1014
1015 xProgress := 'POOB-10-1418';
1016 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE11',nLp_att11_pos);
1017
1018 xProgress := 'POOB-10-1419';
1019 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE12',nLp_att12_pos);
1020
1021 xProgress := 'POOB-10-1420';
1022 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE13',nLp_att13_pos);
1023
1024 xProgress := 'POOB-10-1421';
1025 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE14',nLp_att14_pos);
1026
1027 xProgress := 'POOB-10-1422';
1028 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE15',nLp_att15_pos); -- 2823215
1029
1030
1031 -- Shipment Level Positions
1032 xProgress := 'POOB-10-1423';
1033 ece_extract_utils_pub.find_pos(l_shipment_tbl,'LINE_LOCATION_ID',nShip_Line_Location_ID_pos);
1034
1035 xProgress := 'POOB-10-1424';
1036 ece_extract_utils_pub.find_pos(l_shipment_tbl,'CANCELLED_FLAG',nShipment_Cancel_Flag_pos);
1037
1038 xProgress := 'POOB-10-1025';
1039 ece_extract_utils_pub.find_pos(l_shipment_tbl,'POR_RELEASE_NUM',nShip_Release_Num_pos);
1040
1041 xProgress := 'POOB-10-1026';
1042 ece_extract_utils_pub.find_pos(l_shipment_tbl,'UOM_CODE',nLine_Location_uom_pos);
1043
1044 xProgress := 'POOB-10-1427';
1045 ece_extract_utils_pub.find_pos(l_shipment_tbl,'SHIPMENT_NUMBER',nShipment_key_pos);
1046
1047 xProgress := 'POOB-10-1428';
1048 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CUSTOMER_NAME',nSt_cust_name_pos);
1049
1050 xProgress := 'POOB-10-1429';
1051 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_NAME',nSt_cont_name_pos);
1052
1053 xProgress := 'POOB-10-1430';
1054 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_PHONE',nSt_cont_phone_pos);
1055
1056 xProgress := 'POOB-10-1431';
1057 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_FAX',nSt_cont_fax_pos);
1058
1059 xProgress := 'POOB-10-1432';
1060 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_EMAIL',nSt_cont_email_pos);
1061
1062 xProgress := 'POOB-10-1433';
1063 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPPING_INSTRUCTIONS',nShipping_Instruct_pos);
1064
1065 xProgress := 'POOB-10-1434';
1066 ece_flatfile_pvt.find_pos(l_shipment_tbl,'PACKING_INSTRUCTIONS',nPacking_Instruct_pos);
1067
1068 xProgress := 'POOB-10-1435';
1069 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPPING_METHOD',nShipping_method_pos);
1070
1071 xProgress := 'POOB-10-1437';
1072 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_PO_NUMBER',nCust_po_num_pos);
1073
1074 xProgress := 'POOB-10-1438';
1075 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_PO_LINE_NUM',nCust_po_line_num_pos);
1076
1077 xProgress := 'POOB-10-1439';
1078 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_PO_SHIPMENT_NUM',nCust_po_ship_num_pos);
1079
1080 xProgress := 'POOB-10-1440';
1081 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_ITEM_DESCRIPTION',nCust_prod_desc_pos);
1082
1083 xProgress := 'POOB-10-1441';
1084 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_LOCATION',nDeliv_cust_loc_pos);
1085
1086 xProgress := 'POOB-10-1442';
1087 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CUSTOMER_NAME',nDeliv_cust_name_pos);
1088
1089 xProgress := 'POOB-10-1443';
1090 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_NAME',nDeliv_cont_name_pos);
1091
1092 xProgress := 'POOB-10-1444';
1093 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_PHONE',nDeliv_cont_phone_pos);
1094
1095 xProgress := 'POOB-10-1445';
1096 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_FAX',nDeliv_cont_fax_pos);
1097
1098 xProgress := 'POOB-10-1446';
1099 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CUSTOMER_ADDRESS',nDeliv_cust_addr_pos);
1100
1101 xProgress := 'POOB-10-1447';
1102 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_EMAIL',nDeliv_cont_email_pos);
1103
1104 -- 2823215
1105 xProgress := 'POOB-10-1448';
1106 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CODE_UOM',nShp_uom_pos);
1107
1108 xProgress := 'POOB-10-1449';
1109 ece_flatfile_pvt.find_pos(l_line_tbl,'UOM_CODE',nLine_uom_pos);
1110
1111 if ec_debug.G_debug_level = 3 then
1112 ec_debug.pl(3,'n_trx_date_pos: ',n_trx_date_pos);
1113 ec_debug.pl(3,'nHeader_key_pos: ',nHeader_key_pos);
1114 ec_debug.pl(3,'nDocument_type_pos: ',nDocument_type_pos);
1115 ec_debug.pl(3,'nPO_Number_pos: ',nPO_Number_pos);
1116 ec_debug.pl(3,'nPO_Type_pos: ',nPO_Type_pos);
1117 ec_debug.pl(3,'nRelease_num_pos: ',nRelease_num_pos);
1118 ec_debug.pl(3,'nRelease_id_pos: ',nRelease_id_pos);
1119 ec_debug.pl(3,'nLine_Location_ID_pos: ',nLine_Location_ID_pos);
1120 ec_debug.pl(3,'nLine_num_pos: ',nLine_num_pos);
1121 ec_debug.pl(3,'nLine_key_pos: ',nLine_key_pos);
1122 ec_debug.pl(3,'nItem_id_pos: ',nItem_id_pos);
1123 ec_debug.pl(3,'nShip_Line_Location_ID_pos: ',nShip_Line_Location_ID_pos);
1124 end if;
1125
1126 -- Timezone enhancement
1127 xProgress := 'POOB-TZ-1000';
1128 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_DATE', nRel_date_pos);
1129
1130 xProgress := 'POOB-TZ-1001';
1131 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_DT_TZ_CODE',nRel_dt_tz_pos);
1132
1133 xProgress := 'POOB-TZ-1002';
1134 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_DT_OFF',nRel_dt_off_pos);
1135
1136 xProgress := 'POOB-TZ-1003';
1137 ece_flatfile_pvt.find_pos(l_header_tbl,'CREATION_DATE',nCrtn_date_pos);
1138
1139 xProgress := 'POOB-TZ-1004';
1140 ece_flatfile_pvt.find_pos(l_header_tbl,'CREATION_DT_TZ_CODE',nCrtn_dt_tz_pos);
1141
1142 xProgress := 'POOB-TZ-1005';
1143 ece_flatfile_pvt.find_pos(l_header_tbl,'CREATION_DT_OFF',nCrtn_dt_off_pos);
1144
1145 xProgress := 'POOB-TZ-1006';
1146 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_REVISION_DATE',nRev_date_pos);
1147
1148 xProgress := 'POOB-TZ-1007';
1149 ece_flatfile_pvt.find_pos(l_header_tbl,'REVISION_DT_TZ_CODE',nRev_dt_tz_pos);
1150
1151 xProgress := 'POOB-TZ-1008';
1152 ece_flatfile_pvt.find_pos(l_header_tbl,'REVISION_DT_OFF',nRev_dt_off_pos);
1153
1154 xProgress := 'POOB-TZ-1009';
1155 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_ACCEPTANCE_DUE_BY_DATE',nAcc_due_dt_pos);
1156
1157 xProgress := 'POOB-TZ-1010';
1158 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_ACCEPT_DUE_TZ_CODE',nAcc_due_tz_pos);
1159
1160 xProgress := 'POOB-TZ-1011';
1161 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_ACCEPT_DUE_OFF',nAcc_due_off_pos);
1162
1163 xProgress := 'POOB-TZ-1012';
1164 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_START_DATE',nBlkt_srt_dt_pos);
1165
1166 xProgress := 'POOB-TZ-1013';
1167 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_SRT_DT_TZ_CODE',nBlkt_srt_tz_pos);
1168
1169 xProgress := 'POOB-TZ-1014';
1170 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_SRT_DT_OFF',nBlkt_srt_off_pos);
1171
1172 xProgress := 'POOB-TZ-1015';
1173 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_END_DATE',nBlkt_end_dt_pos);
1174
1175 xProgress := 'POOB-TZ-1016';
1176 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_END_DT_TZ_CODE',nBlkt_end_tz_pos);
1177
1178 xProgress := 'POOB-TZ-1017';
1179 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_END_DT_OFF',nBlkt_end_off_pos);
1180
1181 xProgress := 'POOB-TZ-1018';
1182 ece_flatfile_pvt.find_pos(l_header_tbl,'PCARD_EXPIRATION_DATE',nPcard_exp_dt_pos);
1183
1184 xProgress := 'POOB-TZ-1019';
1185 ece_flatfile_pvt.find_pos(l_header_tbl,'PCARD_EXPRN_DT_TZ_CODE',nPcard_exp_tz_pos);
1186
1187 xProgress := 'POOB-TZ-1020';
1188 ece_flatfile_pvt.find_pos(l_header_tbl,'PCARD_EXPRN_DT_OFF',nPcard_exp_off_pos);
1189
1190
1191 xProgress := 'POOB-TZ-1021';
1192 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_CANCELLED_DATE',nLine_can_dt_pos);
1193
1194 xProgress := 'POOB-TZ-1022';
1195 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_CANCEL_DT_TZ_CODE',nLine_can_tz_pos);
1196
1197 xProgress := 'POOB-TZ-1023';
1198 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_CANCEL_DT_OFF',nLine_can_off_pos);
1199
1200 xProgress := 'POOB-TZ-1024';
1201 ece_flatfile_pvt.find_pos(l_line_tbl,'EXPIRATION_DATE',nExprn_dt_pos);
1202
1203 xProgress := 'POOB-TZ-1025';
1204 ece_flatfile_pvt.find_pos(l_line_tbl,'EXPIRATION_DT_TZ_CODE',nExprn_tz_pos);
1205
1206 xProgress := 'POOB-TZ-1026';
1207 ece_flatfile_pvt.find_pos(l_line_tbl,'EXPIRATION_DT_OFF',nExprn_off_pos);
1208
1209 xProgress := 'POOB-TZ-1027';
1210 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_NEED_BY_DATE',nShip_need_dt_pos);
1211
1212 xProgress := 'POOB-TZ-1028';
1213 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_NEED_DT_TZ_CODE',nShip_need_tz_pos);
1214
1215 xProgress := 'POOB-TZ-1029';
1216 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_NEED_DT_OFF',nShip_need_off_pos);
1217
1218 xProgress := 'POOB-TZ-1030';
1219 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_PROMISED_DATE',nShip_prom_dt_pos);
1220
1221 xProgress := 'POOB-TZ-1031';
1222 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_PROM_DT_TZ_CODE',nShip_prom_tz_pos);
1223
1224 xProgress := 'POOB-TZ-1032';
1225 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_PROM_DT_OFF',nShip_prom_off_pos);
1226
1227 xProgress := 'POOB-TZ-1033';
1228 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_LAST_ACCEPTABLE_DATE',nShip_accept_dt_pos);
1229
1230 xProgress := 'POOB-TZ-1034';
1231 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_LAST_ACC_DT_TZ_CODE',nShip_accept_tz_pos);
1232
1233 xProgress := 'POOB-TZ-1035';
1234 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_LAST_ACC_DT_OFF',nShip_accept_off_pos);
1235
1236 xProgress := 'POOB-TZ-1036';
1237 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CANCELLED_DATE',nShp_can_dt_pos);
1238
1239 xProgress := 'POOB-TZ-1037';
1240 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CANCEL_DT_TZ_CODE',nShp_can_tz_pos);
1241
1242 xProgress := 'POOB-TZ-1038';
1243 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CANCEL_DT_OFF',nShp_can_off_pos);
1244
1245 xProgress := 'POOB-TZ-1039';
1246 ece_flatfile_pvt.find_pos(l_shipment_tbl,'START_DATE',nShp_strt_dt_pos);
1247
1248 xProgress := 'POOB-TZ-1040';
1249 ece_flatfile_pvt.find_pos(l_shipment_tbl,'START_DT_TZ_CODE',nShp_strt_tz_pos);
1250
1251 xProgress := 'POOB-TZ-1041';
1252 ece_flatfile_pvt.find_pos(l_shipment_tbl,'START_DT_OFF',nShp_strt_off_pos);
1253
1254 xProgress := 'POOB-TZ-1042';
1255 ece_flatfile_pvt.find_pos(l_shipment_tbl,'END_DATE',nShp_end_dt_pos);
1256
1257 xProgress := 'POOB-TZ-1043';
1258 ece_flatfile_pvt.find_pos(l_shipment_tbl,'END_DT_TZ_CODE',nShp_end_tz_pos);
1259
1260 xProgress := 'POOB-TZ-1044';
1261 ece_flatfile_pvt.find_pos(l_shipment_tbl,'END_DT_OFF',nShp_end_off_pos);
1262 -- Timezone enhancement
1263 xProgress := 'POOB-09-1400';
1264 ece_flatfile_pvt.find_pos(l_header_tbl,c_header_common_key_name,n_header_common_key_pos);
1265
1266 xProgress := 'POOB-09-1401';
1267 ece_flatfile_pvt.find_pos(l_line_tbl,c_line_common_key_name,n_line_common_key_pos);
1268
1269 xProgress := 'POOB-09-1402';
1270 ece_flatfile_pvt.find_pos(l_shipment_tbl,c_shipment_key_name,n_ship_common_key_pos);
1271
1272 xProgress := 'POOB-10-1407';
1273 -- bind variables
1274 dbms_sql.bind_variable(header_sel_c,'cComm_Method',cCommunication_Method);
1275 IF cCreate_Date_From IS NOT NULL THEN
1276 dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_From',cCreate_Date_From);
1277 END IF;
1278
1279 IF cCreate_Date_To IS NOT NULL THEN
1280 dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_To',cCreate_Date_To);
1281 END IF;
1282
1283 IF cSupplier_Name IS NOT NULL THEN
1284 dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1285 END IF;
1286
1287 IF cSupplier_Site IS NOT NULL THEN
1288 dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1289 END IF;
1290
1291 IF cDocument_Type IS NOT NULL THEN
1292 dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1293 END IF;
1294
1295 IF cPO_Number_From IS NOT NULL THEN
1296 dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1297 END IF;
1298
1299 IF cPO_Number_To IS NOT NULL THEN
1300 dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1301 END IF;
1302
1303 -- dbms_sql.bind_variable(line_sel_c,'l_nOrganization_ID',nOrganization_ID);
1304
1305 iKey_count := 0;
1306 if ec_debug.G_debug_level = 3 then
1307 ec_debug.pl(3,'iKey_count: ',iKey_count);
1308 end if;
1309
1310 -- EXECUTE the SELECT statement
1311 xProgress := 'POOB-10-1408';
1312 dummy := dbms_sql.execute(header_sel_c);
1313
1314 -- ***************************************************
1315 -- The model is:
1316 -- HEADER - LINE - SHIPMENT ...
1317 -- With data for each HEADER line, populate the header
1318 -- interfacetable then get all LINES that belongs
1319 -- to the HEADER. Then get all
1320 -- SHIPMENTS that belongs to the LINE.
1321 -- ***************************************************
1322 xProgress := 'POOB-10-1430';
1323 WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP -- Header
1324
1325 -- **************************************
1326 -- store internal values in pl/sql table
1327 -- **************************************
1328 if (NOT utl_file.is_open(ece_poo_transaction.uFile_type)) then
1329 ece_poo_transaction.uFile_type := utl_file.fopen(i_path,i_filename,'W',32767);
1330 end if;
1331 xProgress := 'POOB-10-1431';
1332 ece_flatfile_pvt.assign_column_value_to_tbl(Header_sel_c,0,l_header_tbl,l_key_tbl);
1333
1334 -- ***************************************************
1335 -- also need to populate transaction_date and run_id
1336 -- ***************************************************
1337 xProgress := 'POOB-10-1432';
1338 l_header_tbl(n_trx_date_pos).value := TO_CHAR(dTransaction_date,'YYYYMMDD HH24MISS');
1339
1340 -- The application specific feedback logic begins here.
1341 xProgress := 'POOB-10-1440';
1342 BEGIN
1343 /* Bug 2396394 Added the document type CONTRACT in SQL below */
1344 SELECT DECODE(l_header_tbl(nDocument_type_pos).value,
1345 'BLANKET' ,'NB',
1346 'STANDARD' ,'NS',
1347 'PLANNED' ,'NP',
1348 'RELEASE' ,'NR',
1349 'BLANKET RELEASE' ,'NR',
1350 'CONTRACT' ,'NC',
1351 'NR')
1352 INTO l_document_type
1353 FROM DUAL;
1354
1355 EXCEPTION
1356 WHEN NO_DATA_FOUND THEN
1357 ec_debug.pl(0,
1358 'EC',
1359 'ECE_DECODE_FAILED',
1360 'PROGRESS_LEVEL',
1361 xProgress,
1362 'CODE',
1363 l_header_tbl(nDocument_type_pos).value);
1364 END;
1365 if ec_debug.G_debug_level = 3 then
1366 ec_debug.pl(3, 'l_document_type: ',l_document_type);
1367 end if;
1368 xProgress := 'POOB-10-1450';
1369
1370
1371 ece_poo_transaction.update_po(l_document_type,
1372 l_header_tbl(nPO_Number_pos).value,
1373 l_header_tbl(nPO_type_pos).value,
1374 l_header_tbl(nRelease_num_pos).value);
1375
1376 xProgress := 'POOB-TZ-1500';
1377 ece_timezone_api.get_server_timezone_details(
1378 to_date(l_header_tbl(nRel_date_pos).value,'YYYYMMDD HH24MISS'),
1379 l_header_tbl(nRel_dt_off_pos).value,
1380 l_header_tbl(nRel_dt_tz_pos).value
1381 );
1382
1383 xProgress := 'POOB-TZ-1510';
1384
1385 ece_timezone_api.get_server_timezone_details
1386 (
1387 to_date(l_header_tbl(nCrtn_date_pos).value,'YYYYMMDD HH24MISS'),
1388 l_header_tbl(nCrtn_dt_off_pos).value,
1389 l_header_tbl(nCrtn_dt_tz_pos).value
1390 );
1391
1392 xProgress := 'POOB-TZ-1520';
1393
1394 ece_timezone_api.get_server_timezone_details
1395 (
1396 to_date(l_header_tbl(nRev_date_pos).value,'YYYYMMDD HH24MISS'),
1397 l_header_tbl(nRev_dt_off_pos).value,
1398 l_header_tbl(nRev_dt_tz_pos).value
1399 );
1400
1401 xProgress := 'POOB-TZ-1530';
1402 ece_timezone_api.get_server_timezone_details
1403 (
1404 to_date(l_header_tbl(nAcc_due_dt_pos).value,'YYYYMMDD HH24MISS'),
1405 l_header_tbl(nAcc_due_off_pos).value,
1406 l_header_tbl(nAcc_due_tz_pos).value
1407 );
1408
1409 xProgress := 'POOB-TZ-1540';
1410 ece_timezone_api.get_server_timezone_details
1411 (
1412 to_date(l_header_tbl(nBlkt_srt_dt_pos).value,'YYYYMMDD HH24MISS'),
1413 l_header_tbl(nBlkt_srt_off_pos).value,
1414 l_header_tbl(nBlkt_srt_tz_pos).value
1415 );
1416
1417 xProgress := 'POOB-TZ-1550';
1418 ece_timezone_api.get_server_timezone_details
1419 (
1420 to_date(l_header_tbl(nBlkt_end_dt_pos).value,'YYYYMMDD HH24MISS'),
1421 l_header_tbl(nBlkt_end_off_pos).value,
1422 l_header_tbl(nBlkt_end_tz_pos).value
1423 );
1424
1425 xProgress := 'POOB-TZ-1560';
1426 ece_timezone_api.get_server_timezone_details
1427 (
1428 to_date(l_header_tbl(nPcard_exp_dt_pos).value,'YYYYMMDD HH24MISS'),
1429 l_header_tbl(nPcard_exp_off_pos).value,
1430 l_header_tbl(nPcard_exp_tz_pos).value
1431 );
1432
1433 -- pass the pl/sql table in for xref
1434 xProgress := 'POOB-10-1460';
1435 ec_code_conversion_pvt.populate_plsql_tbl_with_extval(p_api_version_number => 1.0,
1436 p_init_msg_list => init_msg_list,
1437 p_simulate => simulate,
1438 p_commit => commt,
1439 p_validation_level => validation_level,
1440 p_return_status => return_status,
1441 p_msg_count => msg_count,
1442 p_msg_data => msg_data,
1443 p_key_tbl => l_key_tbl,
1444 p_tbl => l_header_tbl);
1445
1446 -- ***************************
1447 -- insert into interface table
1448 -- ***************************
1449 xProgress := 'POOB-10-1480';
1450 BEGIN
1451 SELECT ece_poo_header_s.NEXTVAL
1452 INTO l_header_fkey
1453 FROM DUAL;
1454
1455 EXCEPTION
1456 WHEN NO_DATA_FOUND THEN
1457 ec_debug.pl(0,
1458 'EC',
1459 'ECE_GET_NEXT_SEQ_FAILED',
1460 'PROGRESS_LEVEL',
1461 xProgress,
1462 'SEQ',
1463 'ECE_POO_HEADER_S');
1464
1465 END;
1466 ec_debug.pl(3,'l_header_fkey: ',l_header_fkey);
1467 --2823215
1468 xProgress := 'POOB-10-1490';
1469
1470 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),1,25),25);
1471
1472 xProgress := 'POOB-10-1491';
1473 c_file_common_key := c_file_common_key ||
1474 RPAD(SUBSTRB(NVL(l_header_tbl(n_header_common_key_pos).value,' '),1,22),22) || RPAD(' ',22) || RPAD(' ',22);
1475
1476 ec_debug.pl(3, 'c_file_common_key: ',c_file_common_key);
1477
1478
1479 /* ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,
1480 cTransaction_Type,
1481 cCommunication_Method,
1482 cHeader_Interface,
1483 l_header_tbl,
1484 l_header_fkey); */
1485
1486 -- Now update the columns values of which have been obtained
1487 -- thru the procedure calls.
1488
1489 -- ********************************************************
1490 -- Call custom program stub to populate the extension table
1491 -- ********************************************************
1492 xProgress := 'POOB-10-1492';
1493 ece_poo_x.populate_ext_header(l_header_fkey,l_header_tbl);
1494 -- 2823215
1495 ece_poo_transaction.write_to_file(cTransaction_Type,
1496 cCommunication_Method,
1497 cHeader_Interface,
1498 l_header_tbl,
1499 iOutput_width,
1500 iRun_id,
1501 c_file_common_key,
1502 l_header_fkey);
1503
1504 -- 2823215
1505 -- Header Level Attachment Handler
1506 xProgress := 'POOB-10-1501';
1507 IF v_header_att_enabled = 'Y' THEN
1508 xProgress := 'POOB-10-1502';
1509 IF l_document_type = 'NR' THEN -- If this is a Release PO.
1510 xProgress := 'POOB-10-1503';
1511 v_entity_name := 'PO_RELEASES';
1512 v_pk1_value := l_header_tbl(nRelease_id_pos).value;
1513 if ec_debug.G_debug_level = 3 then
1514 ec_debug.pl(3,'release_id: ',l_header_tbl(nRelease_id_pos).value);
1515 end if;
1516 ELSE -- If this is a non-Release PO.
1517 xProgress := 'POOB-10-1504';
1518 v_entity_name := 'PO_HEADERS';
1519 v_pk1_value := l_header_tbl(nHeader_key_pos).value;
1520 if ec_debug.G_debug_level = 3 then
1521 ec_debug.pl(3,'po_header_id: ',l_header_tbl(nHeader_key_pos).value);
1522 end if;
1523 END IF;
1524
1525 xProgress := 'POOB-10-1505';
1526 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1527 cTransaction_Type,
1528 iRun_id,
1529 2,
1530 3,
1531 cAtt_Header_Interface,
1532 cAtt_Detail_Interface,
1533 v_entity_name,
1534 'VENDOR',
1535 v_pk1_value,
1536 C_ANY_VALUE,
1537 C_ANY_VALUE,
1538 C_ANY_VALUE,
1539 C_ANY_VALUE,
1540 n_att_seg_size,
1541 l_key_tbl,
1542 c_file_common_key,
1543 l_hdr_att_hdr_tbl,
1544 l_hdr_att_dtl_tbl,
1545 iAtt_hdr_pos); -- 2823215
1546 END IF;
1547
1548 -- ***************************************************
1549 -- From Header data, we can assign values to
1550 -- place holders (foreign keys) in Line_select and
1551 -- Line_detail_Select
1552 -- set values into binding variables
1553 -- ***************************************************
1554
1555 -- use the following bind_variable feature as you see fit.
1556 xProgress := 'POOB-10-1510';
1557 dbms_sql.bind_variable(line_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1558
1559 xProgress := 'POOB-10-1515';
1560 dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1561
1562 xProgress := 'POOB-10-1520';
1563 dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1564
1565 xProgress := 'POOB-10-1525';
1566 dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1567
1568 xProgress := 'POOB-10-1530';
1569 dummy := dbms_sql.execute(line_sel_c);
1570
1571 -- ***************************
1572 -- Line Level Loop Starts Here
1573 -- ***************************
1574 xProgress := 'POOB-10-1540';
1575 WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
1576
1577 -- ****************************
1578 -- store values in pl/sql table
1579 -- ****************************
1580 xProgress := 'POOB-10-1550';
1581 ece_flatfile_pvt.assign_column_value_to_tbl(line_sel_c,iHeader_count,l_line_tbl,l_key_tbl);
1582
1583 -- The following procedure gets the part number for the
1584 -- item ID returned
1585 xProgress := 'POOB-10-1560';
1586 ece_inventory.get_item_number(l_line_tbl(nItem_ID_pos).value,
1587 nOrganization_ID,
1588 line_part_number,
1589 line_part_attrib_category,
1590 line_part_attribute1,
1591 line_part_attribute2,
1592 line_part_attribute3,
1593 line_part_attribute4,
1594 line_part_attribute5,
1595 line_part_attribute6,
1596 line_part_attribute7,
1597 line_part_attribute8,
1598 line_part_attribute9,
1599 line_part_attribute10,
1600 line_part_attribute11,
1601 line_part_attribute12,
1602 line_part_attribute13,
1603 line_part_attribute14,
1604 line_part_attribute15);
1605
1606 begin
1607
1608 select uom_code into l_line_tbl(nLine_uom_pos).value
1609 from mtl_units_of_measure
1610 where unit_of_measure = l_line_tbl(nLine_uom_code_pos).value;
1611 exception
1612 when others then
1613 null;
1614 end;
1615
1616
1617 xProgress := 'POOB-TZ-2500';
1618 ece_timezone_api.get_server_timezone_details
1619 (
1620 to_date(l_line_tbl(nLine_can_dt_pos).value,'YYYYMMDD HH24MISS'),
1621 l_line_tbl(nLine_can_off_pos).value,
1622 l_line_tbl(nLine_can_tz_pos).value
1623 );
1624
1625 xProgress := 'POOB-TZ-2510';
1626
1627 ece_timezone_api.get_server_timezone_details
1628 (
1629 to_date(l_line_tbl(nExprn_dt_pos).value,'YYYYMMDD HH24MISS'),
1630 l_line_tbl(nExprn_off_pos).value,
1631 l_line_tbl(nExprn_tz_pos).value
1632 );
1633
1634 -- pass the pl/sql table in for xref
1635 xProgress := 'POOB-10-1570';
1636 ec_code_conversion_pvt.populate_plsql_tbl_with_extval(
1637 p_api_version_number => 1.0,
1638 p_init_msg_list => init_msg_list,
1639 p_simulate => simulate,
1640 p_commit => commt,
1641 p_validation_level => validation_level,
1642 p_return_status => return_status,
1643 p_msg_count => msg_count,
1644 p_msg_data => msg_data,
1645 p_key_tbl => l_key_tbl,
1646 p_tbl => l_line_tbl);
1647
1648 xProgress := 'POOB-10-1590';
1649 BEGIN
1650 SELECT ece_poo_line_s.NEXTVAL INTO l_line_fkey
1651 FROM DUAL;
1652
1653 EXCEPTION
1654 WHEN NO_DATA_FOUND THEN
1655 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_POO_LINE_S');
1656
1657 END;
1658 ec_debug.pl(3,'l_line_fkey: ',l_line_fkey);
1659
1660 if ec_debug.G_debug_level = 3 then
1661 ec_debug.pl(3,'line_part_number: ' ,line_part_number);
1662 ec_debug.pl(3,'line_part_attrib_category: ',line_part_attrib_category);
1663 ec_debug.pl(3,'line_part_attribute1: ' ,line_part_attribute1);
1664 ec_debug.pl(3,'line_part_attribute2: ' ,line_part_attribute2);
1665 ec_debug.pl(3,'line_part_attribute3: ' ,line_part_attribute3);
1666 ec_debug.pl(3,'line_part_attribute4: ' ,line_part_attribute4);
1667 ec_debug.pl(3,'line_part_attribute5: ' ,line_part_attribute5);
1668 ec_debug.pl(3,'line_part_attribute6: ' ,line_part_attribute6);
1669 ec_debug.pl(3,'line_part_attribute7: ' ,line_part_attribute7);
1670 ec_debug.pl(3,'line_part_attribute8: ' ,line_part_attribute8);
1671 ec_debug.pl(3,'line_part_attribute9: ' ,line_part_attribute9);
1672 ec_debug.pl(3,'line_part_attribute10: ' ,line_part_attribute10);
1673 ec_debug.pl(3,'line_part_attribute11: ' ,line_part_attribute11);
1674 ec_debug.pl(3,'line_part_attribute12: ' ,line_part_attribute12);
1675 ec_debug.pl(3,'line_part_attribute13: ' ,line_part_attribute13);
1676 ec_debug.pl(3,'line_part_attribute14: ' ,line_part_attribute14);
1677 ec_debug.pl(3,'line_part_attribute15: ' ,line_part_attribute15);
1678 end if;
1679
1680 xProgress := 'POOB-10-1591';
1681 l_line_tbl(nLp_att_cat_pos).value := line_part_attrib_category;
1682 l_line_tbl(nLp_att1_pos).value := line_part_attribute1;
1683 l_line_tbl(nLp_att2_pos).value := line_part_attribute2;
1684 l_line_tbl(nLp_att3_pos).value := line_part_attribute3;
1685 l_line_tbl(nLp_att4_pos).value := line_part_attribute4;
1686 l_line_tbl(nLp_att5_pos).value := line_part_attribute5;
1687 l_line_tbl(nLp_att6_pos).value := line_part_attribute6;
1688 l_line_tbl(nLp_att7_pos).value := line_part_attribute7;
1689 l_line_tbl(nLp_att8_pos).value := line_part_attribute8;
1690 l_line_tbl(nLp_att9_pos).value := line_part_attribute9;
1691 l_line_tbl(nLp_att10_pos).value := line_part_attribute10;
1692 l_line_tbl(nLp_att11_pos).value := line_part_attribute11;
1693 l_line_tbl(nLp_att12_pos).value := line_part_attribute12;
1694 l_line_tbl(nLp_att13_pos).value := line_part_attribute13;
1695 l_line_tbl(nLp_att14_pos).value := line_part_attribute14;
1696 l_line_tbl(nLp_att15_pos).value := line_part_attribute15;
1697
1698 xProgress := 'POOB-10-1600';
1699 c_file_common_key := RPAD(SUBSTRB(NVL
1700 (l_header_tbl(nTrans_code_pos).value,' '),
1701 1,
1702 25),25) ||
1703 RPAD(SUBSTRB(NVL
1704 (l_header_tbl(n_header_common_key_pos).value,' '),
1705 1,
1706 22),22) ||
1707 RPAD(SUBSTRB(NVL
1708 (l_line_tbl(n_line_common_key_pos).value,' '),
1709 1,
1710 22),22) ||
1711 RPAD(' ',22);
1712
1713 if ec_debug.G_debug_level = 3 then
1714 ec_debug.pl(3,'c_file_common_key: ',c_file_common_key);
1715 end if;
1716
1717 xProgress := 'POOB-10-1620';
1718 ece_poo_x.populate_ext_line(l_line_fkey,l_line_tbl);
1719 -- 2823215
1720 xProgress := 'POOB-10-1621';
1721 ece_poo_transaction.write_to_file( cTransaction_Type,
1722 cCommunication_Method,
1723 cLine_Interface,
1724 l_line_tbl,
1725 iOutput_width,
1726 iRun_id,
1727 c_file_common_key,
1728 l_line_fkey);
1729
1730 --2823215
1731 -- Insert into Interface Table
1732 /* xProgress := 'POOB-10-1600';
1733 ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,cTransaction_Type,cCommunication_Method,cLine_Interface,l_line_tbl,l_line_fkey); */
1734
1735
1736
1737 /***************************
1738 * Line LEVEL Attachments *
1739 ***************************/
1740 IF v_line_att_enabled = 'Y' THEN
1741 xProgress := 'POOB-10-1621';
1742 /* IF l_document_type = 'NR' THEN -- If this is a Release PO.
1743 xProgress := 'POOB-10-1622';
1744 v_entity_name := 'PO_SHIPMENTS';
1745 v_pk1_value := l_line_tbl(nLine_Location_ID_pos).value; -- LINE_LOCATION_ID
1746 if ec_debug.G_debug_level = 3 then
1747 ec_debug.pl(3,'PO_LINE_LOCATION_ID: ',l_line_tbl(nLine_Location_ID_pos).value);
1748 end if;
1749 ELSE -- If this is a non-Release PO. */ --Bug 2187958
1750 xProgress := 'POOB-10-1623';
1751 v_entity_name := 'PO_LINES';
1752 v_pk1_value := l_line_tbl(nLine_key_pos).value; -- LINE_ID
1753 if ec_debug.G_debug_level = 3 then
1754 ec_debug.pl(3,'PO_LINE_ID: ',l_line_tbl(nLine_key_pos).value);
1755 -- end if;
1756 END IF;
1757
1758 xProgress := 'POOB-10-1624';
1759 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1760 cTransaction_Type,
1761 iRun_id,
1762 5,
1763 6,
1764 cAtt_Header_Interface,
1765 cAtt_Detail_Interface,
1766 v_entity_name,
1767 'VENDOR',
1768 v_pk1_value,
1769 C_ANY_VALUE,
1770 C_ANY_VALUE,
1771 C_ANY_VALUE,
1772 C_ANY_VALUE,
1773 n_att_seg_size,
1774 l_key_tbl,
1775 c_file_common_key,
1776 l_ln_att_hdr_tbl,
1777 l_ln_att_dtl_tbl,
1778 iAtt_ln_pos);
1779
1780 END IF;
1781
1782 /***************************
1783 * Master Org Attachments *
1784 ***************************/
1785 IF v_mitem_att_enabled = 'Y' THEN
1786 xProgress := 'POOB-10-1625';
1787 v_entity_name := 'MTL_SYSTEM_ITEMS';
1788 v_pk1_value := nOrganization_ID; -- Master Inventory Org ID
1789
1790 v_pk2_value := l_line_tbl(nitem_id_pos).value; -- Item ID
1791 if ec_debug.G_debug_level = 3 then
1792 ec_debug.pl(3,'Master Org ID: ',v_pk1_value);
1793 ec_debug.pl(3,'Item ID: ',v_pk2_value);
1794 end if;
1795
1796 xProgress := 'POOB-10-1626';
1797 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1798 cTransaction_Type,
1799 iRun_id,
1800 7,
1801 8,
1802 cAtt_Header_Interface,
1803 cAtt_Detail_Interface,
1804 v_entity_name,
1805 'VENDOR',
1806 v_pk1_value,
1807 v_pk2_value,
1808 NULL,
1809 NULL,
1810 NULL,
1811 n_att_seg_size,
1812 l_key_tbl,
1813 c_file_common_key,
1814 l_mi_att_hdr_tbl,
1815 l_mi_att_dtl_tbl,
1816 iAtt_mi_pos);
1817 END IF;
1818
1819 /******************************
1820 * Inventory Org Attachments *
1821 ******************************/
1822 /* Bug 3550723
1823 IF v_iitem_att_enabled = 'Y' THEN
1824 xProgress := 'POOB-10-1627';
1825 v_entity_name := 'MTL_SYSTEM_ITEMS';
1826 v_pk2_value := l_line_tbl(nitem_id_pos).value; -- Item ID
1827 if ec_debug.G_debug_level = 3 then
1828 ec_debug.pl(3,'Item ID: ',v_pk2_value);
1829 end if;
1830
1831 xProgress := 'POOB-10-1628';
1832 FOR v_org_id IN c_org_id(l_line_tbl(nLine_key_pos).value) LOOP -- Value passed is the Line ID
1833 IF v_org_id.ship_to_organization_id <> nOrganization_ID THEN -- Only do this if it is not the same as the Master Org ID
1834 v_pk1_value := v_org_id.ship_to_organization_id;
1835 if ec_debug.G_debug_level = 3 then
1836 ec_debug.pl(3,'Inventory Org ID: ',v_pk1_value);
1837 end if;
1838
1839 xProgress := 'POOB-10-1626';
1840 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1841 cTransaction_Type,
1842 iRun_id,
1843 9,
1844 10,
1845 cAtt_Header_Interface,
1846 cAtt_Detail_Interface,
1847 v_entity_name,
1848 'VENDOR',
1849 v_pk1_value,
1850 v_pk2_value,
1851 NULL,
1852 NULL,
1853 NULL,
1854 n_att_seg_size,
1855 l_key_tbl,
1856 c_file_common_key,
1857 l_msi_att_hdr_tbl,
1858 l_msi_att_dtl_tbl,
1859 iAtt_msi_pos);
1860 END IF;
1861 END LOOP;
1862 END IF;
1863 */
1864
1865 -- **********************
1866 -- set LINE_NUMBER values
1867 -- **********************
1868 -- Removed based on bug:3957851
1869 -- xProgress := 'POOB-10-1627';
1870 -- dbms_sql.bind_variable(shipment_sel_c,'shipment_number',l_line_tbl(nLine_num_pos).value);
1871
1872 xProgress := 'POOB-10-1630';
1873 dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1874
1875 xProgress := 'POOB-10-1640';
1876 dummy := dbms_sql.execute(shipment_sel_c);
1877
1878 -- *************************
1879 -- Shipment loop starts here
1880 -- *************************
1881 xProgress := 'POOB-10-1650';
1882 WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipment
1883
1884 -- ****************************
1885 -- store values in pl/sql table
1886 -- ****************************
1887 xProgress := 'POOB-10-1660';
1888 ece_flatfile_pvt.assign_column_value_to_tbl(shipment_sel_c,iHeader_count + iLine_count,l_shipment_tbl,l_key_tbl);
1889
1890 xProgress := 'POOB-10-1670';
1891 BEGIN
1892 SELECT ece_poo_shipment_s.NEXTVAL INTO l_shipment_fkey
1893 FROM DUAL;
1894
1895 EXCEPTION
1896 WHEN NO_DATA_FOUND THEN
1897 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_POO_SHIPMENT_S');
1898 END;
1899 if ec_debug.G_debug_level = 3 then
1900 ec_debug.pl(3, 'l_shipment_fkey: ',l_shipment_fkey);
1901 end if;
1902
1903 l_shipment_tbl(nLine_Location_uom_pos).value := l_line_tbl(nLine_uom_code_pos).value; -- bug 2823215
1904
1905 l_shipment_tbl(nShip_Release_Num_pos).value := l_header_tbl(nRelease_num_pos).value; -- bug 2823215
1906
1907 l_shipment_tbl(nShp_uom_pos).value := l_line_tbl(nLine_uom_pos).value;
1908
1909 xProgress := 'POOB-TZ-3500';
1910 ece_timezone_api.get_server_timezone_details
1911 (
1912 to_date(l_shipment_tbl(nShip_need_dt_pos).value,'YYYYMMDD HH24MISS'),
1913 l_shipment_tbl(nShip_need_off_pos).value,
1914 l_shipment_tbl(nShip_need_tz_pos).value
1915 );
1916
1917 xProgress := 'POOB-TZ-3510';
1918 ece_timezone_api.get_server_timezone_details
1919 (
1920 to_date(l_shipment_tbl(nShip_prom_dt_pos).value,'YYYYMMDD HH24MISS'),
1921 l_shipment_tbl(nShip_prom_off_pos).value,
1922 l_shipment_tbl(nShip_prom_tz_pos).value
1923 );
1924
1925 xProgress := 'POOB-TZ-3520';
1926 ece_timezone_api.get_server_timezone_details
1927 (
1928 to_date(l_shipment_tbl(nShip_accept_dt_pos).value,'YYYYMMDD HH24MISS'),
1929 l_shipment_tbl(nShip_accept_off_pos).value,
1930 l_shipment_tbl(nShip_accept_tz_pos).value
1931 );
1932
1933 xProgress := 'POOB-TZ-3530';
1934 ece_timezone_api.get_server_timezone_details
1935 (
1936 to_date(l_shipment_tbl(nShp_can_dt_pos).value,'YYYYMMDD HH24MISS'),
1937 l_shipment_tbl(nShp_can_off_pos).value,
1938 l_shipment_tbl(nShp_can_tz_pos).value
1939 );
1940
1941 xProgress := 'POOB-TZ-3540';
1942 ece_timezone_api.get_server_timezone_details
1943 (
1944 to_date(l_shipment_tbl(nShp_strt_dt_pos).value,'YYYYMMDD HH24MISS'),
1945 l_shipment_tbl(nShp_strt_off_pos).value,
1946 l_shipment_tbl(nShp_strt_tz_pos).value
1947 );
1948
1949 xProgress := 'POOB-TZ-3550';
1950 ece_timezone_api.get_server_timezone_details
1951 (
1952 to_date(l_shipment_tbl(nShp_end_dt_pos).value,'YYYYMMDD HH24MISS'),
1953 l_shipment_tbl(nShp_end_off_pos).value,
1954 l_shipment_tbl(nShp_end_tz_pos).value
1955 );
1956 -- pass the pl/sql table in for xref
1957 xProgress := 'POOB-10-1680';
1958 ec_code_conversion_pvt.populate_plsql_tbl_with_extval(p_api_version_number => 1.0,
1959 p_init_msg_list => init_msg_list,
1960 p_simulate => simulate,
1961 p_commit => commt,
1962 p_validation_level => validation_level,
1963 p_return_status => return_status,
1964 p_msg_count => msg_count,
1965 p_msg_data => msg_data,
1966 p_key_tbl => l_key_tbl,
1967 p_tbl => l_shipment_tbl);
1968
1969 /* xProgress := 'POOB-10-1690';
1970
1971 ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,cTransaction_Type,cCommunication_Method,cShipment_Interface,l_shipment_tbl,l_shipment_fkey); */
1972
1973 xProgress := 'POOB-10-1690';
1974 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value ,' '),1,25),25) ||
1975 RPAD(SUBSTRB(NVL(l_header_tbl(n_header_common_key_pos).value ,' '),1,22),22) ||
1976 RPAD(SUBSTRB(NVL(l_line_tbl(n_line_common_key_pos).value ,' '),1,22),22) ||
1977 RPAD(SUBSTRB(NVL(l_shipment_tbl(n_ship_common_key_pos).value,' '),1,22),22);
1978 if ec_debug.G_debug_level = 3 then
1979 ec_debug.pl(3, 'c_file_common_key: ',c_file_common_key);
1980 end if;
1981
1982
1983 xProgress := 'POOB-10-1700';
1984 ece_poo_x.populate_ext_shipment(l_shipment_fkey,l_shipment_tbl);
1985
1986
1987 -- Drop shipment
1988 xProgress := 'POOB-10-1691';
1989 v_drop_ship_flag := OE_DROP_SHIP_GRP.PO_Line_Location_Is_Drop_Ship(l_shipment_tbl(nShip_Line_Location_ID_pos).value);
1990 xProgress := 'POOB-10-1692';
1991
1992 if ec_debug.G_debug_level = 3 then
1993 ec_debug.pl(3, 'Drop Ship Flag:',v_drop_ship_flag);
1994 end if;
1995
1996 IF (v_drop_ship_flag is NOT NULL) THEN
1997
1998 v_header_cancel_flag := l_header_tbl(nHeader_Cancel_Flag_pos).value;
1999 v_line_cancel_flag := l_line_tbl(nLine_Cancel_Flag_pos).value;
2000 v_shipment_cancel_flag := l_shipment_tbl(nShipment_Cancel_Flag_pos).value;
2001
2002 if ec_debug.G_debug_level = 3 then
2003 ec_debug.pl(3,'v_header_cancel_flag:',v_header_cancel_flag);
2004 ec_debug.pl(3,'v_line_cancel_flag:',v_line_cancel_flag);
2005 ec_debug.pl(3,'v_shipment_cancel_flag:',v_shipment_cancel_flag);
2006 END IF;
2007
2008 if ((nvl(v_header_cancel_flag,'N') <> 'Y')
2009 and (nvl(v_line_cancel_flag,'N') <> 'Y')
2010 and (nvl(v_shipment_cancel_flag,'N') <> 'Y')) then
2011
2012 xProgress := 'POOB-10-1696';
2013 OE_DROP_SHIP_GRP.GET_ORDER_LINE_INFO(1.0,
2014 l_header_tbl(nHeader_key_pos).value,
2015 l_line_tbl(nLine_key_pos).value,
2016 l_shipment_tbl(nShip_Line_Location_ID_pos).value,
2017 l_header_tbl(nRelease_id_pos).value,
2018 2,
2019 rec_order_line_info,
2020 msg_data,
2021 msg_count,
2022 return_status
2023 );
2024 xProgress := 'POOB-10-1670';
2025
2026 if ec_debug.G_debug_level = 3 then
2027 ec_debug.pl(3,'Ship to Customer Name:',rec_order_line_info.ship_to_customer_name);
2028 ec_debug.pl(3,'Ship to Contact Name:',rec_order_line_info.ship_to_contact_name);
2029 ec_debug.pl(3,'Ship to Contact Phone:',rec_order_line_info.ship_to_contact_phone);
2030 ec_debug.pl(3,'Ship to Contact Fax:',rec_order_line_info.ship_to_contact_fax);
2031 ec_debug.pl(3,'Ship to Contact Email:',rec_order_line_info.ship_to_contact_email);
2032 ec_debug.pl(3,'Shipping Instructions:',rec_order_line_info.shipping_instructions);
2033 ec_debug.pl(3,'Packing Instructions:',rec_order_line_info.packing_instructions);
2034 ec_debug.pl(3,'Shipping Method:',rec_order_line_info.shipping_method);
2035 ec_debug.pl(3,'Customer PO Number:',rec_order_line_info.customer_po_number);
2036 ec_debug.pl(3,'Customer PO Line Number:',rec_order_line_info.customer_po_line_number);
2037 ec_debug.pl(3,'Customer PO Shipment Num:',rec_order_line_info.customer_po_shipment_number);
2038 ec_debug.pl(3,'Customer Item Description:',rec_order_line_info.customer_product_description);
2039 ec_debug.pl(3,'Deliver to Location:',rec_order_line_info.deliver_to_customer_location);
2040 ec_debug.pl(3,'Deliver to Customer Name:',rec_order_line_info.deliver_to_customer_name);
2041 ec_debug.pl(3,'Deliver to Contact Name:',rec_order_line_info.deliver_to_customer_name);
2042 ec_debug.pl(3,'Deliver to Contact Phone:',rec_order_line_info.deliver_to_contact_phone);
2043 ec_debug.pl(3,'Deliver to Contact Fax:',rec_order_line_info.deliver_to_contact_fax);
2044 ec_debug.pl(3,'Deliver to Customer Address:',rec_order_line_info.deliver_to_customer_address);
2045 ec_debug.pl(3,'Deliver to Contact Email:', rec_order_line_info.deliver_to_contact_email);
2046 end if;
2047
2048 -- 2823215
2049 l_shipment_tbl(nSt_cust_name_pos).value := rec_order_line_info.ship_to_customer_name;
2050 l_shipment_tbl(nSt_cont_name_pos).value := rec_order_line_info.ship_to_contact_name;
2051 l_shipment_tbl(nSt_cont_phone_pos).value := rec_order_line_info.ship_to_contact_phone;
2052 l_shipment_tbl(nSt_cont_fax_pos).value := rec_order_line_info.ship_to_contact_fax;
2053 l_shipment_tbl(nSt_cont_email_pos).value := rec_order_line_info.ship_to_contact_email;
2054 l_shipment_tbl(nShipping_Instruct_pos).value := rec_order_line_info.shipping_instructions;
2055 l_shipment_tbl(nPacking_Instruct_pos).value := rec_order_line_info.packing_instructions;
2056 l_shipment_tbl(nShipping_method_pos).value := rec_order_line_info.shipping_method;
2057 l_shipment_tbl(nCust_po_num_pos).value := rec_order_line_info.customer_po_number;
2058 l_shipment_tbl(nCust_po_line_num_pos).value := rec_order_line_info.customer_po_line_number;
2059 l_shipment_tbl(nCust_po_ship_num_pos).value := rec_order_line_info.customer_po_shipment_number;
2060 l_shipment_tbl(nCust_prod_desc_pos).value := rec_order_line_info.customer_product_description;
2061 l_shipment_tbl(nDeliv_cust_loc_pos).value := rec_order_line_info.deliver_to_customer_location;
2062 l_shipment_tbl(nDeliv_cust_name_pos).value := rec_order_line_info.deliver_to_customer_name;
2063 l_shipment_tbl(nDeliv_cont_name_pos).value := rec_order_line_info.deliver_to_contact_name;
2064 l_shipment_tbl(nDeliv_cont_phone_pos).value := rec_order_line_info.deliver_to_contact_phone;
2065 l_shipment_tbl(nDeliv_cont_fax_pos).value := rec_order_line_info.deliver_to_contact_fax;
2066 l_shipment_tbl(nDeliv_cust_addr_pos).value := rec_order_line_info.deliver_to_customer_address;
2067 l_shipment_tbl(nDeliv_cont_email_pos).value := rec_order_line_info.deliver_to_contact_email;
2068 -- 2823215
2069 end if;
2070
2071 END IF;
2072
2073 -- 2823215
2074 ece_poo_transaction.write_to_file(cTransaction_Type,
2075 cCommunication_Method,
2076 cShipment_Interface,
2077 l_shipment_tbl,
2078 iOutput_width,
2079 iRun_id,
2080 c_file_common_key,
2081 l_shipment_fkey);
2082 -- 2823215
2083 -- ********************************************************
2084 -- Call custom program stub to populate the extension table
2085 -- ********************************************************
2086 /* xProgress := 'POOB-10-1700';
2087 ece_poo_x.populate_ext_shipment(l_shipment_fkey,l_shipment_tbl); */
2088
2089 -- Shipment Level Attachment Handler
2090 xProgress := 'POOB-10-1710';
2091 IF v_ship_att_enabled = 'Y' THEN
2092 v_entity_name := 'PO_SHIPMENTS';
2093 v_pk1_value := l_shipment_tbl(nShip_Line_Location_ID_pos).value;
2094 if ec_debug.G_debug_level = 3 then
2095 ec_debug.pl(3,'Ship Level Line Location ID: ',l_shipment_tbl(nShip_Line_Location_ID_pos).value);
2096 end if;
2097
2098 xProgress := 'POOB-10-1720';
2099 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
2100 cTransaction_Type,
2101 iRun_id,
2102 12,
2103 13,
2104 cAtt_Header_Interface,
2105 cAtt_Detail_Interface,
2106 v_entity_name,
2107 'VENDOR',
2108 v_pk1_value,
2109 C_ANY_VALUE,
2110 C_ANY_VALUE,
2111 C_ANY_VALUE,
2112 C_ANY_VALUE,
2113 n_att_seg_size,
2114 l_key_tbl,
2115 c_file_common_key,
2116 l_shp_att_hdr_tbl,
2117 l_shp_att_dtl_tbl,
2118 iAtt_shp_pos); -- 2823215
2119 END IF;
2120
2121 -- Project Level Handler
2122 xProgress := 'POOB-10-1730';
2123 -- IF v_project_acct_status = 'I' THEN -- Project Accounting is Installed --Bug 1891291
2124 ece_poo_transaction.POPULATE_DISTRIBUTION_INFO(
2125 cCommunication_Method,
2126 cTransaction_Type,
2127 iRun_id,
2128 cDistribution_Interface,
2129 l_key_tbl,
2130 l_header_tbl(nHeader_key_pos).value, -- PO_HEADER_ID
2131 l_header_tbl(nRelease_id_pos).value, -- PO_RELEASE_ID
2132 l_line_tbl(nLine_key_pos).value, -- PO_LINE_ID
2133 l_shipment_tbl(nShip_Line_Location_ID_pos).value, -- LINE_LOCATION_ID
2134 c_file_common_key); --2823215
2135 -- END IF; --Bug 1891291
2136
2137
2138
2139
2140 END LOOP; -- SHIPMENT Level Loop
2141
2142 xProgress := 'POOB-10-1740';
2143 IF dbms_sql.last_row_count = 0 THEN
2144 v_LevelProcessed := 'SHIPMENT';
2145 ec_debug.pl(0,
2146 'EC',
2147 'ECE_NO_DB_ROW_PROCESSED',
2148 'PROGRESS_LEVEL',
2149 xProgress,
2150 'LEVEL_PROCESSED',
2151 v_LevelProcessed,
2152 'TRANSACTION_TYPE',
2153 cTransaction_Type);
2154 END IF;
2155
2156 END LOOP; -- LINE Level Loop
2157
2158 xProgress := 'POOB-10-1750';
2159 IF(dbms_sql.last_row_count = 0) THEN
2160 v_LevelProcessed := 'LINE';
2161 ec_debug.pl(0,
2162 'EC',
2163 'ECE_NO_DB_ROW_PROCESSED',
2164 'PROGRESS_LEVEL',
2165 xProgress,
2166 'LEVEL_PROCESSED',
2167 v_LevelProcessed,
2168 'TRANSACTION_TYPE',
2169 cTransaction_Type);
2170 END IF;
2171
2172 xHeaderCount := xHeaderCount + 1;
2173
2174 END LOOP; -- HEADER Level Loop
2175
2176 xProgress := 'POOB-10-1760';
2177 IF(dbms_sql.last_row_count = 0) THEN
2178 v_LevelProcessed := 'HEADER';
2179 ec_debug.pl(0,
2180 'EC',
2181 'ECE_NO_DB_ROW_PROCESSED',
2182 'LEVEL_PROCESSED',
2183 v_LevelProcessed,
2184 'PROGRESS_LEVEL',
2185 xProgress,
2186 'TRANSACTION_TYPE',
2187 cTransaction_Type);
2188 END IF;
2189
2190 xProgress := 'POOB-10-1770';
2191 if (ece_poo_transaction.project_sel_c>0) then --Bug 2819176
2192 dbms_sql.close_cursor(ece_poo_transaction.project_sel_c); --Bug 2490109
2193 end if;
2194 xProgress := 'POOB-10-1780';
2195 dbms_sql.close_cursor(shipment_sel_c);
2196
2197 xProgress := 'POOB-10-1790';
2198 dbms_sql.close_cursor(line_sel_c);
2199
2200 xProgress := 'POOB-10-1800';
2201 dbms_sql.close_cursor(header_sel_c);
2202 ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_POO_TRX');
2203
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
2207 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2208
2209 app_exception.raise_exception;
2210
2211 END populate_poo_trx;
2212
2213 PROCEDURE put_data_to_output_table(cCommunication_Method IN VARCHAR2,
2214 cTransaction_Type IN VARCHAR2,
2215 iOutput_width IN INTEGER,
2216 iRun_id IN INTEGER,
2217 cHeader_Interface IN VARCHAR2,
2218 cLine_Interface IN VARCHAR2,
2219 cShipment_Interface IN VARCHAR2,
2220 cDistribution_Interface IN VARCHAR2) IS
2221
2222 xProgress VARCHAR2(80);
2223 v_LevelProcessed VARCHAR2(40);
2224
2225 cAtt_Header_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_HEADERS';
2226 cAtt_Detail_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_DETAILS';
2227
2228 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
2229 l_line_tbl ece_flatfile_pvt.Interface_tbl_type;
2230 l_shipment_tbl ece_flatfile_pvt.Interface_tbl_type;
2231
2232 l_document_type VARCHAR2(30);
2233
2234 c_header_common_key_name VARCHAR2(40);
2235 c_line_common_key_name VARCHAR2(40);
2236 c_shipment_key_name VARCHAR2(40);
2237 c_file_common_key VARCHAR2(255);
2238
2239 nHeader_key_pos NUMBER;
2240 nLine_key_pos NUMBER;
2241 nShipment_key_pos NUMBER;
2242 nTrans_code_pos NUMBER;
2243
2244 Header_sel_c INTEGER;
2245 line_sel_c INTEGER;
2246 Shipment_sel_c INTEGER;
2247
2248 Header_del_c1 INTEGER;
2249 Line_del_c1 INTEGER;
2250 Shipment_del_c1 INTEGER;
2251
2252 Header_del_c2 INTEGER;
2253 Line_del_c2 INTEGER;
2254 Shipment_del_c2 INTEGER;
2255
2256 cHeader_select VARCHAR2(32000);
2257 cLine_select VARCHAR2(32000);
2258 cShipment_select VARCHAR2(32000);
2259
2260 cHeader_from VARCHAR2(32000);
2261 cLine_from VARCHAR2(32000);
2262 cShipment_from VARCHAR2(32000);
2263
2264 cHeader_where VARCHAR2(32000);
2265 cLine_where VARCHAR2(32000);
2266 cShipment_where VARCHAR2(32000);
2267
2268 cHeader_delete1 VARCHAR2(32000);
2269 cLine_delete1 VARCHAR2(32000);
2270 cShipment_delete1 VARCHAR2(32000);
2271
2272 cHeader_delete2 VARCHAR2(32000);
2273 cLine_delete2 VARCHAR2(32000);
2274 cShipment_delete2 VARCHAR2(32000);
2275
2276 iHeader_count NUMBER;
2277 iLine_count NUMBER;
2278 iShipment_count NUMBER;
2279
2280 rHeader_rowid ROWID;
2281 rLine_rowid ROWID;
2282 rShipment_rowid ROWID;
2283
2284 cHeader_X_Interface VARCHAR2(50);
2285 cLine_X_Interface VARCHAR2(50);
2286 cShipment_X_Interface VARCHAR2(50);
2287
2288 rHeader_X_rowid ROWID;
2289 rLine_X_rowid ROWID;
2290 rShipment_X_rowid ROWID;
2291
2292 iHeader_start_num INTEGER;
2293 iLine_start_num INTEGER;
2294 iShipment_start_num INTEGER;
2295 dummy INTEGER;
2296
2297 nDocument_type_pos NUMBER;
2298 nPos1 NUMBER;
2299 nTrans_id NUMBER;
2300 n_po_header_ID NUMBER;
2301 nRelease_ID NUMBER;
2302 nRelease_ID_pos NUMBER;
2303 n_po_line_ID NUMBER;
2304 nPO_Line_Location_ID_pos NUMBER;
2305 nPO_Line_Location_ID NUMBER;
2306 nLine_Location_ID_pos NUMBER;
2307 nLine_Location_ID NUMBER;
2308 nLine_num_pos NUMBER;
2309 nLine_num NUMBER;
2310 nRelease_num NUMBER;
2311 nRelease_num_pos NUMBER;
2312 nOrganization_ID NUMBER;
2313 nItem_ID_pos NUMBER;
2314 nItem_ID NUMBER;
2315
2316 v_project_acct_installed BOOLEAN;
2317 v_project_acct_short_name VARCHAR2(2) := 'PA';
2318 v_project_acct_status VARCHAR2(120);
2319 v_project_acct_industry VARCHAR2(120);
2320 v_project_acct_schema VARCHAR2(120);
2321
2322 v_entity_name VARCHAR2(120);
2323 v_pk1_value VARCHAR2(120);
2324 v_pk2_value VARCHAR2(120);
2325
2326 CURSOR c_org_id(p_line_id NUMBER) IS
2327 SELECT DISTINCT ship_to_organization_id
2328 FROM po_line_locations
2329 WHERE po_line_id = p_line_id;
2330
2331 BEGIN
2332 if ec_debug.G_debug_level = 3 then
2333 ec_debug.push('ECE_POO_TRANSACTION.PUT_DATA_TO_OUTPUT_TABLE');
2334 ec_debug.pl(3,'cCommunication_Method: ',cCommunication_Method);
2335 ec_debug.pl(3,'cTransaction_Type: ' ,cTransaction_Type);
2336 ec_debug.pl(3,'iOutput_width: ' ,iOutput_width);
2337 ec_debug.pl(3,'iRun_id: ' ,iRun_id);
2338 ec_debug.pl(3,'cHeader_Interface: ' ,cHeader_Interface);
2339 ec_debug.pl(3,'cLine_Interface: ' ,cLine_Interface);
2340 ec_debug.pl(3,'cShipment_Interface: ' ,cShipment_Interface);
2341 end if;
2342
2343 BEGIN
2344 SELECT inventory_organization_id
2345 INTO norganization_id
2346 FROM financials_system_parameters;
2347
2348 EXCEPTION
2349 WHEN NO_DATA_FOUND THEN
2350 ec_debug.pl(0,
2351 'EC',
2352 'ECE_NO_ROW_SELECTED',
2353 'PROGRESS_LEVEL',
2354 xProgress,
2355 'INFO',
2356 'INVENTORY ORGANIZATION ID',
2357 'TABLE_NAME',
2358 'FINANCIALS_SYSTEM_PARAMETERS');
2359 END;
2360 if ec_debug.G_debug_level = 3 then
2361 ec_debug.pl(3,'norganization_id: ',norganization_id);
2362 end if;
2363
2364 -- Let's See if Project Accounting is Installed
2365 /* xProgress := 'POOB-20-1000';
2366 v_project_acct_installed := fnd_installation.get_app_info(
2367 v_project_acct_short_name, -- i.e. 'PA'
2368 v_project_acct_status, -- 'I' means it's installed
2369 v_project_acct_industry,
2370 v_project_acct_schema);
2371
2372 v_project_acct_status := NVL(v_project_acct_status,'X');
2373 ec_debug.pl(3,'v_project_acct_status: ' ,v_project_acct_status);
2374 ec_debug.pl(3,'v_project_acct_industry: ',v_project_acct_industry);
2375 ec_debug.pl(3,'v_project_acct_schema: ' ,v_project_acct_schema);
2376 */
2377 xProgress := 'POOB-20-1005';
2378 ece_flatfile_pvt.select_clause(cTransaction_Type,
2379 cCommunication_Method,
2380 cHeader_Interface,
2381 cHeader_X_Interface,
2382 l_header_tbl,
2383 c_header_common_key_name,
2384 cHeader_select,
2385 cHeader_from,
2386 cHeader_where);
2387
2388 xProgress := 'POOB-20-1010';
2389 ece_flatfile_pvt.select_clause(cTransaction_Type,
2390 cCommunication_Method,
2391 cLine_Interface,
2392 cLine_X_Interface,
2393 l_line_tbl,
2394 c_line_common_key_name,
2395 cLine_select,
2396 cLine_from,
2397 cLine_where);
2398
2399 xProgress := 'POOB-20-1020';
2400 ece_flatfile_pvt.select_clause(cTransaction_Type,
2401 cCommunication_Method,
2402 cShipment_Interface,
2403 cShipment_X_Interface,
2404 l_shipment_tbl,
2405 c_shipment_key_name,
2406 cShipment_select,
2407 cShipment_from,
2408 cShipment_where);
2409
2410 -- Header Level Find Positions
2411 xProgress := 'POOB-20-1021';
2412 ece_flatfile_pvt.find_pos(l_header_tbl,ece_flatfile_pvt.G_Translator_Code,nTrans_code_pos);
2413
2414 xProgress := 'POOB-20-1022';
2415 ece_flatfile_pvt.find_pos(l_header_tbl,c_header_common_key_name,nHeader_key_pos);
2416
2417
2418 xProgress := 'POOB-20-1023';
2419 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_NUMBER',nRelease_num_pos);
2420
2421 xProgress := 'POOB-20-1024';
2422 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_RELEASE_ID',nRelease_ID_pos);
2423
2424 xProgress := 'POOB-20-1025';
2425 ece_flatfile_pvt.find_pos(l_header_tbl,'DOCUMENT_TYPE',nDocument_type_pos);
2426
2427 -- Line Level Find Positions
2428 xProgress := 'POOB-20-1026';
2429 ece_flatfile_pvt.find_pos(l_line_tbl,c_line_common_key_name,nLine_key_pos);
2430
2431 xProgress := 'POOB-20-1027';
2432 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_NUMBER',nLine_num_pos);
2433
2434 xProgress := 'POOB-20-1028';
2435 ece_flatfile_pvt.find_pos(l_line_tbl,'PO_LINE_LOCATION_ID',nPO_Line_Location_ID_pos);
2436
2437 xProgress := 'POOB-20-1029';
2438 ece_flatfile_pvt.find_pos(l_line_tbl,'ITEM_ID',nItem_ID_pos);
2439
2440 -- Shipment Level Find Positions
2441 xProgress := 'POOB-20-1030';
2442 ece_flatfile_pvt.find_pos(l_shipment_tbl,'LINE_LOCATION_ID',nLine_Location_ID_pos);
2443
2444 xProgress := 'POOB-20-1032';
2445 ece_flatfile_pvt.find_pos(l_shipment_tbl,c_shipment_key_name,nShipment_key_pos);
2446 if ec_debug.G_debug_level = 3 then
2447 ec_debug.pl(3,'nTrans_code_pos: ',nTrans_code_pos);
2448 ec_debug.pl(3,'nHeader_key_pos: ',nHeader_key_pos);
2449 ec_debug.pl(3,'nRelease_num_pos: ',nRelease_num_pos);
2450 ec_debug.pl(3,'nRelease_ID_pos: ',nRelease_ID_pos);
2451 ec_debug.pl(3,'nDocument_type_pos: ',nDocument_type_pos);
2452 ec_debug.pl(3,'nLine_key_pos: ',nLine_key_pos);
2453 ec_debug.pl(3,'nLine_num_pos: ',nLine_num_pos);
2454 ec_debug.pl(3,'nPO_Line_Location_ID_pos: ',nPO_Line_Location_ID_pos);
2455 ec_debug.pl(3,'nItem_ID_pos: ',nItem_ID_pos);
2456 ec_debug.pl(3,'nLine_Location_ID_pos: ',nLine_Location_ID_pos);
2457 ec_debug.pl(3,'nShipment_key_pos: ',nShipment_key_pos);
2458 end if;
2459 -- Build SELECT Statement
2460 xProgress := 'POOB-20-1035';
2461 cHeader_where := cHeader_where ||
2462 ' AND ' ||
2463 cHeader_Interface ||
2464 '.run_id = ' ||
2465 ':Run_id';
2466
2467 cLine_where := cLine_where ||
2468 ' AND ' ||
2469 cLine_Interface ||
2470 '.run_id = ' ||
2471 ':Run_id' ||
2472 ' AND ' ||
2473 cLine_Interface ||
2474 '.po_header_id = :po_header_id AND ' ||
2475 cLine_Interface ||
2476 '.release_number = :por_release_num';
2477
2478 cShipment_where := cShipment_where ||
2479 ' AND ' ||
2480 cShipment_Interface ||
2481 '.run_id =' ||
2482 ':Run_id' ||
2483 ' AND ' ||
2484 cShipment_Interface ||
2485 '.po_header_id = :po_header_id AND ' ||
2486 cShipment_Interface ||
2487 '.po_line_id = :po_line_id AND ' ||
2488 cShipment_Interface ||
2489 '.release_number = :por_release_num AND ((' ||
2490 cShipment_Interface ||
2491 '.release_number = 0) OR (' ||
2492 cShipment_Interface ||
2493 '.release_number <> 0 AND ' ||
2494 cShipment_Interface ||
2495 '.shipment_number = :shipment_number))';
2496
2497 xProgress := 'POOB-20-1040';
2498 cHeader_select := cHeader_select ||
2499 ',' ||
2500 cHeader_Interface ||
2501 '.rowid,' ||
2502 cHeader_X_Interface ||
2503 '.rowid,' ||
2504 cHeader_Interface ||
2505 '.po_header_id,' ||
2506 cHeader_Interface ||
2507 '.release_number ';
2508
2509 cLine_select := cLine_select ||
2510 ',' ||
2511 cLine_Interface ||
2512 '.rowid,' ||
2513 cLine_X_Interface ||
2514 '.rowid,' ||
2515 cLine_Interface ||
2516 '.po_line_id,' ||
2517 cLine_Interface ||
2518 '.line_number ';
2519
2520 cShipment_select := cShipment_select ||
2521 ',' ||
2522 cShipment_Interface ||
2523 '.rowid,' ||
2524 cShipment_X_Interface ||
2525 '.rowid,' ||
2526 cShipment_Interface ||
2527 '.shipment_number ';
2528
2529 xProgress := 'POOB-20-1050';
2530 cHeader_select := cHeader_select ||
2531 cHeader_from ||
2532 cHeader_where ||
2533 ' ORDER BY ' ||
2534 cHeader_Interface ||
2535 '.po_header_id,' ||
2536 cHeader_Interface ||
2537 '.release_number ' ||
2538 ' FOR UPDATE';
2539 ec_debug.pl(3,'cHeader_select: ',cHeader_select);
2540
2541 cLine_select := cLine_select ||
2542 cLine_from ||
2543 cLine_where ||
2544 ' ORDER BY ' ||
2545 cLine_Interface ||
2546 '.line_number ' ||
2547 ' FOR UPDATE';
2548 ec_debug.pl(3, 'cLine_select: ',cLine_select);
2549
2550 cShipment_select := cShipment_select ||
2551 cShipment_from ||
2552 cShipment_where ||
2553 ' ORDER BY ' ||
2554 cShipment_Interface ||
2555 '.shipment_number ' ||
2556 ' FOR UPDATE';
2557 ec_debug.pl(3, 'cShipment_select: ',cShipment_select);
2558
2559 xProgress := 'POOB-20-1060';
2560 cHeader_delete1 := 'DELETE FROM ' || cHeader_Interface || ' WHERE rowid = :col_rowid';
2561
2562 cLine_delete1 := 'DELETE FROM ' || cLine_Interface || ' WHERE rowid = :col_rowid';
2563
2564 cShipment_delete1 := 'DELETE FROM ' || cShipment_Interface || ' WHERE rowid = :col_rowid';
2565
2566 xProgress := 'POOB-20-1070';
2567 cHeader_delete2 := 'DELETE FROM ' || cHeader_X_Interface || ' WHERE rowid = :col_rowid';
2568
2569 cLine_delete2 := 'DELETE FROM ' || cLine_X_Interface || ' WHERE rowid = :col_rowid';
2570
2571 cShipment_delete2 := 'DELETE FROM ' || cShipment_X_Interface || ' WHERE rowid = :col_rowid';
2572 if ec_debug.G_debug_level = 3 then
2573 ec_debug.pl(3,'cHeader_delete1: ',cHeader_delete1);
2574 ec_debug.pl(3,'cLine_delete1: ',cLine_delete1);
2575 ec_debug.pl(3,'cShipment_delete1: ',cShipment_delete1);
2576 ec_debug.pl(3,'cHeader_delete2: ',cHeader_delete2);
2577 ec_debug.pl(3,'cLine_delete2: ',cLine_delete2);
2578 ec_debug.pl(3,'cShipment_delete2: ',cShipment_delete2);
2579 end if;
2580
2581 -- ***************************************************
2582 -- *** Get data setup for the dynamic SQL call.
2583 -- *** Open a cursor for each of the SELECT call
2584 -- *** This tells the database to reserve spaces
2585 -- *** for the data returned by the SQL statement
2586 -- ***************************************************
2587 xProgress := 'POOB-20-1080';
2588 Header_sel_c := dbms_sql.open_cursor;
2589
2590 xProgress := 'POOB-20-1090';
2591 line_sel_c := dbms_sql.open_cursor;
2592
2593 xProgress := 'POOB-20-1100';
2594 Shipment_sel_c := dbms_sql.open_cursor;
2595
2596 xProgress := 'POOB-20-1110';
2597 Header_del_c1 := dbms_sql.open_cursor;
2598
2599 xProgress := 'POOB-20-1120';
2600 Line_del_c1 := dbms_sql.open_cursor;
2601
2602 xProgress := 'POOB-20-1130';
2603 Shipment_del_c1 := dbms_sql.open_cursor;
2604
2605 xProgress := 'POOB-20-1140';
2606 Header_del_c2 := dbms_sql.open_cursor;
2607
2608 xProgress := 'POOB-20-1150';
2609 Line_del_c2 := dbms_sql.open_cursor;
2610
2611 xProgress := 'POOB-20-1160';
2612 Shipment_del_c2 := dbms_sql.open_cursor;
2613
2614 -- *****************************************
2615 -- Parse each of the SELECT statement
2616 -- so the database understands the command
2617 -- *****************************************
2618 xProgress := 'POOB-20-1170';
2619 BEGIN
2620 dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
2621
2622 EXCEPTION
2623 WHEN OTHERS THEN
2624 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
2625 app_exception.raise_exception;
2626
2627 END;
2628
2629 xProgress := 'POOB-20-1180';
2630 BEGIN
2631 dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
2632
2633 EXCEPTION
2634 WHEN OTHERS THEN
2635 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
2636 app_exception.raise_exception;
2637
2638 END;
2639
2640 xProgress := 'POOB-20-1190';
2641 BEGIN
2642 dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
2643
2644 EXCEPTION
2645 WHEN OTHERS THEN
2646 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
2647 app_exception.raise_exception;
2648
2649 END;
2650
2651 xProgress := 'POOB-20-1200';
2652 BEGIN
2653 dbms_sql.parse(header_del_c1,cHeader_delete1,dbms_sql.native);
2654
2655 EXCEPTION
2656 WHEN OTHERS THEN
2657 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
2658 app_exception.raise_exception;
2659
2660 END;
2661
2662 xProgress := 'POOB-20-1210';
2663 BEGIN
2664 dbms_sql.parse(line_del_c1,cLine_delete1,dbms_sql.native);
2665
2666 EXCEPTION
2667 WHEN OTHERS THEN
2668 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete1);
2669 app_exception.raise_exception;
2670
2671 END;
2672
2673 xProgress := 'POOB-20-1220';
2674 BEGIN
2675 dbms_sql.parse(shipment_del_c1,cShipment_delete1,dbms_sql.native);
2676
2677 EXCEPTION
2678 WHEN OTHERS THEN
2679 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete1);
2680 app_exception.raise_exception;
2681
2682 END;
2683
2684 xProgress := 'POOB-20-1230';
2685 BEGIN
2686 dbms_sql.parse(header_del_c2,cHeader_delete2,dbms_sql.native);
2687
2688 EXCEPTION
2689 WHEN OTHERS THEN
2690 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
2691 app_exception.raise_exception;
2692
2693 END;
2694
2695 xProgress := 'POOB-20-1240';
2696 BEGIN
2697 dbms_sql.parse(line_del_c2,cLine_delete2,dbms_sql.native);
2698
2699 EXCEPTION
2700 WHEN OTHERS THEN
2701 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete2);
2702 app_exception.raise_exception;
2703
2704 END;
2705
2706 xProgress := 'POOB-20-1250';
2707 BEGIN
2708 dbms_sql.parse(shipment_del_c2,cShipment_delete2,dbms_sql.native);
2709
2710 EXCEPTION
2711 WHEN OTHERS THEN
2712 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete2);
2713 app_exception.raise_exception;
2714
2715 END;
2716
2717 -- *************
2718 -- set counter
2719 -- *************
2720 xProgress := 'POOB-20-1260';
2721 iHeader_count := l_header_tbl.COUNT;
2722 iLine_count := l_line_tbl.COUNT;
2723 iShipment_count := l_shipment_tbl.COUNT;
2724
2725 if ec_debug.G_debug_level = 3 then
2726 ec_debug.pl(3,'iHeader_count: ' ,iHeader_count);
2727 ec_debug.pl(3,'iLine_count: ' ,iLine_count);
2728 ec_debug.pl(3,'iShipment_count: ',iShipment_count);
2729 end if;
2730
2731 -- ******************************************************
2732 -- Define TYPE for every columns in the SELECT statement
2733 -- For each piece of the data returns, we need to tell
2734 -- the database what type of information it will be.
2735 -- e.g. ID is NUMBER, due_date is DATE
2736 -- However, for simplicity, we will convert
2737 -- everything to varchar2.
2738 -- ******************************************************
2739 xProgress := 'POOB-20-1270';
2740 ece_flatfile_pvt.define_interface_column_type(Header_sel_c,
2741 cHeader_select,
2742 ece_flatfile_pvt.G_MaxColWidth,
2743 l_header_tbl);
2744
2745 -- ***************************************************
2746 -- Need rowid for delete (Header Level)
2747 -- ***************************************************
2748 xProgress := 'POOB-20-1280';
2749 dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
2750
2751 xProgress := 'POOB-20-1290';
2752 dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2753
2754 xProgress := 'POOB-20-1300';
2755 dbms_sql.define_column(Header_sel_c,iHeader_count + 3,n_po_header_ID);
2756
2757 xProgress := 'POOB-20-1310';
2758 ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_flatfile_pvt.G_MaxColWidth,l_line_tbl);
2759
2760 -- ***************************************************
2761 -- Need rowid for delete (Line Level)
2762 -- ***************************************************
2763 xProgress := 'POOB-20-1320';
2764 dbms_sql.define_column_rowid(line_sel_c,iLine_count + 1,rLine_rowid);
2765
2766 xProgress := 'POOB-20-1330';
2767 dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2768
2769 xProgress := 'POOB-20-1340';
2770 dbms_sql.define_column(line_sel_c,iLine_count + 3,n_po_line_ID);
2771
2772 xProgress := 'POOB-20-1350';
2773 ece_flatfile_pvt.define_interface_column_type(Shipment_sel_c,cShipment_select,ece_flatfile_pvt.G_MaxColWidth,l_shipment_tbl);
2774
2775 -- ***************************************************
2776 -- Need rowid for delete (Shipment Level)
2777 -- ***************************************************
2778 xProgress := 'POOB-20-1360';
2779 dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 1,rShipment_rowid);
2780
2781 xProgress := 'POOB-20-1370';
2782 dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
2783
2784 -- ************************************************************
2785 -- *** The following is custom tailored for this transaction
2786 -- *** It find the values and use them in the WHERE clause to
2787 -- *** join tables together.
2788 -- ************************************************************
2789 -- *******************************************
2790 -- To complete the Line SELECT statement,
2791 -- we will need values for the join condition.
2792 -- *******************************************
2793 xProgress := 'POOB-20-1375';
2794 dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
2795 dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2796 dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2797
2798 --- EXECUTE the SELECT statement
2799 xProgress := 'POOB-20-1380';
2800 dummy := dbms_sql.execute(Header_sel_c);
2801
2802 -- ********************************************************************
2803 -- *** With data for each HEADER line, populate the ECE_OUTPUT table
2804 -- *** then populate ECE_OUTPUT with data from all LINES that belongs
2805 -- *** to the HEADER. Then populate ECE_OUTPUT with data from all
2806 -- *** LINE TAX that belongs to the LINE.
2807 -- ********************************************************************
2808
2809 -- HEADER - LINE - SHIPMENT ...
2810 xProgress := 'POOB-20-1390';
2811 WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP -- Header
2812 -- ******************************
2813 -- store values in pl/sql table
2814 -- ******************************
2815 xProgress := 'POOB-20-1400';
2816 ece_flatfile_pvt.assign_column_value_to_tbl(header_sel_c,l_header_tbl);
2817
2818 xProgress := 'POOB-20-1410';
2819 dbms_sql.column_value(header_sel_c,iHeader_count + 1,rHeader_rowid);
2820
2821 xProgress := 'POOB-20-1420';
2822 dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2823
2824 xProgress := 'POOB-20-1430';
2825 dbms_sql.column_value(header_sel_c,iHeader_count + 3,n_po_header_id);
2826
2827 xProgress := 'POOB-20-1440';
2828 nRelease_num := l_header_tbl(nRelease_num_pos).value;
2829
2830 xProgress := 'POOB-20-1450';
2831 nRelease_ID := l_header_tbl(nRelease_id_pos).value;
2832 if ec_debug.G_debug_level = 3 then
2833 ec_debug.pl(3,'nRelease_num: ',nRelease_num);
2834 ec_debug.pl(3,'nRelease_ID: ',nRelease_ID);
2835 end if;
2836
2837 BEGIN
2838 xProgress := 'POOB-20-1455';
2839 /* Bug 2396394 Added the document type CONTRACT in SQL below */
2840
2841 SELECT DECODE(l_header_tbl(nDocument_type_pos).value,
2842 'BLANKET' ,'NB',
2843 'STANDARD' ,'NS',
2844 'PLANNED' ,'NP',
2845 'RELEASE' ,'NR',
2846 'BLANKET RELEASE' ,'NR',
2847 'CONTRACT' ,'NC',
2848 'NR')
2849 INTO l_document_type
2850 FROM DUAL;
2851
2852 EXCEPTION
2853 WHEN NO_DATA_FOUND THEN
2854 ec_debug.pl(0,
2855 'EC',
2856 'ECE_DECODE_FAILED',
2857 'PROGRESS_LEVEL',
2858 xProgress,
2859 'CODE',
2860 l_header_tbl(nDocument_type_pos).value);
2861 END;
2862 ec_debug.pl(3,'l_document_type: ',l_document_type);
2863
2864 xProgress := 'POOB-20-1460';
2865 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),1,25),25);
2866
2867 xProgress := 'POOB-20-1470';
2868 c_file_common_key := c_file_common_key ||
2869 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value,' '),1,22),22) || RPAD(' ',22) || RPAD(' ',22);
2870
2871 ec_debug.pl(3, 'c_file_common_key: ',c_file_common_key);
2872
2873 xProgress := 'POOB-20-1480';
2874 ece_poo_transaction.write_to_file(cTransaction_Type,
2875 cCommunication_Method,
2876 cHeader_Interface,
2877 l_header_tbl,
2878 iOutput_width,
2879 iRun_id,
2880 c_file_common_key,
2881 null);
2882
2883 IF l_document_type = 'NR' THEN -- If this is a Release PO.
2884 xProgress := 'POOB-20-1481';
2885 v_entity_name := 'PO_RELEASES';
2886 v_pk1_value := nRelease_ID;
2887 if ec_debug.G_debug_level = 3 then
2888 ec_debug.pl(3,'release_id: ',nRelease_ID);
2889 end if;
2890 ELSE -- If this is a non-Release PO.
2891 xProgress := 'POOB-20-1482';
2892 v_entity_name := 'PO_HEADERS';
2893 v_pk1_value := n_po_header_id;
2894 if ec_debug.G_debug_level = 3 then
2895 ec_debug.pl(3,'po_header_id: ',n_po_header_id);
2896 end if;
2897 END IF;
2898
2899 xProgress := 'POOB-20-1483';
2900 put_att_to_output_table(cCommunication_Method,
2901 cTransaction_Type,
2902 iOutput_width,
2903 iRun_id,
2904 2,
2905 3,
2906 cAtt_Header_Interface,
2907 cAtt_Detail_Interface,
2908 v_entity_name,
2909 'VENDOR',
2910 v_pk1_value,
2911 NULL,
2912 NULL,
2913 NULL,
2914 NULL,
2915 c_file_common_key);
2916
2917 -- ***************************************************
2918 -- With Header data at hand, we can assign values to
2919 -- place holders (foreign keys) in Line_select and
2920 -- Line_detail_Select
2921 -- ***************************************************
2922 -- ******************************************
2923 -- set values into binding variables
2924 -- ******************************************
2925 xProgress := 'POOB-20-1490';
2926 dbms_sql.bind_variable(line_sel_c,'po_header_id',n_po_header_ID);
2927
2928 xProgress := 'POOB-20-1500';
2929 dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_ID);
2930
2931 xProgress := 'POOB-20-1505';
2932 dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2933
2934 xProgress := 'POOB-20-1506';
2935 dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2936
2937 xProgress := 'POOB-20-1510';
2938 dummy := dbms_sql.execute(line_sel_c);
2939
2940 -- ***************************************************
2941 -- line loop starts here
2942 -- ***************************************************
2943 xProgress := 'POOB-20-1520';
2944 WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
2945
2946 -- ***************************************************
2947 -- store values in pl/sql table
2948 -- ***************************************************
2949 xProgress := 'POOB-20-1530';
2950 ece_flatfile_pvt.assign_column_value_to_tbl(line_sel_c,l_line_tbl);
2951
2952 xProgress := 'POOB-20-1533';
2953 dbms_sql.column_value(line_sel_c,iLine_count + 1,rLine_rowid);
2954
2955 xProgress := 'POOB-20-1535';
2956 dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2957
2958 xProgress := 'POOB-20-1537';
2959 dbms_sql.column_value(line_sel_c,iLine_count + 3,n_po_line_id);
2960
2961 xProgress := 'POOB-20-1540';
2962 nLine_num := l_line_tbl(nLine_num_pos).value;
2963
2964 xProgress := 'POOB-20-1544';
2965 nPO_Line_Location_ID := l_line_tbl(nPO_Line_Location_ID_pos).value;
2966
2967 xProgress := 'POOB-20-1545';
2968 nItem_ID := l_line_tbl(nItem_id_pos).value;
2969 if ec_debug.G_debug_level = 3 then
2970 ec_debug.pl(3,'n_po_line_id: ',n_po_line_id);
2971 ec_debug.pl(3,'nLine_num: ',nLine_num);
2972 ec_debug.pl(3,'nPO_Line_Location_ID: ',nPO_Line_Location_ID);
2973 ec_debug.pl(3,'nItem_ID: ',nItem_ID);
2974 end if;
2975
2976 xProgress := 'POOB-20-1550';
2977 c_file_common_key := RPAD(SUBSTRB(NVL
2978 (l_header_tbl(nTrans_code_pos).value,' '),
2979 1,
2980 25),25) ||
2981 RPAD(SUBSTRB(NVL
2982 (l_header_tbl(nHeader_key_pos).value,' '),
2983 1,
2984 22),22) ||
2985 RPAD(SUBSTRB(NVL
2986 (l_line_tbl(nLine_key_pos).value,' '),
2987 1,
2988 22),22) ||
2989 RPAD(' ',22);
2990 if ec_debug.G_debug_level = 3 then
2991 ec_debug.pl(3,'c_file_common_key: ',c_file_common_key);
2992 end if;
2993
2994 xProgress := 'POOB-20-1551';
2995 ece_poo_transaction.write_to_file(cTransaction_Type,
2996 cCommunication_Method,
2997 cLine_Interface,
2998 l_line_tbl,
2999 iOutput_width,
3000 iRun_id,
3001 c_file_common_key,
3002 null);
3003
3004 -- Line Level Attachment Handler
3005 /* IF l_document_type = 'NR' THEN -- If this is a Release PO.
3006 xProgress := 'POOB-20-1552';
3007 v_entity_name := 'PO_SHIPMENTS';
3008 v_pk1_value := nPO_Line_Location_ID; -- LINE_LOCATION_ID
3009 ELSE -- If this is a non-Release PO.
3010 END IF;
3011 Bug 2187958
3012 */
3013 xProgress := 'POOB-20-1553';
3014 v_entity_name := 'PO_LINES';
3015 v_pk1_value := n_po_line_id; -- LINE_ID
3016
3017 xProgress := 'POOB-20-1554';
3018 put_att_to_output_table(cCommunication_Method,
3019 cTransaction_Type,
3020 iOutput_width,
3021 iRun_id,
3022 5,
3023 6,
3024 cAtt_Header_Interface,
3025 cAtt_Detail_Interface,
3026 v_entity_name,
3027 'VENDOR',
3028 v_pk1_value,
3029 NULL,
3030 NULL,
3031 NULL,
3032 NULL,
3033 c_file_common_key);
3034
3035 -- Master Item Attachment Handler
3036 xProgress := 'POOB-20-1555';
3037 v_entity_name := 'MTL_SYSTEM_ITEMS';
3038 v_pk1_value := nOrganization_ID; -- Master Inventory Org ID
3039
3040 v_pk2_value := nItem_ID; -- Item ID
3041 if ec_debug.G_debug_level = 3 then
3042 ec_debug.pl(3,'Master Org ID: ',v_pk1_value);
3043 ec_debug.pl(3,'Item ID: ',v_pk2_value);
3044 end if;
3045
3046 xProgress := 'POOB-20-1556';
3047 put_att_to_output_table(cCommunication_Method,
3048 cTransaction_Type,
3049 iOutput_width,
3050 iRun_id,
3051 7,
3052 8,
3053 cAtt_Header_Interface,
3054 cAtt_Detail_Interface,
3055 v_entity_name,
3056 'VENDOR',
3057 v_pk1_value,
3058 v_pk2_value,
3059 NULL,
3060 NULL,
3061 NULL,
3062 c_file_common_key);
3063
3064 /* Bug 3550723
3065 -- Inventory Item Attachment Handler
3066 xProgress := 'POOB-20-1557';
3067 FOR v_org_id IN c_org_id(n_po_line_id) LOOP -- Value passed is the Line ID
3068 IF v_org_id.ship_to_organization_id <> nOrganization_ID THEN -- Only do this if it is not the same as the Master Org ID
3069 v_pk1_value := v_org_id.ship_to_organization_id;
3070
3071 if ec_debug.G_debug_level = 3 then
3072 ec_debug.pl(3,'Inventory Org ID: ',v_pk1_value);
3073 end if;
3074
3075 xProgress := 'POOB-20-1558';
3076 put_att_to_output_table(cCommunication_Method,
3077 cTransaction_Type,
3078 iOutput_width,
3079 iRun_id,
3080 9,
3081 10,
3082 cAtt_Header_Interface,
3083 cAtt_Detail_Interface,
3084 v_entity_name,
3085 'VENDOR',
3086 v_pk1_value,
3087 v_pk2_value,
3088 NULL,
3089 NULL,
3090 NULL,
3091 c_file_common_key);
3092 END IF;
3093 END LOOP;
3094 */
3095
3096 -- **************************
3097 -- set LINE_NUMBER values
3098 -- **************************
3099 xProgress := 'POOB-20-1560';
3100 dbms_sql.bind_variable(shipment_sel_c,'po_line_id',n_po_line_ID);
3101
3102 xProgress := 'POOB-20-1575';
3103 dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3104
3105 xProgress := 'POOB-20-1580';
3106 dummy := dbms_sql.execute(shipment_sel_c);
3107
3108 -- ****************************
3109 -- Shipment loop starts here
3110 -- ****************************
3111 xProgress := 'POCOB-10-1590';
3112 WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipments
3113
3114 -- *********************************
3115 -- store values in pl/sql table
3116 -- *********************************
3117 xProgress := 'POCOB-10-1600';
3118 ece_flatfile_pvt.assign_column_value_to_tbl(Shipment_sel_c,l_shipment_tbl);
3119
3120 xProgress := 'POCOB-10-1603';
3121 dbms_sql.column_value(shipment_sel_c,iShipment_count + 1,rShipment_rowid);
3122
3123 xProgress := 'POCOB-10-1606';
3124 dbms_sql.column_value(shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
3125
3126 xProgress := 'POCOB-10-1610';
3127 nLine_Location_ID := l_shipment_tbl(nLine_Location_ID_pos).value;
3128 if ec_debug.G_debug_level = 3 then
3129 ec_debug.pl(3,'Ship Level Line Location ID: ',nLine_Location_ID);
3130 end if;
3131
3132 xProgress := 'POCOB-10-1620';
3133 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value ,' '),1,25),25) ||
3134 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value ,' '),1,22),22) ||
3135 RPAD(SUBSTRB(NVL(l_line_tbl(nLine_key_pos).value ,' '),1,22),22) ||
3136 RPAD(SUBSTRB(NVL(l_shipment_tbl(nShipment_key_pos).value,' '),1,22),22);
3137 if ec_debug.G_debug_level = 3 then
3138 ec_debug.pl(3, 'c_file_common_key: ',c_file_common_key);
3139 end if;
3140
3141 xProgress := 'POOB-20-1630';
3142 ece_poo_transaction.write_to_file(cTransaction_Type,
3143 cCommunication_Method,
3144 cShipment_Interface,
3145 l_shipment_tbl,
3146 iOutput_width,
3147 iRun_id,
3148 c_file_common_key,
3149 null);
3150
3151 -- Shipment Level Attachment Handler
3152 v_entity_name := 'PO_SHIPMENTS';
3153 v_pk1_value := nLine_Location_ID;
3154
3155 xProgress := 'POOB-20-1632';
3156 put_att_to_output_table(cCommunication_Method,
3157 cTransaction_Type,
3158 iOutput_width,
3159 iRun_id,
3160 12,
3161 13,
3162 cAtt_Header_Interface,
3163 cAtt_Detail_Interface,
3164 v_entity_name,
3165 'VENDOR',
3166 v_pk1_value,
3167 NULL,
3168 NULL,
3169 NULL,
3170 NULL,
3171 c_file_common_key);
3172
3173 -- Project Level Handler
3174 xProgress := 'POOB-20-1634';
3175 -- IF v_project_acct_status = 'I' THEN -- Project Accounting is Installed --bug1891291
3176 ece_poo_transaction.PUT_DISTDATA_TO_OUT_TBL(
3177 cCommunication_Method,
3178 cTransaction_Type,
3179 iOutput_width,
3180 iRun_id,
3181 cDistribution_Interface,
3182 n_po_header_ID, -- PO_HEADER_ID
3183 nRelease_ID, -- PO_RELEASE_ID
3184 n_po_line_ID, -- PO_LINE_ID
3185 nLine_Location_ID, -- LINE_LOCATION_ID
3186 c_file_common_key);
3187 -- END IF; --bug 1891291
3188
3189 xProgress := 'POOB-20-1640';
3190 dbms_sql.bind_variable(shipment_del_c1,'col_rowid',rShipment_rowid);
3191
3192 xProgress := 'POOB-20-1650';
3193 dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3194
3195 xProgress := 'POOB-20-1660';
3196 dummy := dbms_sql.execute(shipment_del_c1);
3197
3198 xProgress := 'POOB-20-1670';
3199 dummy := dbms_sql.execute(shipment_del_c2);
3200
3201 END LOOP; -- Shipment Level
3202
3203 xProgress := 'POOB-20-1674';
3204 IF dbms_sql.last_row_count = 0 THEN
3205 v_LevelProcessed := 'SHIPMENT';
3206 ec_debug.pl(0,
3207 'EC',
3208 'ECE_NO_DB_ROW_PROCESSED',
3209 'PROGRESS_LEVEL',
3210 xProgress,
3211 'LEVEL_PROCESSED',
3212 v_LevelProcessed,
3213 'TRANSACTION_TYPE',
3214 cTransaction_Type);
3215 END IF;
3216
3217 -- *********************
3218 -- Use rowid for delete
3219 -- *********************
3220 xProgress := 'POOB-20-1680';
3221 dbms_sql.bind_variable(line_del_c1,'col_rowid',rLine_rowid);
3222
3223 xProgress := 'POOB-20-1690';
3224 dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3225
3226 xProgress := 'POOB-20-1700';
3227 dummy := dbms_sql.execute(line_del_c1);
3228
3229 xProgress := 'POOB-20-1710';
3230 dummy := dbms_sql.execute(line_del_c2);
3231
3232 END LOOP; -- Line Level
3233
3234 xProgress := 'POOB-20-1714';
3235 IF dbms_sql.last_row_count = 0 THEN
3236 v_LevelProcessed := 'LINE';
3237 ec_debug.pl(0,
3238 'EC',
3239 'ECE_NO_DB_ROW_PROCESSED',
3240 'PROGRESS_LEVEL',
3241 xProgress,
3242 'LEVEL_PROCESSED',
3243 v_LevelProcessed,
3244 'TRANSACTION_TYPE',
3245 cTransaction_Type);
3246 END IF;
3247
3248 xProgress := 'POOB-20-1720';
3249 dbms_sql.bind_variable(header_del_c1,'col_rowid',rHeader_rowid);
3250
3251 xProgress := 'POOB-20-1730';
3252 dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3253
3254 xProgress := 'POOB-20-1740';
3255 dummy := dbms_sql.execute(header_del_c1);
3256
3257 xProgress := 'POOB-20-1750';
3258 dummy := dbms_sql.execute(header_del_c2);
3259
3260 END LOOP; -- Header Level
3261
3262 xProgress := 'POOB-20-1754';
3263 IF dbms_sql.last_row_count = 0 THEN
3264 v_LevelProcessed := 'HEADER';
3265 ec_debug.pl(0,
3266 'EC',
3267 'ECE_NO_DB_ROW_PROCESSED',
3268 'PROGRESS_LEVEL',
3269 xProgress,
3270 'LEVEL_PROCESSED',
3271 v_LevelProcessed,
3272 'TRANSACTION_TYPE',
3273 cTransaction_Type);
3274 END IF;
3275
3276 xProgress := 'POOB-20-1760';
3277 dbms_sql.close_cursor(header_sel_c);
3278
3279 xProgress := 'POOB-20-1770';
3280 dbms_sql.close_cursor(line_sel_c);
3281
3282 xProgress := 'POOB-20-1780';
3283 dbms_sql.close_cursor(shipment_sel_c);
3284
3285 xProgress := 'POOB-20-1790';
3286 dbms_sql.close_cursor(header_del_c1);
3287
3288 xProgress := 'POOB-20-1800';
3289 dbms_sql.close_cursor(line_del_c1);
3290
3291 xProgress := 'POOB-20-1812';
3292 dbms_sql.close_cursor(shipment_del_c1);
3293
3294 xProgress := 'POOB-20-1814';
3295 dbms_sql.close_cursor(header_del_c2);
3296
3297 xProgress := 'POOB-20-1816';
3298 dbms_sql.close_cursor(line_del_c2);
3299
3300 xProgress := 'POOB-20-1818';
3301 dbms_sql.close_cursor(shipment_del_c2);
3302
3303 -- Bug 2490109 Closing the distribution cursors.
3304 xProgress := 'POOB-50-1819';
3305 if(ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3306 dbms_sql.close_cursor(ece_poo_transaction.project_sel_c);
3307
3308 xProgress := 'POOB-50-1820';
3309 dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3310
3311 xProgress := 'POOB-50-1821';
3312 dbms_sql.close_cursor(ece_poo_transaction.project_del_c2);
3313 end if;
3314
3315 xProgress := 'POOB-20-1820';
3316 if ec_debug.G_debug_level >= 2 then
3317 ec_debug.pop('ECE_POO_TRANSACTION.PUT_DATA_TO_OUTPUT_TABLE');
3318 end if;
3319
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
3323 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3324
3325 app_exception.raise_exception;
3326
3327 END put_data_to_output_table;
3328
3329
3330 PROCEDURE update_po(
3331 document_type IN VARCHAR2,
3332 po_number IN VARCHAR2,
3333 po_type IN VARCHAR2,
3334 release_number IN VARCHAR2) IS
3335
3336 xProgress VARCHAR2(80);
3337 l_document_type VARCHAR2(25);
3338 l_document_subtype VARCHAR2(25);
3339 l_document_id NUMBER;
3340 l_header_id NUMBER;
3341 l_release_id NUMBER;
3342 l_error_code NUMBER;
3343 l_error_buf VARCHAR2(1000);
3344 l_error_stack VARCHAR2(2000);
3345
3346 BEGIN
3347 if ec_debug.G_debug_level >= 2 then
3348 ec_debug.push('ECE_POO_TRANSACTION.UPDATE_PO');
3349 ec_debug.pl(3,'document_type: ', document_type);
3350 ec_debug.pl(3,'po_number: ', po_number);
3351 ec_debug.pl(3,'po_type: ', po_type);
3352 ec_debug.pl(3,'release_number: ',release_number);
3353 end if;
3354
3355 xProgress := 'POOB-30-1000';
3356 BEGIN
3357 SELECT po_header_id
3358 INTO l_header_id
3359 FROM po_headers
3360 WHERE segment1 = po_number AND
3361 type_lookup_code = po_type;
3362
3363 EXCEPTION
3364 WHEN NO_DATA_FOUND THEN
3365 ec_debug.pl(0,'EC','ECE_NO_ROW_SELECTED','PROGRESS_LEVEL',xProgress,'INFO','PO HEADER ID','TABLE_NAME','PO_HEADERS');
3366
3367 END;
3368 if ec_debug.G_debug_level = 3 then
3369 ec_debug.pl(3,'l_header_id: ',l_header_id);
3370 end if;
3371
3372 -- Perform the first update if this is a Standard or Blanket PO
3373 xProgress := 'POOB-30-1010';
3374 IF document_type NOT IN('NR','CR') THEN
3375 xProgress := 'POOB-30-1020';
3376 UPDATE po_headers
3377 SET last_update_date = SYSDATE,
3378 printed_date = SYSDATE,
3379 print_count = NVL(print_count,0) + 1,
3380 edi_processed_flag = 'Y'
3381 WHERE po_header_id = l_header_id;
3382
3383 IF SQL%NOTFOUND THEN
3384 ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_HEADERS');
3385 END IF;
3386
3387 -- Perform the same update for the Archive Table.
3388 xProgress := 'POOB-30-1022';
3389 UPDATE po_headers_archive
3390 SET last_update_date = SYSDATE,
3391 printed_date = SYSDATE,
3392 print_count = NVL(print_count,0) + 1,
3393 edi_processed_flag = 'Y'
3394 WHERE po_header_id = l_header_id AND
3395 latest_external_flag = 'Y';
3396
3397 IF SQL%NOTFOUND THEN
3398 ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_HEADERS_ARCHIVE');
3399 END IF;
3400
3401 ELSE
3402 -- Get the po_release_id, as it is needed here for the update and
3403 -- later for the archive call
3404 xProgress := 'POOB-30-1024';
3405 BEGIN
3406 SELECT po_release_id INTO l_release_id
3407 FROM po_releases
3408 WHERE release_num = release_number AND
3409 po_header_id = l_header_id;
3410
3411 EXCEPTION
3412 WHEN NO_DATA_FOUND THEN
3413 ec_debug.pl(0,'EC','ECE_NO_ROW_SELECTED','PROGRESS_LEVEL',xProgress,'INFO','PO RELEASE ID','TABLE_NAME','PO_RELEASES');
3414
3415 END;
3416 if ec_debug.G_debug_level = 3 then
3417 ec_debug.pl(3, 'l_release_id: ',l_release_id);
3418 end if;
3419
3420 -- Perform this update if this is a Release PO
3421 xProgress := 'POOB-30-1030';
3422 UPDATE po_releases
3423 SET last_update_date = SYSDATE,
3424 printed_date = SYSDATE,
3425 print_count = NVL(print_count,0) + 1,
3426 edi_processed_flag = 'Y'
3427 WHERE po_release_id = l_release_id;
3428
3429 IF SQL%NOTFOUND THEN
3430 ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_RELEASES');
3431 END IF;
3432
3433 -- Perform the same update for the Archive Table.
3434 xProgress := 'POOB-30-1040';
3435 UPDATE po_releases_archive
3436 SET last_update_date = SYSDATE,
3437 printed_date = SYSDATE,
3438 print_count = NVL(print_count,0) + 1,
3439 edi_processed_flag = 'Y'
3440 WHERE po_release_id = l_release_id AND
3441 latest_external_flag = 'Y';
3442
3443 IF SQL%NOTFOUND THEN
3444 ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_RELEASES_ARCHIVE');
3445 END IF;
3446 END IF;
3447
3448 -- Perform archiving by calling the archive proceedure.
3449 xProgress := 'POOB-30-1050';
3450 BEGIN
3451 /* Bug 2396394 Added the document type CONTRACT(NC) in SQL below */
3452 SELECT DECODE
3453 (document_type,
3454 'NS','PO',
3455 'NC','PO',
3456 'CS','PO',
3457 'NB','PA',
3458 'CB','PA',
3459 'NP','PO',
3460 'CP','PO',
3461 'NR','RELEASE',
3462 'CR','RELEASE'),
3463 DECODE
3464 (document_type,
3465 'NR',DECODE
3466 (po_type,
3467 'PLANNED','SCHEDULED',
3468 'BLANKET','BLANKET'),
3469 'CR',DECODE(po_type,
3470 'PLANNED','SCHEDULED',
3471 'BLANKET','BLANKET'),
3472 po_type)
3473 INTO l_document_type,l_document_subtype
3474 FROM DUAL;
3475
3476 EXCEPTION
3477 WHEN NO_DATA_FOUND THEN
3478 ec_debug.pl(0,'EC','ECE_DECODE_FAILED','PROGRESS_LEVEL',xProgress,'CODE',document_type);
3479
3480 END;
3481
3482 if ec_debug.G_debug_level = 3 then
3483 ec_debug.pl(3,'l_document_type: ', l_document_type);
3484 ec_debug.pl(3,'l_document_subtype: ',l_document_subtype);
3485 end if;
3486
3487 xProgress := 'POOB-30-1060';
3488 IF document_type NOT IN ('NR','CR') THEN
3489 l_document_id := l_header_id;
3490 ELSE
3491 l_document_id := l_release_id;
3492 END IF;
3493
3494 xProgress := 'POOB-30-1090';
3495 ece_po_archive_pkg.porarchive(
3496 l_document_type,
3497 l_document_subtype,
3498 l_document_id,
3499 'PRINT',
3500 l_error_code,
3501 l_error_buf,
3502 l_error_stack);
3503
3504 xProgress := 'POOB-30-1100';
3505 -- IF l_error_code <> 0 THEN
3506 -- raise_application_error(-20000,l_error_buf || l_error_stack);
3507 -- END IF;
3508
3509 if ec_debug.G_debug_level = 3 then
3510 ec_debug.pop('ECE_POO_TRANSACTION.UPDATE_PO');
3511 end if;
3512
3513 EXCEPTION
3514 WHEN OTHERS THEN
3515 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
3516 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3517
3518 app_exception.raise_exception;
3519 END update_po;
3520
3521 PROCEDURE POPULATE_DISTRIBUTION_INFO(
3522 cCommunication_Method IN VARCHAR2,
3523 cTransaction_Type IN VARCHAR2,
3524 iRun_id IN INTEGER,
3525 cDistribution_Interface IN VARCHAR2,
3526 l_key_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
3527 cPO_Header_ID IN NUMBER,
3528 cPO_Release_ID IN NUMBER,
3529 cPO_Line_ID IN NUMBER,
3530 cPO_Line_Location_ID IN NUMBER,
3531 cFile_Common_Key IN VARCHAR2) IS
3532
3533 xProgress VARCHAR2(80);
3534 v_LevelProcessed VARCHAR(40);
3535
3536 /* Bug 2490109
3537 l_project_tbl ece_flatfile_pvt.Interface_tbl_type;
3538 project_sel_c INTEGER;
3539 */
3540 v_project_view_name VARCHAR2(120) := 'ECE_PO_DISTRIBUTIONS_V'; -- Bug 1891291
3541
3542 cProject_select VARCHAR2(32000);
3543 cProject_from VARCHAR2(32000);
3544 cProject_where VARCHAR2(32000);
3545
3546 iProject_output_level NUMBER := 14;
3547 iProject_count NUMBER := 0;
3548 --iKey_count NUMBER := 0;
3549
3550 l_project_fkey NUMBER;
3551
3552 dummy INTEGER;
3553
3554 init_msg_list VARCHAR2(20);
3555 simulate VARCHAR2(20);
3556 validation_level VARCHAR2(20);
3557 commt VARCHAR2(20);
3558 return_status VARCHAR2(20);
3559 msg_count NUMBER;
3560 msg_data VARCHAR2(2000); --3650215
3561 v_project_acct_installed BOOLEAN;
3562 v_project_acct_short_name VARCHAR2(2) := 'PA';
3563 v_project_acct_status VARCHAR2(120);
3564 v_project_acct_industry VARCHAR2(120);
3565 v_project_acct_schema VARCHAR2(120);
3566 c_project_number VARCHAR2(25);
3567 c_project_type VARCHAR2(20);
3568 c_task_number VARCHAR2(25);
3569 c_task_id NUMBER; --bug 1891291
3570 c_project_id NUMBER; --bug 1891291
3571 nTask_id_pos NUMBER; -- 2823215
3572 nProject_id_pos NUMBER;
3573 nProject_num_pos NUMBER;
3574 nProject_type_pos NUMBER;
3575 nTask_num_pos NUMBER; -- 2823215
3576 nConv_dt_pos pls_integer;
3577 nConv_tz_pos pls_integer;
3578 nConv_off_pos pls_integer;
3579 BEGIN
3580 if ec_debug.G_debug_level = 3 then
3581 ec_debug.push('ECE_POO_TRANSACTION.POPULATE_PROJECT_INFO');
3582 ec_debug.pl(3,'cCommunication_Method: ',cCommunication_Method);
3583 ec_debug.pl(3,'cTransaction_Type: ' ,cTransaction_Type);
3584 ec_debug.pl(3,'iRun_id: ' ,iRun_id);
3585 ec_debug.pl(3,'cDistribution_Interface: ' ,cDistribution_Interface);
3586 ec_debug.pl(3,'cPO_Header_ID: ' ,cPO_Header_ID);
3587 ec_debug.pl(3,'cPO_Release_ID: ' ,cPO_Release_ID);
3588 ec_debug.pl(3,'cPO_Line_ID: ' ,cPO_Line_ID);
3589 ec_debug.pl(3,'cPO_Line_Location_ID: ' ,cPO_Line_Location_ID);
3590 end if;
3591
3592 -- Initialize the PL/SQL Table
3593
3594
3595
3596 v_project_acct_installed := fnd_installation.get_app_info(
3597 v_project_acct_short_name, -- i.e. 'PA'
3598 v_project_acct_status, -- 'I' means it's installed
3599 v_project_acct_industry,
3600 v_project_acct_schema);
3601
3602 v_project_acct_status := NVL(v_project_acct_status,'X');
3603
3604 if ece_poo_transaction.project_sel_c =0 then --Bug 2490109
3605 ece_poo_transaction.project_sel_c:=-911;
3606 end if;
3607
3608 if ece_poo_transaction.project_sel_c <0 then --Bug 2490109
3609
3610 xProgress := 'POOB-40-1000';
3611 iKey_count := l_key_tbl.COUNT;
3612 if ec_debug.G_debug_level = 3 then
3613 ec_debug.pl(3,'iKey_count: ',iKey_count);
3614 END if;
3615 ece_flatfile_pvt.init_table(cTransaction_Type,cDistribution_Interface,iProject_output_level,TRUE,l_project_tbl,l_key_tbl);
3616
3617 xProgress := 'POOB-40-1010';
3618 ece_extract_utils_pub.select_clause(cTransaction_Type,
3619 cCommunication_Method,
3620 cDistribution_Interface,
3621 l_project_tbl,
3622 cProject_select,
3623 cProject_from,
3624 cProject_where);
3625
3626 -- Build the WHERE Clause
3627 xProgress := 'POOB-40-1020';
3628 cProject_where := cProject_where ||
3629 'po_header_id = :po_header_id';
3630
3631 cProject_where := cProject_where || ' AND ' ||
3632 'nvl(po_release_id,0) = :po_release_id';
3633
3634 cProject_where := cProject_where || ' AND ' ||
3635 'po_line_id = :po_line_id';
3636
3637 cProject_where := cProject_where || ' AND ' ||
3638 'line_location_id = :line_location_id';
3639
3640 xProgress := 'POOB-40-1030';
3641 cProject_where := cProject_where || ' ORDER BY distribution_num';
3642
3643 -- Combine the SELECT, FROM and WHERE Clauses
3644 xProgress := 'POOB-40-1040';
3645 cProject_select := cProject_select ||
3646 cProject_from ||
3647 cProject_where;
3648
3649 if ec_debug.G_debug_level = 3 then
3650 ec_debug.pl(3,'cProject_select: ',cProject_select);
3651 end if;
3652
3653 -- Open the Cursor
3654 xProgress := 'POOB-40-1050';
3655 project_sel_c := dbms_sql.open_cursor;
3656
3657 -- Parse the Cursor
3658 xProgress := 'POOB-40-1060';
3659 BEGIN
3660 dbms_sql.parse(project_sel_c,
3661 cProject_select,
3662 dbms_sql.native);
3663
3664 EXCEPTION
3665 WHEN OTHERS THEN
3666 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
3667 app_exception.raise_exception;
3668 END;
3669
3670 -- Set Counter
3671 xProgress := 'POOB-40-1070';
3672 iProject_count := l_project_tbl.COUNT;
3673 ec_debug.pl(3,'iProject_count: ',iProject_count);
3674
3675 xProgress := 'POOB-40-1080';
3676 --iKey_count := l_key_tbl.COUNT;
3677 --ec_debug.pl(3,'iKey_count: ',iKey_count);
3678
3679 -- Define Column Types
3680 xProgress := 'POOB-40-1090';
3681 ece_flatfile_pvt.define_interface_column_type(project_sel_c,cProject_select,ece_extract_utils_PUB.G_MaxColWidth,l_project_tbl);
3682
3683 -- Project Level Positions
3684
3685 end if; --Bug 2490109
3686 --2823215
3687 ece_flatfile_pvt.find_pos(l_project_tbl,'TASK_ID',nTask_id_pos);
3688 ece_flatfile_pvt.find_pos(l_project_tbl,'PROJECT_ID',nProject_id_pos);
3689 ece_flatfile_pvt.find_pos(l_project_tbl,'PROJECT_NUMBER',nProject_num_pos);
3690 ece_flatfile_pvt.find_pos(l_project_tbl,'PROJECT_TYPE',nProject_type_pos);
3691 ece_flatfile_pvt.find_pos(l_project_tbl,'TASK_NUMBER',nTask_num_pos);
3692
3693
3694 --2823215
3695 ece_flatfile_pvt.find_pos(l_project_tbl,'CONVERSION_DATE',nConv_dt_pos);
3696 ece_flatfile_pvt.find_pos(l_project_tbl,'CONVERSION_DT_TZ_CODE',nConv_tz_pos);
3697 ece_flatfile_pvt.find_pos(l_project_tbl,'CONVERSION_DT_OFF',nConv_off_pos);
3698
3699 if ece_poo_transaction.project_sel_c >0 then --Bug 2490109
3700 -- Bind Variables
3701 xProgress := 'POOB-40-1140';
3702 dbms_sql.bind_variable(project_sel_c,':po_header_id',NVL(cPO_Header_ID,0));
3703
3704 xProgress := 'POOB-40-1150';
3705 dbms_sql.bind_variable(project_sel_c,':po_release_id',NVL(cPO_Release_ID,0));
3706
3707 xProgress := 'POOB-40-1160';
3708 dbms_sql.bind_variable(project_sel_c,':po_line_id',NVL(cPO_Line_ID,0));
3709
3710 xProgress := 'POOB-40-1170';
3711 dbms_sql.bind_variable(project_sel_c,':line_location_id',NVL(cPO_Line_Location_ID,0));
3712
3713 -- Execute the Cursor
3714 xProgress := 'POOB-40-1180';
3715 dummy := dbms_sql.execute(project_sel_c);
3716
3717 -- Fetch Data
3718 xProgress := 'POOB-40-1190';
3719 WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
3720 -- Store Internal Values in the PL/SQL Table
3721 xProgress := 'POOB-40-1200';
3722 ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,iKey_count,l_project_tbl,l_key_tbl);
3723
3724 xProgress := 'POOB-TZ-4500';
3725
3726 ece_timezone_api.get_server_timezone_details
3727 (
3728 to_date(l_project_tbl(nConv_dt_pos).value,'YYYYMMDD HH24MISS'),
3729 l_project_tbl(nConv_off_pos).value,
3730 l_project_tbl(nConv_tz_pos).value
3731 );
3732
3733 -- Convert Internal to External Values
3734 xProgress := 'POOB-40-1210';
3735 ec_code_conversion_pvt.populate_plsql_tbl_with_extval(
3736 p_api_version_number => 1.0,
3737 p_init_msg_list => init_msg_list,
3738 p_simulate => simulate,
3739 p_commit => commt,
3740 p_validation_level => validation_level,
3741 p_return_status => return_status,
3742 p_msg_count => msg_count,
3743 p_msg_data => msg_data,
3744 p_key_tbl => l_key_tbl,
3745 p_tbl => l_project_tbl);
3746
3747 -- Get Project FKEY
3748 xProgress := 'POOB-40-1220';
3749 BEGIN
3750 SELECT ece_po_project_info_s.NEXTVAL INTO l_project_fkey
3751 FROM DUAL;
3752
3753 EXCEPTION
3754 WHEN NO_DATA_FOUND THEN
3755 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_PO_PROJECT_INFO_S');
3756
3757 END;
3758 if ec_debug.G_debug_level = 3 then
3759 ec_debug.pl(3,'l_project_fkey: ',l_project_fkey);
3760 end if;
3761
3762 -- Insert into Interface Table
3763 /* xProgress := 'POOB-40-1230';
3764 ece_extract_utils_pub.insert_into_interface_tbl(
3765 iRun_id,
3766 cTransaction_Type,
3767 cCommunication_Method,
3768 cDistribution_Interface,
3769 l_project_tbl,
3770 l_project_fkey); */
3771
3772 -- Bug 1891291 begin
3773 /* Update ECE_PO_DISTRIBUTIONS
3774 with project related data
3775 based on task_id and project_id
3776 */
3777
3778 xProgress := 'POOB-40-1240';
3779 IF cTransaction_Type = 'POO' THEN
3780 ece_poo_x.populate_ext_project(l_project_fkey,l_project_tbl);
3781 ELSIF cTransaction_Type = 'POCO' THEN
3782 ece_poco_x.populate_ext_project(l_project_fkey,l_project_tbl);
3783 END IF;
3784
3785 IF v_project_acct_status = 'I' THEN
3786 begin
3787
3788
3789 c_task_id := l_project_tbl(nTask_id_pos).value;
3790 c_project_id := l_project_tbl(nProject_id_pos).value;
3791
3792 /* select task_id,project_id into
3793 c_task_id,c_project_id
3794 from
3795 ECE_PO_DISTRIBUTIONS EPID
3796 where
3797 EPID.TRANSACTION_RECORD_ID = l_project_fkey; */
3798
3799
3800 if (c_task_id is not NULL and c_project_id is not null) then
3801 select PPE.PROJECT_NUMBER,PPE.PROJECT_TYPE,
3802 PAT.TASK_NUMBER INTO
3803 c_PROJECT_NUMBER,c_PROJECT_TYPE,
3804 c_TASK_NUMBER
3805 FROM
3806 PA_PROJECTS_EXPEND_V
3807 PPE,
3808 PA_TASKS
3809 PAT,
3810 ECE_PO_DISTRIBUTIONS
3811 EPID
3812 WHERE
3813 EPID.TASK_ID = PAT.TASK_ID (+) AND
3814 EPID.PROJECT_ID = PAT.PROJECT_ID (+) AND
3815 EPID.PROJECT_ID = PPE.PROJECT_ID (+) AND
3816 EPID.TRANSACTION_RECORD_ID = l_project_fkey;
3817
3818 --2823215
3819 l_project_tbl(nProject_num_pos).value := c_project_number;
3820 l_project_tbl(nProject_type_pos).value := c_project_type;
3821 l_project_tbl(nTask_num_pos).value := c_task_number;
3822 --2823215
3823
3824 /* UPDATE ECE_PO_DISTRIBUTIONS EPID
3825 SET EPID.PROJECT_NUMBER = c_PROJECT_NUMBER,
3826 EPID.PROJECT_TYPE = c_PROJECT_TYPE,
3827 EPID.TASK_NUMBER = c_TASK_NUMBER
3828 WHERE EPID.TRANSACTION_RECORD_ID = l_project_fkey; */
3829
3830 end if;
3831 exception
3832 when no_data_found then null;
3833 when others then null;
3834 end;
3835
3836 end if;
3837
3838 -- 2823215
3839 ece_poo_transaction.write_to_file(cTransaction_Type,
3840 cCommunication_Method,
3841 cDistribution_Interface,
3842 l_project_tbl,
3843 iOutput_width,
3844 iRun_id,
3845 cFile_Common_Key,
3846 l_project_fkey);
3847 -- 2823215
3848 -- Bug 1891291 end
3849 -- Call Custom Project Stub Depending on Transaction
3850 /* xProgress := 'POOB-40-1240';
3851 IF cTransaction_Type = 'POO' THEN
3852 ece_poo_x.populate_ext_project(l_project_fkey,l_project_tbl);
3853 ELSIF cTransaction_Type = 'POCO' THEN
3854 ece_poco_x.populate_ext_project(l_project_fkey,l_project_tbl);
3855 END IF; */
3856
3857 END LOOP;
3858
3859 -- Check to see if anything was processed
3860 xProgress := 'POOB-40-1250';
3861 IF(dbms_sql.last_row_count = 0) THEN
3862 v_LevelProcessed := 'PROJECT';
3863 ec_debug.pl(0,'EC','ECE_NO_DB_ROW_PROCESSED','LEVEL_PROCESSED',v_LevelProcessed,'PROGRESS_LEVEL',xProgress,'TRANSACTION_TYPE',cTransaction_Type);
3864 END IF;
3865
3866 end if; --Bug 2490109
3867
3868 -- Close the Cursor Bug 2490109
3869 -- xProgress := 'POOB-40-1260';
3870 -- dbms_sql.close_cursor(project_sel_c);
3871
3872 -- Tell Debug that this Procedure is Done
3873 if ec_debug.G_debug_level = 3 then
3874 ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_PROJECT_INFO');
3875 end if;
3876
3877 EXCEPTION
3878 WHEN OTHERS THEN
3879 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
3880 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3881
3882 app_exception.raise_exception;
3883
3884 END POPULATE_DISTRIBUTION_INFO;
3885
3886 PROCEDURE PUT_DISTDATA_TO_OUT_TBL(
3887 cCommunication_Method IN VARCHAR2,
3888 cTransaction_Type IN VARCHAR2,
3889 iOutput_width IN INTEGER,
3890 iRun_id IN INTEGER,
3891 cDistribution_Interface IN VARCHAR2,
3892 cPO_Header_ID IN NUMBER,
3893 cPO_Release_ID IN NUMBER,
3894 cPO_Line_ID IN NUMBER,
3895 cPO_Line_Location_ID IN NUMBER,
3896 cFile_Common_Key IN VARCHAR2) IS
3897
3898 xProgress VARCHAR2(80);
3899 v_LevelProcessed VARCHAR2(40);
3900 c_project_common_key_name VARCHAR2(40);
3901
3902 nProject_key_pos NUMBER;
3903
3904 /* Bug 2490109
3905 l_project_tbl ece_flatfile_pvt.Interface_tbl_type;
3906 project_sel_c INTEGER;
3907 project_del_c1 INTEGER;
3908 project_del_c2 INTEGER;
3909 */
3910
3911 cProject_select VARCHAR2(32000);
3912 cProject_from VARCHAR2(32000);
3913 cProject_where VARCHAR2(32000);
3914
3915 cProject_delete1 VARCHAR2(32000);
3916 cProject_delete2 VARCHAR2(32000);
3917
3918 iProject_count NUMBER;
3919
3920 rProject_rowid ROWID;
3921 rProject_X_rowid ROWID;
3922
3923 cProject_X_Interface VARCHAR2(50);
3924
3925 iProject_output_level NUMBER := 14;
3926 iProject_start_num INTEGER;
3927 dummy INTEGER;
3928
3929 BEGIN
3930 if ec_debug.G_debug_level >= 2 then
3931 ec_debug.push('ECE_POO_TRANSACTION.PUT_PROJECT_DATA_TO_OUTPUT_TBL');
3932 ec_debug.pl(3,'cCommunication_Method: ',cCommunication_Method);
3933 ec_debug.pl(3,'cTransaction_Type: ' ,cTransaction_Type);
3934 ec_debug.pl(3,'iOutput_width: ' ,iOutput_width);
3935 ec_debug.pl(3,'iRun_id: ' ,iRun_id);
3936 ec_debug.pl(3,'cDistribution_Interface: ' ,cDistribution_Interface);
3937 ec_debug.pl(3,'cPO_Header_ID: ' ,cPO_Header_ID);
3938 ec_debug.pl(3,'cPO_Release_ID: ' ,cPO_Release_ID);
3939 ec_debug.pl(3,'cPO_Line_ID: ' ,cPO_Line_ID);
3940 ec_debug.pl(3,'cPO_Line_Location_ID: ' ,cPO_Line_Location_ID);
3941 ec_debug.pl(3,'cFile_Common_Key: ' ,cFile_Common_Key);
3942 end if;
3943
3944
3945 if ece_poo_transaction.project_sel_c = 0 then -- Bug 2490109
3946 ece_poo_transaction.project_sel_c:=-911;
3947 end if;
3948
3949 if ece_poo_transaction.project_sel_c < 0 then -- Bug 2490109
3950 ece_poo_transaction.l_project_tbl.DELETE; -- Bug 2490109
3951
3952 -- Build the SELECT, FROM, and WHERE Clauses
3953 xProgress := 'POOB-50-1000';
3954 ece_flatfile_pvt.select_clause(cTransaction_Type,
3955 cCommunication_Method,
3956 cDistribution_Interface,
3957 cProject_X_Interface,
3958 l_project_tbl,
3959 c_project_common_key_name,
3960 cProject_select,
3961 cProject_from,
3962 cProject_where,
3963 iProject_output_level);
3964
3965 -- Customize the WHERE Clause
3966 xProgress := 'POOB-50-1010';
3967 cProject_where := cProject_where || ' AND ' ||
3968 cDistribution_Interface || '.run_id = :run_id';
3969
3970 cProject_where := cProject_where || ' AND NVL(' ||
3971 cDistribution_Interface || '.po_header_id,0) = :po_header_id';
3972
3973 cProject_where := cProject_where || ' AND NVL(' ||
3974 cDistribution_Interface || '.po_release_id,0) = :po_release_id';
3975
3976 cProject_where := cProject_where || ' AND NVL(' ||
3977 cDistribution_Interface || '.po_line_id,0) = :po_line_id';
3978
3979 cProject_where := cProject_where || ' AND NVL(' ||
3980 cDistribution_Interface || '.line_location_id,0) = :po_line_location_id';
3981
3982 cProject_where := cProject_where || ' ORDER BY ' ||
3983 cDistribution_Interface || '.distribution_num';
3984
3985 -- Customize the SELECT Clause
3986 cProject_select := cProject_select || ',' ||
3987 cDistribution_Interface || '.rowid' || ',' ||
3988 cProject_X_Interface || '.rowid ';
3989
3990 -- Build the Complete SQL Statement
3991 cProject_select := cProject_select ||
3992 cProject_from ||
3993 cProject_where ||
3994 ' FOR UPDATE';
3995 ec_debug.pl(3,'cProject_select: ',cProject_select);
3996
3997 -- Build First DELETE SQL Statement
3998 cProject_delete1 := 'DELETE FROM ' || cDistribution_Interface || ' WHERE rowid = :col_rowid';
3999
4000 -- Build Second DELETE SQL Statement
4001 cProject_delete2 := 'DELETE FROM ' || cProject_X_Interface || ' WHERE rowid = :col_rowid';
4002
4003 if ec_debug.G_debug_level = 3 then
4004 ec_debug.pl(3,'cProject_delete1: ',cProject_delete1);
4005 ec_debug.pl(3,'cProject_delete2: ',cProject_delete2);
4006 end if;
4007
4008 -- Open the Cursors
4009 xProgress := 'POOB-50-1020';
4010 project_sel_c := dbms_sql.open_cursor;
4011
4012 xProgress := 'POOB-50-1030';
4013 project_del_c1 := dbms_sql.open_cursor;
4014
4015 xProgress := 'POOB-50-1040';
4016 project_del_c2 := dbms_sql.open_cursor;
4017
4018 -- Parse the SQL Statements
4019 xProgress := 'POOB-50-1050';
4020 BEGIN
4021 xProgress := 'POOB-50-1060';
4022 dbms_sql.parse(project_sel_c,cProject_select,dbms_sql.native);
4023
4024 EXCEPTION
4025 WHEN OTHERS THEN
4026 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
4027 app_exception.raise_exception;
4028
4029 END;
4030
4031 xProgress := 'POOB-50-1070';
4032 BEGIN
4033 xProgress := 'POOB-50-1080';
4034 dbms_sql.parse(project_del_c1,cProject_delete1,dbms_sql.native);
4035
4036 EXCEPTION
4037 WHEN OTHERS THEN
4038 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete1);
4039 app_exception.raise_exception;
4040
4041 END;
4042
4043 xProgress := 'POOB-50-1090';
4044 BEGIN
4045 xProgress := 'POOB-50-1100';
4046 dbms_sql.parse(project_del_c2,cProject_delete2,dbms_sql.native);
4047
4048 EXCEPTION
4049 WHEN OTHERS THEN
4050 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete2);
4051 app_exception.raise_exception;
4052
4053 END;
4054
4055 end if; --Bug 2490109
4056
4057 if ece_poo_transaction.project_sel_c > 0 then -- Bug 2490109
4058
4059 -- Set the Counter Variables
4060 xProgress := 'POOB-50-1110';
4061 iProject_count := l_project_tbl.COUNT;
4062 if ec_debug.G_debug_level = 3 then
4063 ec_debug.pl(3,'iProject_count: ',iProject_count);
4064 end if;
4065
4066 -- Define Column Types
4067 xProgress := 'POOB-50-1120';
4068 ece_flatfile_pvt.define_interface_column_type(project_sel_c,
4069 cProject_select,
4070 ece_flatfile_pvt.G_MaxColWidth,
4071 l_project_tbl);
4072
4073 -- Define Additional Column Types
4074 xProgress := 'POOB-50-1130';
4075 dbms_sql.define_column_rowid(project_sel_c,iProject_count + 1,rProject_rowid);
4076
4077 xProgress := 'POOB-50-1140';
4078 dbms_sql.define_column_rowid(project_sel_c,iProject_count + 2,rProject_X_rowid);
4079
4080 -- Bind Variables
4081 xProgress := 'POOB-50-1150';
4082 dbms_sql.bind_variable(project_sel_c,':run_id', iRun_id);
4083
4084 xProgress := 'POOB-50-1160';
4085 dbms_sql.bind_variable(project_sel_c,':po_header_id', NVL(cPO_Header_ID,0));
4086
4087 xProgress := 'POOB-50-1170';
4088 dbms_sql.bind_variable(project_sel_c,':po_release_id', NVL(cPO_Release_ID,0));
4089
4090 xProgress := 'POOB-50-1180';
4091 dbms_sql.bind_variable(project_sel_c,':po_line_id', NVL(cPO_Line_ID,0));
4092
4093 xProgress := 'POOB-50-1190';
4094 dbms_sql.bind_variable(project_sel_c,':po_line_location_id',NVL(cPO_Line_Location_ID,0));
4095
4096 -- Execute the SQL Statement
4097 xProgress := 'POOB-50-1200';
4098 dummy := dbms_sql.execute(project_sel_c);
4099
4100 -- Fetch Data
4101 xProgress := 'POOB-50-1210';
4102 WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
4103 -- Store the Fetched Data in the PL/SQL Table
4104 xProgress := 'POOB-50-1220';
4105 ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,l_project_tbl);
4106
4107 xProgress := 'POOB-50-1230';
4108 dbms_sql.column_value(project_sel_c,iProject_count + 1,rProject_rowid);
4109
4110 xProgress := 'POOB-50-1240';
4111 dbms_sql.column_value(project_sel_c,iProject_count + 2,rProject_X_rowid);
4112
4113 xProgress := 'POOB-50-1250';
4114 ece_poo_transaction.write_to_file(cTransaction_Type,
4115 cCommunication_Method,
4116 cDistribution_Interface,
4117 l_project_tbl,
4118 iOutput_width,
4119 iRun_id,
4120 cFile_Common_Key,
4121 null);
4122
4123 -- Bind the ROWIDs for Deletion
4124 xProgress := 'POOB-50-1260';
4125 dbms_sql.bind_variable(project_del_c1,':col_rowid',rProject_rowid);
4126
4127 xProgress := 'POOB-50-1270';
4128 dbms_sql.bind_variable(project_del_c2,':col_rowid',rProject_X_rowid);
4129
4130 -- Execute the First Delete SQL
4131 xProgress := 'POOB-50-1280';
4132 dummy := dbms_sql.execute(project_del_c1);
4133
4134 -- Execute the Second Delete SQL
4135 xProgress := 'POOB-50-1290';
4136 dummy := dbms_sql.execute(project_del_c2);
4137
4138 END LOOP; -- Project Level
4139
4140 -- Make a note if zero rows were processed
4141 xProgress := 'POOB-50-1300';
4142 IF dbms_sql.last_row_count = 0 THEN
4143 xProgress := 'POOB-50-1310';
4144 v_LevelProcessed := 'PROJECT';
4145 ec_debug.pl(0,
4146 'EC',
4147 'ECE_NO_DB_ROW_PROCESSED',
4148 'PROGRESS_LEVEL',
4149 xProgress,
4150 'LEVEL_PROCESSED',
4151 v_LevelProcessed,
4152 'TRANSACTION_TYPE',
4153 cTransaction_Type);
4154 END IF;
4155
4156 end if; -- Bug 2490109
4157
4158 /* Bug 2490109
4159 -- Let's Close the Cursors
4160 xProgress := 'POOB-50-1320';
4161 dbms_sql.close_cursor(project_sel_c);
4162
4163 xProgress := 'POOB-50-1330';
4164 dbms_sql.close_cursor(project_del_c1);
4165
4166 xProgress := 'POOB-50-1340';
4167 dbms_sql.close_cursor(project_del_c2);
4168 */
4169
4170 xProgress := 'POOB-50-1350';
4171 if ec_debug.G_debug_level >= 2 then
4172 ec_debug.pop('ECE_POO_TRANSACTION.PUT_PROJECT_DATA_TO_OUTPUT_TBL');
4173 end if;
4174
4175 EXCEPTION
4176 WHEN OTHERS THEN
4177 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
4178 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
4179
4180 app_exception.raise_exception;
4181
4182 END PUT_DISTDATA_TO_OUT_TBL;
4183
4184 PROCEDURE populate_text_attachment(
4185 cCommunication_Method IN VARCHAR2,
4186 cTransaction_Type IN VARCHAR2,
4187 iRun_id IN INTEGER,
4188 cHeader_Output_Level IN NUMBER,
4189 cDetail_Output_Level IN NUMBER,
4190 cAtt_Header_Interface IN VARCHAR2,
4191 cAtt_Detail_Interface IN VARCHAR2,
4192 cEntity_Name IN VARCHAR2,
4193 cName IN VARCHAR2,
4194 cPK1_Value IN VARCHAR2,
4195 cPK2_Value IN VARCHAR2,
4196 cPK3_Value IN VARCHAR2,
4197 cPK4_Value IN VARCHAR2,
4198 cPK5_Value IN VARCHAR2,
4199 cSegment_Size IN NUMBER,
4200 l_key_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
4201 cFile_Common_Key IN VARCHAR2,
4202 l_att_header_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
4203 l_att_detail_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
4204 l_key_count IN OUT NOCOPY NUMBER) IS
4205
4206 xProgress VARCHAR2(80);
4207
4208 --l_att_header_tbl ece_flatfile_pvt.Interface_tbl_type;
4209
4210 v_att_header_select VARCHAR2(32000);
4211 v_att_header_from VARCHAR2(32000);
4212 v_att_header_where VARCHAR2(32000);
4213 v_att_view_name VARCHAR2(120) := 'ECE_ATTACHMENT_V';
4214
4215 n_att_header_sel_c INTEGER;
4216 n_dummy INTEGER;
4217 n_header_count NUMBER := 0;
4218 n_header_fkey NUMBER;
4219
4220 n_datatype_id_pos NUMBER;
4221 n_att_seq_num_pos NUMBER;
4222 n_att_doc_id_pos NUMBER; --Bug 2187958
4223 n_att_hdr_count NUMBER;
4224 BEGIN
4225 if ec_debug.G_debug_level >= 2 then
4226 ec_debug.push('ECE_POO_TRANSACTION.POPULATE_TEXT_ATTACHMENT');
4227 ec_debug.pl(3,'cEntity_Name: ', cEntity_Name);
4228 ec_debug.pl(3,'cName: ', cName);
4229 ec_debug.pl(3,'cPK1_Value: ', cPK1_Value);
4230 ec_debug.pl(3,'cPK2_Value: ', cPK2_Value);
4231 ec_debug.pl(3,'cPK3_Value: ', cPK3_Value);
4232 ec_debug.pl(3,'cPK4_Value: ', cPK4_Value);
4233 ec_debug.pl(3,'cPK5_Value: ', cPK5_Value);
4234 ec_debug.pl(3,'cSegment_Size: ',cSegment_Size);
4235 end if;
4236
4237 xProgress := 'POOB-60-1000';
4238 if ( l_att_header_tbl.count = 0) then
4239 l_key_count := l_key_tbl.count;
4240 ece_flatfile_pvt.init_table(cTransaction_Type,
4241 cAtt_Header_Interface,
4242 cHeader_Output_Level,
4243 TRUE,
4244 l_att_header_tbl,
4245 l_key_tbl);
4246 end if;
4247
4248 if ec_debug.G_debug_level >= 3 then
4249 ec_debug.pl(3,'l_key_count: ', l_key_count);
4250 end if;
4251
4252 xProgress := 'POOB-60-1005';
4253 -- Build the SELECT Clause.
4254 ece_extract_utils_pub.select_clause(cTransaction_Type,
4255 cCommunication_Method,
4256 cAtt_Header_Interface,
4257 l_att_header_tbl,
4258 v_att_header_select,
4259 v_att_header_from,
4260 v_att_header_where);
4261
4262 xProgress := 'POOB-60-1010';
4263 -- Build the WHERE and the ORDER BY Clause.
4264 -- Entity Name must not be NULL.
4265 v_att_header_where := v_att_header_where ||
4266 v_att_view_name || '.entity_name = :cEntity_Name';
4267
4268 xProgress := 'POOB-60-1020';
4269 -- Name must not be NULL.
4270 v_att_header_where := v_att_header_where || ' AND UPPER(' ||
4271 v_att_view_name || '.category_name) = UPPER(:cName)';
4272
4273 xProgress := 'POOB-60-1030';
4274 -- cPK1 Value must not be NULL.
4275 v_att_header_where := v_att_header_where || ' AND ' ||
4276 v_att_view_name || '.pk1_value = :cPK1_Value';
4277
4278 xProgress := 'POOB-60-1040';
4279 /* IF cPK2_Value IS NOT NULL THEN
4280 v_att_header_where := v_att_header_where || ' AND ' ||
4281 v_att_view_name || '.pk2_value = :cPK2_Value';
4282 ELSE -- cPK2_Value IS NULL.
4283 v_att_header_where := v_att_header_where || ' AND ' ||
4284 v_att_view_name || '.pk2_value IS NULL';
4285 END IF; */
4286 -- BUG:5367903
4287
4288 IF cPK2_Value IS NULL THEN
4289 v_att_header_where := v_att_header_where || ' AND ' ||
4290 v_att_view_name || '.pk2_value IS NULL';
4291 ELSE
4292 IF cPK2_Value <> C_ANY_VALUE THEN
4293 v_att_header_where := v_att_header_where || ' AND ' ||
4294 v_att_view_name || '.pk2_value = :cPK2_Value';
4295 END IF;
4296 END IF;
4297
4298
4299 xProgress := 'POOB-60-1050';
4300 IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4301 /* IF cPK3_Value IS NOT NULL THEN
4302 v_att_header_where := v_att_header_where || ' AND ' ||
4303 v_att_view_name || '.pk3_value = :cPK3_Value';
4304 ELSE -- cPK3_Value IS NULL.
4305 v_att_header_where := v_att_header_where || ' AND ' ||
4306 v_att_view_name || '.pk3_value IS NULL';
4307 END IF;*/
4308 -- BUG:5367903
4309
4310 IF cPK3_Value IS NULL THEN
4311 v_att_header_where := v_att_header_where || ' AND ' ||
4312 v_att_view_name || '.pk3_value IS NULL';
4313 ELSE
4314 IF cPK3_Value <> C_ANY_VALUE THEN
4315 v_att_header_where := v_att_header_where || ' AND ' ||
4316 v_att_view_name || '.pk3_value = :cPK3_Value';
4317 END IF;
4318 END IF;
4319
4320 xProgress := 'POOB-60-1060';
4321 /* IF cPK4_Value IS NOT NULL THEN
4322 v_att_header_where := v_att_header_where || ' AND ' ||
4323 v_att_view_name || '.pk4_value = :cPK4_Value';
4324 ELSE -- cPK4_Value IS NULL.
4325 v_att_header_where := v_att_header_where || ' AND ' ||
4326 v_att_view_name || '.pk4_value IS NULL';
4327 END IF;*/
4328 -- BUG:5367903
4329
4330 IF cPK4_Value IS NULL THEN
4331 v_att_header_where := v_att_header_where || ' AND ' ||
4332 v_att_view_name || '.pk4_value IS NULL';
4333 ELSE
4334 IF cPK4_Value <> C_ANY_VALUE THEN
4335 v_att_header_where := v_att_header_where || ' AND ' ||
4336 v_att_view_name || '.pk4_value = :cPK4_Value';
4337 END IF;
4338 END IF;
4339
4340 xProgress := 'POOB-60-1070';
4341 /* IF cPK5_Value IS NOT NULL THEN
4342 v_att_header_where := v_att_header_where || ' AND ' ||
4343 v_att_view_name || '.pk5_value = :cPK5_Value';
4344 ELSE -- cPK5_Value IS NULL.
4345 v_att_header_where := v_att_header_where || ' AND ' ||
4346 v_att_view_name || '.pk5_value IS NULL';
4347 END IF;*/
4348 -- BUG:5367903
4349
4350 IF cPK5_Value IS NULL THEN
4351 v_att_header_where := v_att_header_where || ' AND ' ||
4352 v_att_view_name || '.pk5_value IS NULL';
4353 ELSE
4354 IF cPK5_Value <> C_ANY_VALUE THEN
4355 v_att_header_where := v_att_header_where || ' AND ' ||
4356 v_att_view_name || '.pk5_value = :cPK5_Value';
4357 END IF;
4358 END IF;
4359
4360 END IF;
4361
4362 xProgress := 'POOB-60-1080';
4363 /*v_att_header_where := v_att_header_where || ' ORDER BY ' ||
4364 v_att_view_name || '.att_seq_num';*/
4365
4366 --Bug 2187958
4367 v_att_header_where := v_att_header_where || ' ORDER BY ' ||
4368 v_att_view_name || '.attached_document_id';
4369
4370 xProgress := 'POOB-60-1090';
4371 -- Now we put all the clauses together.
4372 v_att_header_select := v_att_header_select || v_att_header_from || v_att_header_where;
4373
4374 if ec_debug.G_debug_level = 3 then
4375 ec_debug.pl(3,'v_att_header_select: ',v_att_header_select);
4376 end if;
4377
4378 xProgress := 'POOB-60-1092';
4379 ece_extract_utils_pub.find_pos(l_att_header_tbl,'DATATYPE_ID',n_datatype_id_pos);
4380
4381 xProgress := 'POOB-60-1094';
4382 ece_extract_utils_pub.find_pos(l_att_header_tbl,'ATT_SEQ_NUM',n_att_seq_num_pos);
4383 if ec_debug.G_debug_level = 3 then
4384 ec_debug.pl(3,'n_datatype_id_pos: ',n_datatype_id_pos);
4385 ec_debug.pl(3,'n_att_seq_num_pos: ',n_att_seq_num_pos);
4386 end if;
4387
4388 xProgress := 'POOB-60-1096';
4389 ece_extract_utils_pub.find_pos(l_att_header_tbl,'ATTACHED_DOCUMENT_ID',n_att_doc_id_pos); --Bug 2187958
4390 if ec_debug.G_debug_level = 3 then
4391 ec_debug.pl(3,'n_att_doc_id_pos: ',n_att_doc_id_pos);
4392 end if;
4393
4394 -- Open Cursor.
4395 xProgress := 'POOB-60-1100';
4396 n_att_header_sel_c := dbms_sql.open_cursor;
4397
4398 -- Parse Cursor.
4399 xProgress := 'POOB-60-1110';
4400 BEGIN
4401 dbms_sql.parse(n_att_header_sel_c,v_att_header_select,dbms_sql.native);
4402
4403 EXCEPTION
4404 WHEN OTHERS THEN
4405 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
4406 v_att_header_select);
4407 app_exception.raise_exception;
4408 END;
4409
4410 -- Set Counter
4411 xProgress := 'POOB-60-1120';
4412 n_header_count := l_att_header_tbl.COUNT;
4413 if ec_debug.G_debug_level = 3 then
4414 ec_debug.pl(3,'n_header_count: ',n_header_count);
4415 end if;
4416
4417 xProgress := 'POOB-60-1130';
4418 ece_flatfile_pvt.define_interface_column_type(n_att_header_sel_c,
4419 v_att_header_select,
4420 ece_extract_utils_pub.G_MAXCOLWIDTH,
4421 l_att_header_tbl);
4422
4423 -- Bind Variables
4424 xProgress := 'POOB-60-1132';
4425 dbms_sql.bind_variable(n_att_header_sel_c,':cEntity_Name',cEntity_Name);
4426
4427 xProgress := 'POOB-60-1133';
4428 dbms_sql.bind_variable(n_att_header_sel_c,':cName',cName);
4429
4430 xProgress := 'POOB-60-1134';
4431 dbms_sql.bind_variable(n_att_header_sel_c,':cPK1_Value',cPK1_Value);
4432
4433 IF cPK2_Value IS NOT NULL and cPK2_Value <> C_ANY_VALUE THEN
4434 xProgress := 'POOB-60-1135';
4435 dbms_sql.bind_variable(n_att_header_sel_c,':cPK2_Value',cPK2_Value);
4436 END IF;
4437
4438 IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4439 /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4440 IF cPK3_Value IS NOT NULL AND cPK3_Value <> C_ANY_VALUE THEN
4441 xProgress := 'POOB-60-1136';
4442 dbms_sql.bind_variable(n_att_header_sel_c,':cPK3_Value',cPK3_Value);
4443 END IF;
4444
4445 /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4446 IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4447 xProgress := 'POOB-60-1137';
4448 dbms_sql.bind_variable(n_att_header_sel_c,':cPK4_Value',cPK4_Value);
4449 END IF;
4450
4451 /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4452 IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4453 xProgress := 'POOB-60-1138';
4454 dbms_sql.bind_variable(n_att_header_sel_c,':cPK5_Value',cPK5_Value);
4455 END IF;
4456 END IF;
4457
4458 -- Execute Cursor
4459 xProgress := 'POOB-60-1140';
4460 n_dummy := dbms_sql.execute(n_att_header_sel_c);
4461
4462 xProgress := 'POOB-60-1150';
4463 WHILE dbms_sql.fetch_rows(n_att_header_sel_c) > 0 LOOP
4464 xProgress := 'POOB-60-1160';
4465 ece_flatfile_pvt.assign_column_value_to_tbl(n_att_header_sel_c,l_key_count,l_att_header_tbl,l_key_tbl);
4466
4467 xProgress := 'POOB-60-1170';
4468 /* BEGIN
4469 SELECT ece_attachment_headers_s.NEXTVAL INTO n_header_fkey
4470 FROM DUAL;
4471
4472 EXCEPTION
4473 WHEN NO_DATA_FOUND THEN
4474 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_ATTACHMENT_HEADERS_S');
4475 END; */
4476
4477 xProgress := 'POOB-60-1180';
4478 /* ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,
4479 cTransaction_Type,
4480 cCommunication_Method,
4481 cAtt_Header_Interface,
4482 l_att_header_tbl,
4483 n_header_fkey); */
4484
4485 -- 2823215
4486 ece_poo_transaction.write_to_file( cTransaction_Type,
4487 cCommunication_Method,
4488 cAtt_Header_Interface,
4489 l_att_header_tbl,
4490 iOutput_width,
4491 iRun_id,
4492 cFile_Common_Key,
4493 n_header_fkey);
4494 -- 2823215
4495 xProgress := 'POOB-60-1190';
4496 -- populate_ext_att_header(n_header_fkey,l_att_header_tbl);
4497
4498 xProgress := 'POOB-60-1200';
4499 populate_text_att_detail(
4500 cCommunication_Method,
4501 cTransaction_Type,
4502 iRun_id,
4503 cDetail_Output_Level,
4504 cAtt_Detail_Interface,
4505 l_att_header_tbl(n_att_seq_num_pos).value,
4506 cEntity_Name,
4507 cName,
4508 cPK1_Value,
4509 cPK2_Value,
4510 cPK3_Value,
4511 cPK4_Value,
4512 cPK5_Value,
4513 l_att_header_tbl(n_datatype_id_pos).value,
4514 cSegment_Size,
4515 l_key_tbl,
4516 l_att_header_tbl(n_att_doc_id_pos).value, -- Bug 2187958
4517 cFile_Common_Key,
4518 l_att_detail_tbl);
4519
4520 END LOOP;
4521
4522 xProgress := 'POOB-60-1210';
4523 dbms_sql.close_cursor(n_att_header_sel_c);
4524 if ec_debug.G_debug_level >= 2 then
4525 ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_TEXT_ATTACHMENT');
4526 end if;
4527
4528 EXCEPTION
4529 WHEN OTHERS THEN
4530 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
4531 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
4532
4533 app_exception.raise_exception;
4534 END populate_text_attachment;
4535
4536 PROCEDURE populate_text_att_detail(
4537 cCommunication_Method IN VARCHAR2,
4538 cTransaction_Type IN VARCHAR2,
4539 iRun_id IN INTEGER,
4540 cDetail_Output_Level IN NUMBER,
4541 cAtt_Detail_Interface IN VARCHAR2,
4542 cAtt_Seq_Num IN NUMBER,
4543 cEntity_Name IN VARCHAR2,
4544 cName IN VARCHAR2,
4545 cPK1_Value IN VARCHAR2,
4546 cPK2_Value IN VARCHAR2,
4547 cPK3_Value IN VARCHAR2,
4548 cPK4_Value IN VARCHAR2,
4549 cPK5_Value IN VARCHAR2,
4550 cData_Type_ID IN NUMBER, -- 1=Short, 2=Long
4551 cSegment_Size IN NUMBER,
4552 l_key_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
4553 cAtt_doc_id IN NUMBER,
4554 cFile_Common_Key IN VARCHAR2,
4555 l_att_detail_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type) IS
4556
4557 xProgress VARCHAR2(80);
4558
4559 c_local_chr_10 VARCHAR2(1) := fnd_global.local_chr(10);
4560 c_local_chr_13 VARCHAR2(1) := fnd_global.local_chr(13);
4561
4562 --l_att_detail_tbl ece_flatfile_pvt.Interface_tbl_type;
4563 l_short_text VARCHAR2(32000);
4564 l_data_chunk VARCHAR2(32000);
4565 l_temp_text VARCHAR2(32000);
4566
4567 v_att_detail_select VARCHAR2(32000) := 'SELECT ';
4568 v_att_detail_from VARCHAR2(32000) := ' FROM ';
4569 v_att_detail_where VARCHAR2(32000) := ' WHERE ';
4570 v_att_view_name VARCHAR2(120) := 'ECE_ATTACHMENT_V';
4571 v_continue_flag VARCHAR2(1);
4572 v_last_char VARCHAR2(32000);
4573 v_split_word VARCHAR2(120);
4574
4575 n_att_detail_sel_c INTEGER;
4576 n_dummy INTEGER;
4577
4578 n_cr_pos NUMBER;
4579 n_lf_pos NUMBER;
4580 n_detail_count NUMBER := 0;
4581 n_detail_fkey NUMBER;
4582 n_new_chunk_size NUMBER;
4583 n_return_size NUMBER;
4584 n_temp_return_size NUMBER;
4585 n_segment_number NUMBER;
4586 n_short_text_length NUMBER; -- Bug 3310412
4587 n_cur_pos NUMBER;
4588 n_space_pos NUMBER;
4589
4590 n_att_seq_num_pos NUMBER;
4591 n_att_doc_id_pos NUMBER; --Bug 2187958
4592 n_entity_name_pos NUMBER;
4593 n_name_pos NUMBER;
4594 n_pk1_value_pos NUMBER;
4595 n_pk2_value_pos NUMBER;
4596 n_pk3_value_pos NUMBER;
4597 n_pk4_value_pos NUMBER;
4598 n_pk5_value_pos NUMBER;
4599 n_seg_num_pos NUMBER;
4600 n_cont_flag_pos NUMBER;
4601 n_att_seg_pos NUMBER;
4602 n_run_id_pos NUMBER;
4603 n_transaction_record_id_pos NUMBER;
4604 n_pr_pos NUMBER; -- 3618073
4605
4606 BEGIN
4607 if ec_debug.G_debug_level = 3 then
4608 ec_debug.push('ECE_POO_TRANSACTION.POPULATE_TEXT_ATT_DETAIL');
4609 ec_debug.pl(3,'cDetail_Output_Level: ' ,cDetail_Output_Level);
4610 ec_debug.pl(3,'cAtt_Detail_Interface: ',cAtt_Detail_Interface);
4611 ec_debug.pl(3,'cAtt_Seq_Num: ' ,cAtt_Seq_Num);
4612 ec_debug.pl(3,'cEntity_Name: ' ,cEntity_Name);
4613 ec_debug.pl(3,'cName: ' ,cName);
4614 ec_debug.pl(3,'cPK1_Value: ' ,cPK1_Value);
4615 ec_debug.pl(3,'cPK2_Value: ' ,cPK2_Value);
4616 ec_debug.pl(3,'cPK3_Value: ' ,cPK3_Value);
4617 ec_debug.pl(3,'cPK4_Value: ' ,cPK4_Value);
4618 ec_debug.pl(3,'cPK5_Value: ' ,cPK5_Value);
4619 ec_debug.pl(3,'cData_Type_ID: ' ,cData_Type_ID);
4620 ec_debug.pl(3,'cSegment_Size: ' ,cSegment_Size);
4621 ec_debug.pl(3,'cAtt_doc_id: ' ,cAtt_doc_id);
4622 end if;
4623
4624 fnd_profile.get('ECE_ATT_SPLIT_WORD_ALLOWED',v_split_word);
4625 v_split_word := NVL(v_split_word,'Y');
4626 ec_debug.pl(3,'v_split_word: ',v_split_word);
4627
4628 xProgress := 'POOB-70-1000';
4629 if (l_att_detail_tbl.count = 0) then
4630 ece_flatfile_pvt.init_table(cTransaction_Type,cAtt_Detail_Interface,cDetail_Output_Level,TRUE,l_att_detail_tbl,l_key_tbl);
4631
4632 end if;
4633
4634 xProgress := 'POOB-70-1010';
4635 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'ATT_SEQ_NUM', n_att_seq_num_pos);
4636 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'ENTITY_NAME', n_entity_name_pos);
4637 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'NAME', n_name_pos);
4638 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'PK1_VALUE', n_pk1_value_pos);
4639 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'PK2_VALUE', n_pk2_value_pos);
4640 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'PK3_VALUE', n_pk3_value_pos);
4641 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'PK4_VALUE', n_pk4_value_pos);
4642 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'PK5_VALUE', n_pk5_value_pos);
4643 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'SEGMENT_NUMBER', n_seg_num_pos);
4644 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'CONTINUE_FLAG' , n_cont_flag_pos);
4645 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'ATTACHMENT_SEGMENT', n_att_seg_pos);
4646 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'RUN_ID', n_run_id_pos);
4647 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'TRANSACTION_RECORD_ID',n_transaction_record_id_pos);
4648 ece_flatfile_pvt.find_pos(l_att_detail_tbl,'ATTACHED_DOCUMENT_ID', n_att_doc_id_pos); --Bug2187958
4649 xProgress := 'POOB-70-1020';
4650 -- Build the SELECT Clause.
4651 ece_extract_utils_pub.select_clause(cTransaction_Type,
4652 cCommunication_Method,
4653 cAtt_Detail_Interface ,
4654 l_att_detail_tbl,
4655 v_att_detail_select,
4656 v_att_detail_from,
4657 v_att_detail_where);
4658
4659 IF cData_Type_ID = 1 THEN -- Short Text
4660 v_att_detail_select := v_att_detail_select || ',short_text';
4661 ELSE
4662 v_att_detail_select := v_att_detail_select || ',long_text';
4663 END IF;
4664
4665 xProgress := 'POOB-70-1030';
4666 -- Build the WHERE and the ORDER BY Clause.
4667 -- Entity Name must not be NULL.
4668 v_att_detail_where := v_att_detail_where ||
4669 v_att_view_name || '.entity_name = :cEntity_name';
4670
4671 xProgress := 'POOB-70-1040';
4672 -- Name must not be NULL.
4673 v_att_detail_where := v_att_detail_where || ' AND UPPER(' ||
4674 v_att_view_name || '.category_name) = UPPER(:cName)';
4675
4676 xProgress := 'POOB-70-1045';
4677 -- Attachment Sequence must not be NULL.
4678 v_att_detail_where := v_att_detail_where || ' AND ' ||
4679 v_att_view_name || '.att_seq_num = :cAtt_Seq_Num';
4680
4681 --Attached document ID must not be null Bug 2187958
4682 v_att_detail_where := v_att_detail_where || ' AND ' ||
4683 v_att_view_name || '.attached_document_id = :cAtt_doc_id';
4684
4685 xProgress := 'POOB-70-1050';
4686 -- PK1 Value must not be NULL.
4687 v_att_detail_where := v_att_detail_where || ' AND ' ||
4688 v_att_view_name || '.pk1_value = :cPK1_Value';
4689
4690 xProgress := 'POOB-70-1060';
4691 /* IF cPK2_Value IS NOT NULL THEN
4692 xProgress := 'POOB-70-1070';
4693 v_att_detail_where := v_att_detail_where || ' AND ' ||
4694 v_att_view_name || '.pk2_value = :cPK2_Value';
4695 ELSE -- cPK2_Value IS NULL.
4696 xProgress := 'POOB-70-1080';
4697 v_att_detail_where := v_att_detail_where || ' AND ' ||
4698 v_att_view_name || '.pk2_value IS NULL';
4699 END IF;*/
4700 -- BUG:5367903
4701 IF cPK2_Value IS NULL THEN
4702 xProgress := 'POOB-70-1070';
4703 v_att_detail_where := v_att_detail_where || ' AND ' ||
4704 v_att_view_name || '.pk2_value IS NULL';
4705 ELSE -- cPK2_Value IS NOT NULL.
4706 xProgress := 'POOB-70-1080';
4707 IF CPK2_Value <> C_ANY_VALUE THEN
4708 v_att_detail_where := v_att_detail_where || ' AND ' ||
4709 v_att_view_name || '.pk2_value = :cPK2_Value';
4710 END IF;
4711 END IF;
4712
4713 xProgress := 'POOB-70-1090';
4714 IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4715 /* IF cPK3_Value IS NOT NULL THEN
4716 xProgress := 'POOB-70-1100';
4717 v_att_detail_where := v_att_detail_where || ' AND ' ||
4718 v_att_view_name || '.pk3_value = :cPK3_Value';
4719 ELSE -- cPK3_Value IS NULL.
4720 xProgress := 'POOB-70-1110';
4721 v_att_detail_where := v_att_detail_where || ' AND ' ||
4722 v_att_view_name || '.pk3_value IS NULL';
4723 END IF; */
4724 -- BUG:5367903
4725
4726 IF cPK3_Value IS NULL THEN
4727 xProgress := 'POOB-70-1100';
4728 v_att_detail_where := v_att_detail_where || ' AND ' ||
4729 v_att_view_name || '.pk3_value IS NULL';
4730 ELSE -- cPK3_Value IS NOT NULL.
4731 xProgress := 'POOB-70-1110';
4732 IF CPK3_Value <> C_ANY_VALUE THEN
4733 v_att_detail_where := v_att_detail_where || ' AND ' ||
4734 v_att_view_name || '.pk3_value = :cPK3_Value';
4735 END IF;
4736 END IF;
4737
4738 xProgress := 'POOB-70-1120';
4739 /*IF cPK4_Value IS NOT NULL THEN
4740 xProgress := 'POOB-70-1130';
4741 v_att_detail_where := v_att_detail_where || ' AND ' ||
4742 v_att_view_name || '.pk4_value = :cPK4_Value';
4743 ELSE -- cPK4_Value IS NULL.
4744 xProgress := 'POOB-70-1140';
4745 v_att_detail_where := v_att_detail_where || ' AND ' ||
4746 v_att_view_name || '.pk4_value IS NULL';
4747 END IF;*/
4748 -- BUG:5367903
4749 IF cPK4_Value IS NULL THEN
4750 xProgress := 'POOB-70-1130';
4751 v_att_detail_where := v_att_detail_where || ' AND ' ||
4752 v_att_view_name || '.pk4_value IS NULL';
4753 ELSE -- cPK4_Value IS NOT NULL.
4754 xProgress := 'POOB-70-1140';
4755 IF CPK4_Value <> C_ANY_VALUE THEN
4756 v_att_detail_where := v_att_detail_where || ' AND ' ||
4757 v_att_view_name || '.pk4_value = :cPK4_Value';
4758 END IF;
4759 END IF;
4760
4761 xProgress := 'POOB-70-1150';
4762 /* IF cPK5_Value IS NOT NULL THEN
4763 xProgress := 'POOB-70-1160';
4764 v_att_detail_where := v_att_detail_where || ' AND ' ||
4765 v_att_view_name || '.pk5_value = :cPK5_Value';
4766 ELSE -- cPK5_Value IS NULL.
4767 xProgress := 'POOB-70-1170';
4768 v_att_detail_where := v_att_detail_where || ' AND ' ||
4769 v_att_view_name || '.pk5_value IS NULL';
4770 END IF; */
4771 -- BUG:5367903
4772 IF cPK5_Value IS NULL THEN
4773 xProgress := 'POOB-70-1160';
4774 v_att_detail_where := v_att_detail_where || ' AND ' ||
4775 v_att_view_name || '.pk5_value IS NULL';
4776 ELSE -- cPK5_Value IS NOT NULL.
4777 xProgress := 'POOB-70-1170';
4778 IF CPK5_Value <> C_ANY_VALUE THEN
4779 v_att_detail_where := v_att_detail_where || ' AND ' ||
4780 v_att_view_name || '.pk5_value = :cPK5_Value';
4781 END IF;
4782 END IF;
4783
4784 END IF;
4785
4786 xProgress := 'POOB-70-1180';
4787 v_att_detail_where := v_att_detail_where || ' AND ' ||
4788 v_att_view_name || '.usage_type <> ''T''';
4789 --Bug 2187958
4790 xProgress := 'POOB-70-1190';
4791 /* v_att_detail_where := v_att_detail_where || ' ORDER BY ' ||
4792 v_att_view_name || '.att_seq_num';*/
4793
4794 v_att_detail_where := v_att_detail_where || ' ORDER BY ' ||
4795 v_att_view_name || '.attached_document_id';
4796
4797 -- Now we put all the clauses together.
4798 xProgress := 'POOB-70-1200';
4799 v_att_detail_select := v_att_detail_select || v_att_detail_from || v_att_detail_where;
4800 if ec_debug.G_debug_level = 3 then
4801 ec_debug.pl(3,'v_att_detail_select: ',v_att_detail_select);
4802 end if;
4803
4804 -- Open Cursor.
4805 xProgress := 'POOB-70-1210';
4806 n_att_detail_sel_c := dbms_sql.open_cursor;
4807
4808 -- Parse Cursor.
4809 xProgress := 'POOB-70-1220';
4810 BEGIN
4811 xProgress := 'POOB-70-1230';
4812 dbms_sql.parse(n_att_detail_sel_c,v_att_detail_select,dbms_sql.native);
4813
4814 EXCEPTION
4815 WHEN OTHERS THEN
4816 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,v_att_detail_select);
4817 app_exception.raise_exception;
4818
4819 END;
4820
4821 xProgress := 'POOB-70-1240';
4822 dbms_sql.bind_variable(n_att_detail_sel_c,':cEntity_name',cEntity_name);
4823
4824 xProgress := 'POOB-70-1250';
4825 dbms_sql.bind_variable(n_att_detail_sel_c,':cName',cName);
4826
4827 xProgress := 'POOB-70-1255';
4828 dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
4829
4830 xProgress := 'POOB-70-1258';
4831 dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_doc_id',cAtt_doc_id);
4832
4833 xProgress := 'POOB-70-1260';
4834 dbms_sql.bind_variable(n_att_detail_sel_c,':cPK1_Value',cPK1_Value);
4835
4836 xProgress := 'POOB-70-1270';
4837 /* IF cPK2_Value IS NOT NULL THEN */ -- BUG:5367903
4838 IF cPK2_Value IS NOT NULL AND cPK2_Value <> C_ANY_VALUE THEN
4839 dbms_sql.bind_variable(n_att_detail_sel_c,':cPK2_Value',cPK2_Value);
4840 END IF;
4841
4842 IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4843 xProgress := 'POOB-70-1280';
4844 /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4845 IF cPK3_Value IS NOT NULL AND cPK3_Value <> C_ANY_VALUE THEN
4846 dbms_sql.bind_variable(n_att_detail_sel_c,':cPK3_Value',cPK3_Value);
4847 END IF;
4848
4849 xProgress := 'POOB-70-1290';
4850 /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4851 IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4852 dbms_sql.bind_variable(n_att_detail_sel_c,':cPK4_Value',cPK4_Value);
4853 END IF;
4854
4855 xProgress := 'POOB-70-1300';
4856 /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4857 IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4858 dbms_sql.bind_variable(n_att_detail_sel_c,':cPK5_Value',cPK5_Value);
4859 END IF;
4860 END IF;
4861
4862 -- Set Counter
4863 xProgress := 'POOB-70-1310';
4864 n_detail_count := l_att_detail_tbl.COUNT;
4865 if ec_debug.G_debug_level = 3 then
4866 ec_debug.pl(3,'n_detail_count: ',n_detail_count);
4867 end if;
4868
4869 -- Define Columns
4870 xProgress := 'POOB-70-1320';
4871 ece_flatfile_pvt.define_interface_column_type(n_att_detail_sel_c,
4872 v_att_detail_select,
4873 ece_extract_utils_pub.G_MAXCOLWIDTH,
4874 l_att_detail_tbl);
4875
4876 IF cData_Type_ID = 1 THEN -- Short Text
4877 xProgress := 'POOB-70-1330';
4878 /* dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,ece_extract_utils_pub.G_MAXCOLWIDTH);*/
4879 /*Bug 2153310.
4880 Increased the size to 2000 since the short text attachment can have
4881 2000 characters */
4882 dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
4883 ELSE -- Long Text
4884 xProgress := 'POOB-70-1340';
4885 dbms_sql.define_column_long(n_att_detail_sel_c,n_detail_count + 1);
4886 END IF;
4887
4888 -- Execute Cursor
4889 xProgress := 'POOB-70-1350';
4890 n_dummy := dbms_sql.execute(n_att_detail_sel_c);
4891
4892 xProgress := 'POOB-70-1360';
4893 WHILE dbms_sql.fetch_rows(n_att_detail_sel_c) > 0 LOOP
4894 xProgress := 'POOB-70-1370';
4895 ec_debug.pl(3,'xProgress: ',xProgress);
4896
4897 ece_flatfile_pvt.assign_column_value_to_tbl(n_att_detail_sel_c,l_att_detail_tbl);
4898
4899 n_segment_number := 1;
4900 n_cur_pos := 1;
4901 n_new_chunk_size := 0;
4902
4903 xProgress := 'POOB-70-1380';
4904 if ec_debug.G_debug_level = 3 then
4905 ec_debug.pl(3,'xProgress: ',xProgress);
4906 end if;
4907
4908 IF cData_Type_ID = 1 THEN -- Short Text
4909 xProgress := 'POOB-70-1390';
4910 if ec_debug.G_debug_level = 3 then
4911 ec_debug.pl(3,'xProgress: ',xProgress);
4912 end if;
4913
4914 dbms_sql.column_value(n_att_detail_sel_c,n_detail_count + 1,l_short_text);
4915 -- l_short_text := LTRIM(RTRIM(l_short_text)); -- Not using this one because white spaces at front may be intentional
4916 l_short_text := RTRIM(l_short_text);
4917 END IF;
4918
4919 xProgress := 'POOB-70-1400';
4920 ec_debug.pl(3,'xProgress: ',xProgress);
4921 LOOP
4922 xProgress := 'POOB-70-1410';
4923 if ec_debug.G_debug_level = 3 then
4924 ec_debug.pl(3,'xProgress: ',xProgress);
4925 end if;
4926 n_pr_pos := 0; -- 3618073
4927 IF cData_Type_ID = 1 THEN -- Short Text
4928 xProgress := 'POOB-70-1420';
4929 if ec_debug.G_debug_level = 3 then
4930 ec_debug.pl(3,'xProgress: ',xProgress);
4931 end if;
4932
4933 l_short_text := SUBSTRB(l_short_text,n_cur_pos);
4934 n_short_text_length := LENGTH(l_short_text); -- bug 3310412
4935 -- l_data_chunk := SUBSTRB(l_short_text,n_cur_pos,cSegment_Size);
4936 l_data_chunk := SUBSTRB(l_short_text,1,cSegment_Size);
4937
4938 n_return_size := LENGTH(l_data_chunk);
4939
4940 if ec_debug.G_debug_level = 3 then
4941 ec_debug.pl(3,'n_cur_pos: ',n_cur_pos);
4942 ec_debug.pl(3,'n_return_size: ',n_return_size);
4943 ec_debug.pl(3,'l_short_text: ',l_short_text);
4944 ec_debug.pl(3,'l_data_chunk: ',l_data_chunk);
4945 end if;
4946 ELSIF cData_Type_ID = 2 THEN -- Long Text
4947 xProgress := 'POOB-70-1430';
4948 if ec_debug.G_debug_level = 3 then
4949 ec_debug.pl(3,'xProgress: ',xProgress);
4950 end if;
4951
4952 -- Cursor(I) ,Pos(I) ,Len(I) ,Offset(I) ,Value(O) ,Value Len(O)
4953 dbms_sql.column_value_long(n_att_detail_sel_c,n_detail_count + 1,cSegment_Size,n_cur_pos - 1,l_data_chunk,n_return_size);
4954 if ec_debug.G_debug_level = 3 then
4955 ec_debug.pl(3,'n_cur_pos: ',n_cur_pos);
4956 ec_debug.pl(3,'n_return_size: ',n_return_size);
4957 ec_debug.pl(3,'l_data_chunk: ',l_data_chunk);
4958 end if;
4959 END IF;
4960
4961 xProgress := 'POOB-70-1440';
4962 if ec_debug.G_debug_level = 3 then
4963 ec_debug.pl(3,'xProgress: ',xProgress);
4964 end if;
4965 EXIT WHEN (n_return_size = 0 AND cData_Type_ID = 2) OR (l_short_text IS NULL AND cData_Type_ID = 1);
4966
4967 xProgress := 'POOB-70-1450';
4968 if ec_debug.G_debug_level = 3 then
4969 ec_debug.pl(3,'xProgress: ',xProgress);
4970 end if;
4971
4972 v_continue_flag := 'Y';
4973 n_cr_pos := INSTR(l_data_chunk,c_local_chr_13);
4974 n_lf_pos := INSTR(l_data_chunk,c_local_chr_10);
4975
4976 if ec_debug.G_debug_level = 3 then
4977 ec_debug.pl(3,'n_cr_pos: ',n_cr_pos);
4978 ec_debug.pl(3,'n_lf_pos: ',n_lf_pos);
4979 end if;
4980
4981 IF n_cr_pos = 0 AND n_lf_pos = 0 THEN -- There are no CR or CRLF in the chunk...
4982 xProgress := 'POOB-70-1460';
4983 if ec_debug.G_debug_level = 3 then
4984 ec_debug.pl(3,'xProgress: ',xProgress);
4985 end if;
4986
4987 IF v_split_word = 'N' THEN -- No I am not allowed to split a word.
4988 xProgress := 'POOB-70-1470';
4989 if ec_debug.G_debug_level = 3 then
4990 ec_debug.pl(3,'xProgress: ',xProgress);
4991 end if;
4992
4993 v_last_char := SUBSTRB(l_data_chunk,n_return_size,1);
4994
4995 IF v_last_char NOT IN ('.',',',';','-',' ') THEN
4996 xProgress := 'POOB-70-1480';
4997 if ec_debug.G_debug_level = 3 then
4998 ec_debug.pl(3,'xProgress: ',xProgress);
4999 end if;
5000
5001 n_space_pos := INSTR(l_data_chunk,' ',-1,1);
5002
5003 IF n_space_pos <> 0 THEN --Space Character Found
5004 xProgress := 'POOB-70-1490';
5005 -- bug 3310412
5006 /* if the return size is less than the allowed segment size*/
5007 /* return the whole text as word splitting is not possible */
5008 if (n_return_size < cSegment_Size) then
5009 n_new_chunk_size := n_return_size;
5010 else
5011 /* If remaining length of short text is equal to segment size */
5012 /* return the whole text without splitting */
5013 if (cData_Type_ID = 2 and (n_short_text_length = cSegment_size)) Then
5014 n_new_chunk_size := n_return_size;
5015 /* Else remaining length is greater than segment size */
5016 /* The last word is split */
5017 /* Print upto the last space character */
5018 else
5019 n_new_chunk_size := n_space_pos;
5020 end if;
5021 end if;
5022 -- bug 3310412
5023 l_data_chunk := SUBSTRB(l_data_chunk,1,n_new_chunk_size);
5024
5025 if ec_debug.G_debug_level = 3 then
5026 ec_debug.pl(3,'xProgress: ',xProgress);
5027 ec_debug.pl(3,'n_new_chunk_size: ',n_new_chunk_size);
5028 end if;
5029 ELSE --Space Chacter Not Found
5030 xProgress := 'POOB-70-1492';
5031 n_new_chunk_size := n_return_size;
5032 if ec_debug.G_debug_level = 3 then
5033 ec_debug.pl(3,'xProgress: ',xProgress);
5034 ec_debug.pl(3,'n_new_chunk_size: ',n_new_chunk_size);
5035 end if;
5036 END IF;
5037 ELSE -- Whew, last character is a breakable character...
5038 xProgress := 'POOB-70-1494';
5039
5040 n_new_chunk_size := n_return_size;
5041 if ec_debug.G_debug_level = 3 then
5042 ec_debug.pl(3,'xProgress: ',xProgress);
5043 ec_debug.pl(3,'n_new_chunk_size: ',n_new_chunk_size);
5044 end if;
5045 END IF;
5046 ELSE -- Yes I am allowed to split a word.
5047 xProgress := 'POOB-70-1496';
5048
5049 n_new_chunk_size := n_return_size;
5050 if ec_debug.G_debug_level = 3 then
5051 ec_debug.pl(3,'xProgress: ',xProgress);
5052 ec_debug.pl(3,'n_new_chunk_size: ',n_new_chunk_size);
5053 end if;
5054 END IF;
5055
5056 ELSIF n_cr_pos = 1 OR n_lf_pos = 1 THEN -- This is a blank line...
5057 xProgress := 'POOB-70-1500';
5058 if ec_debug.G_debug_level = 3 then
5059 ec_debug.pl(3,'xProgress: ',xProgress);
5060 end if;
5061
5062 IF n_cr_pos = 1 AND n_lf_pos = 2 THEN
5063 xProgress := 'POOB-70-1510';
5064 if ec_debug.G_debug_level = 3 then
5065 ec_debug.pl(3,'xProgress: ',xProgress);
5066 end if;
5067
5068 n_new_chunk_size := 2;
5069 n_pr_pos := 0; -- 3618073
5070 ELSE
5071 xProgress := 'POOB-70-1520';
5072 if ec_debug.G_debug_level = 3 then
5073 ec_debug.pl(3,'xProgress: ',xProgress);
5074 end if;
5075 n_new_chunk_size := 1;
5076 n_pr_pos := 0; -- 3618073
5077 END IF;
5078
5079 xProgress := 'POOB-70-1530';
5080 if ec_debug.G_debug_level = 3 then
5081 ec_debug.pl(3,'xProgress: ',xProgress);
5082 end if;
5083 l_data_chunk := '';
5084
5085 ELSIF n_cr_pos > 1 OR n_lf_pos > 1 THEN -- There is a CR or LF in the chunk...
5086 xProgress := 'POOB-70-1540';
5087 if ec_debug.G_debug_level = 3 then
5088 ec_debug.pl(3,'xProgress: ',xProgress);
5089 end if;
5090
5091 IF n_cr_pos > 1 THEN
5092 xProgress := 'POOB-70-1550';
5093 if ec_debug.G_debug_level = 3 then
5094 ec_debug.pl(3,'xProgress: ',xProgress);
5095 end if;
5096
5097 IF n_lf_pos = n_cr_pos + 1 THEN -- This is a CRLF combo...
5098 xProgress := 'POOB-70-1560';
5099 if ec_debug.G_debug_level = 3 then
5100 ec_debug.pl(3,'xProgress: ',xProgress);
5101 end if;
5102 n_new_chunk_size := n_lf_pos;
5103 n_pr_pos := 2; -- 3618073
5104 ELSE -- This is CR only...
5105 xProgress := 'POOB-70-1570';
5106 if ec_debug.G_debug_level = 3 then
5107 ec_debug.pl(3,'xProgress: ',xProgress);
5108 end if;
5109
5110 n_new_chunk_size := n_cr_pos;
5111 n_pr_pos := 1; --3618073
5112 END IF;
5113 ELSE -- This is LF only
5114 xProgress := 'POOB-70-1580';
5115 if ec_debug.G_debug_level = 3 then
5116 ec_debug.pl(3,'xProgress: ',xProgress);
5117 end if;
5118 n_new_chunk_size := n_lf_pos;
5119 n_pr_pos := 1;
5120 END IF;
5121
5122 xProgress := 'POOB-70-1590';
5123
5124 l_data_chunk := SUBSTRB(l_data_chunk,1,n_new_chunk_size-n_pr_pos); -- 3618073
5125
5126 if ec_debug.G_debug_level = 3 then
5127 ec_debug.pl(3,'xProgress: ',xProgress);
5128 ec_debug.pl(3,'n_new_chunk_size: ',n_new_chunk_size);
5129 ec_debug.pl(3,'l_data_chunk: ',l_data_chunk);
5130 end if;
5131 END IF;
5132
5133 xProgress := 'POOB-70-1600';
5134
5135 l_att_detail_tbl(n_att_seg_pos).value := l_data_chunk;
5136
5137 l_att_detail_tbl(n_seg_num_pos).value := n_segment_number;
5138
5139 if ec_debug.G_debug_level = 3 then
5140 ec_debug.pl(3,'xProgress: ',xProgress);
5141 ec_debug.pl(3,'l_data_chunk: ',l_data_chunk);
5142 ec_debug.pl(3,'n_new_chunk_size: ',n_new_chunk_size);
5143 ec_debug.pl(3,'n_segment_number: ',n_segment_number);
5144 ec_debug.pl(3,'n_cur_pos: ',n_cur_pos);
5145 end if;
5146
5147 xProgress := 'POOB-70-1610';
5148
5149 IF cData_Type_ID = 1 THEN -- Short Text
5150 n_cur_pos := n_new_chunk_size + 1;
5151 ELSIF cData_Type_ID = 2 THEN -- Long Text
5152 n_cur_pos := n_cur_pos + n_new_chunk_size;
5153 END IF;
5154
5155 n_segment_number := n_segment_number + 1;
5156 if ec_debug.G_debug_level = 3 then
5157 ec_debug.pl(3,'New n_cur_pos: ',n_cur_pos);
5158 ec_debug.pl(3,'n_segment_number: ',n_segment_number);
5159 end if;
5160
5161 BEGIN
5162 xProgress := 'POOB-70-1620';
5163 ec_debug.pl(3,'xProgress: ',xProgress);
5164
5165 SELECT ece_attachment_details_s.NEXTVAL INTO n_detail_fkey
5166 FROM DUAL;
5167 if ec_debug.G_debug_level = 3 then
5168 ec_debug.pl(3,'n_detail_fkey: ',n_detail_fkey);
5169 end if;
5170 EXCEPTION
5171 WHEN NO_DATA_FOUND THEN
5172 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_ATTACHMENT_DETAILS_S');
5173 END;
5174
5175 -- I have to execute the following few lines of code because, I have no way of knowing at this point
5176 -- whether to set the Continue Flag 'Y' or 'N' until I loop around to the top again. This is how I find
5177 -- out ahead of time what the answer will be.
5178 IF cData_Type_ID = 1 THEN -- Short Text
5179 xProgress := 'POOB-70-1622';
5180 if ec_debug.G_debug_level = 3 then
5181 ec_debug.pl(3,'xProgress: ',xProgress);
5182 end if;
5183 l_temp_text := SUBSTRB(l_short_text,n_cur_pos);
5184 ELSIF cData_Type_ID = 2 THEN -- Long Text
5185 xProgress := 'POOB-70-1623';
5186 if ec_debug.G_debug_level = 3 then
5187 ec_debug.pl(3,'xProgress: ',xProgress);
5188 end if;
5189
5190 dbms_sql.column_value_long(n_att_detail_sel_c,n_detail_count + 1,cSegment_Size,n_cur_pos - 1,l_temp_text,n_temp_return_size);
5191 END IF;
5192
5193 IF (n_temp_return_size = 0 AND cData_Type_ID = 2) OR (l_temp_text IS NULL AND cData_Type_ID = 1) THEN
5194 xProgress := 'POOB-70-1624';
5195 if ec_debug.G_debug_level = 3 then
5196 ec_debug.pl(3,'xProgress: ',xProgress);
5197 end if;
5198 v_continue_flag := 'N';
5199 END IF;
5200
5201 l_att_detail_tbl(n_cont_flag_pos).value := v_continue_flag;
5202 if ec_debug.G_debug_level = 3 then
5203 ec_debug.pl(3,'v_continue_flag: ',v_continue_flag);
5204 end if;
5205
5206 xProgress := 'POOB-70-1625';
5207 ec_debug.pl(3,'xProgress: ',xProgress);
5208 l_att_detail_tbl(n_att_seq_num_pos).value := cAtt_Seq_Num;
5209 l_att_detail_tbl(n_entity_name_pos).value := cEntity_name;
5210 l_att_detail_tbl(n_name_pos).value := cName;
5211 l_att_detail_tbl(n_pk1_value_pos).value := cPK1_Value;
5212 l_att_detail_tbl(n_pk2_value_pos).value := cPK2_Value;
5213 l_att_detail_tbl(n_pk3_value_pos).value := cPK3_Value;
5214 l_att_detail_tbl(n_pk4_value_pos).value := cPK4_Value;
5215 l_att_detail_tbl(n_pk5_value_pos).value := cPK5_Value;
5216 l_att_detail_tbl(n_run_id_pos).value := iRun_id;
5217 l_att_detail_tbl(n_att_doc_id_pos).value := cAtt_doc_id; --Bug 2187958
5218 xProgress := 'POOB-70-1630';
5219 ec_debug.pl(3,'xProgress: ',xProgress);
5220 /* ece_extract_utils_pub.insert_into_interface_tbl(
5221 iRun_id,
5222 cTransaction_Type,
5223 cCommunication_Method,
5224 cAtt_Detail_Interface,
5225 l_att_detail_tbl,
5226 n_detail_fkey); */
5227 -- 2823215
5228 ece_poo_transaction.write_to_file(cTransaction_Type,
5229 cCommunication_Method,
5230 cAtt_Detail_Interface,
5231 l_att_detail_tbl,
5232 iOutput_width,
5233 iRun_id,
5234 cFile_Common_Key,
5235 n_detail_fkey);
5236 -- 2823215
5237 END LOOP;
5238
5239 -- populate_ext_att_detail();
5240
5241 END LOOP;
5242
5243 xProgress := 'POOB-70-1640';
5244 dbms_sql.close_cursor(n_att_detail_sel_c);
5245
5246 if ec_debug.G_debug_level >= 2 then
5247 ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_TEXT_ATT_DETAIL');
5248 end if;
5249 EXCEPTION
5250 WHEN OTHERS THEN
5251 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
5252 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
5253
5254 app_exception.raise_exception;
5255
5256 END populate_text_att_detail;
5257
5258 PROCEDURE put_att_to_output_table(
5259 cCommunication_Method IN VARCHAR2,
5260 cTransaction_Type IN VARCHAR2,
5261 iOutput_Width IN INTEGER,
5262 iRun_id IN INTEGER,
5263 cHeader_Output_Level IN NUMBER,
5264 cDetail_Output_Level IN NUMBER,
5265 cHeader_Interface IN VARCHAR2,
5266 cDetail_Interface IN VARCHAR2,
5267 cEntity_Name IN VARCHAR2,
5268 cName IN VARCHAR2,
5269 cPK1_Value IN VARCHAR2,
5270 cPK2_Value IN VARCHAR2,
5271 cPK3_Value IN VARCHAR2,
5272 cPK4_Value IN VARCHAR2,
5273 cPK5_Value IN VARCHAR2,
5274 cFile_Common_Key IN VARCHAR2) IS
5275
5276 xProgress VARCHAR2(80);
5277
5278 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
5279
5280 c_header_common_key_name VARCHAR2(40);
5281
5282 nSeq_Num_pos NUMBER;
5283 nSeq_Num NUMBER;
5284 nDoc_ID_pos NUMBER;
5285 nDoc_ID NUMBER;
5286 nAtt_Doc_ID_pos NUMBER;
5287 nAtt_Doc_ID NUMBER;
5288 Header_sel_c INTEGER;
5289 Header_del_c1 INTEGER;
5290 Header_del_c2 INTEGER;
5291 cHeader_select VARCHAR2(32000);
5292 cHeader_from VARCHAR2(32000);
5293 cHeader_where VARCHAR2(32000);
5294 cHeader_delete1 VARCHAR2(32000);
5295 cHeader_delete2 VARCHAR2(32000);
5296
5297 cHeader_X_Interface VARCHAR2(50);
5298
5299 iHeader_count NUMBER;
5300 dummy INTEGER;
5301 rHeader_rowid ROWID;
5302 rHeader_X_rowid ROWID;
5303 get_no_rows INTEGER;
5304 get_no_lrows INTEGER;
5305 BEGIN
5306 if ec_debug.G_debug_level >= 2 then
5307 ec_debug.push('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5308 ec_debug.pl(3,'cEntity_Name: ',cEntity_Name);
5309 ec_debug.pl(3,'cName: ', cName);
5310 ec_debug.pl(3,'cPK1_Value: ', cPK1_Value);
5311 ec_debug.pl(3,'cPK2_Value: ', cPK2_Value);
5312 ec_debug.pl(3,'cPK3_Value: ', cPK3_Value);
5313 ec_debug.pl(3,'cPK4_Value: ', cPK4_Value);
5314 ec_debug.pl(3,'cPK5_Value: ', cPK5_Value);
5315 end if;
5316
5317 xProgress := 'POOB-80-1000';
5318 ece_flatfile_pvt.select_clause(cTransaction_Type,
5319 cCommunication_Method,
5320 cHeader_Interface,
5321 cHeader_X_Interface,
5322 l_header_tbl,
5323 c_header_common_key_name,
5324 cHeader_select,
5325 cHeader_from,
5326 cHeader_where,
5327 cHeader_Output_Level);
5328
5329 -- Build the WHERE and the ORDER BY Clause.
5330 cHeader_where := cHeader_where || ' AND ' ||
5331 cHeader_Interface || '.run_id = :Run_id';
5332
5333 -- Entity Name must not be NULL.
5334 cHeader_where := cHeader_where || ' AND ' ||
5335 cHeader_Interface || '.entity_name = :Entity_Name';
5336
5337 -- Name must not be NULL.
5338 cHeader_where := cHeader_where || ' AND ' || 'UPPER(' ||
5339 cHeader_Interface || '.name) = UPPER(:Name)';
5340
5341 -- PK1 Value must not be NULL.
5342 cHeader_where := cHeader_where || ' AND ' ||
5343 cHeader_Interface || '.pk1_value = :PK1_Value';
5344
5345 IF cPK2_Value IS NOT NULL THEN
5346 xProgress := 'POOB-80-1010';
5347 cHeader_where := cHeader_where || ' AND ' ||
5348 cHeader_Interface || '.pk2_value = :PK2_Value';
5349 ELSE -- cPK2_Value IS NULL.
5350 xProgress := 'POOB-80-1020';
5351 cHeader_where := cHeader_where || ' AND ' ||
5352 cHeader_Interface || '.pk2_value IS NULL';
5353 END IF;
5354
5355 IF cPK3_Value IS NOT NULL THEN
5356 xProgress := 'POOB-80-1030';
5357 cHeader_where := cHeader_where || ' AND ' ||
5358 cHeader_Interface || '.pk3_value = :PK3_Value';
5359 ELSE -- cPK3_Value IS NULL.
5360 xProgress := 'POOB-80-1040';
5361 cHeader_where := cHeader_where || ' AND ' ||
5362 cHeader_Interface || '.pk3_value IS NULL';
5363 END IF;
5364
5365 IF cPK4_Value IS NOT NULL THEN
5366 xProgress := 'POOB-80-1050';
5367 cHeader_where := cHeader_where || ' AND ' ||
5368 cHeader_Interface || '.pk4_value = :PK4_Value';
5369 ELSE -- cPK4_Value IS NULL.
5370 xProgress := 'POOB-80-1060';
5371 cHeader_where := cHeader_where || ' AND ' ||
5372 cHeader_Interface || '.pk4_value IS NULL';
5373 END IF;
5374
5375 IF cPK5_Value IS NOT NULL THEN
5376 xProgress := 'POOB-80-1070';
5377 cHeader_where := cHeader_where || ' AND ' ||
5378 cHeader_Interface || '.pk5_value = :PK5_Value';
5379
5380 ELSE -- cPK5_Value IS NULL.
5381 xProgress := 'POOB-80-1080';
5382 cHeader_where := cHeader_where || ' AND ' ||
5383 cHeader_Interface || '.pk5_value IS NULL';
5384 END IF;
5385
5386 /* Bug 1892253
5387 If an item has an attachment and if this
5388 item is used in more than 1 PO while
5389 extracting,then the attachment information
5390 of the item will be repeated in ece_attachment_headers
5391 n number of times if n number of POs with the same
5392 item are extracted.
5393 Added code to pick up no of rows having distinct
5394 attachment data from ece_attachment_headers and
5395 appended this number to the where condition.
5396 */
5397
5398
5399
5400 if centity_name = 'MTL_SYSTEM_ITEMS' then
5401 begin
5402 get_no_rows := 0;
5403
5404 select count(distinct(att_seq_num)) into get_no_rows
5405 from ece_attachment_headers
5406 where pk1_value = cPK1_value
5407 and pk2_value = cPK2_value
5408 and entity_name like 'MTL_SYSTEM_ITEMS';
5409 get_no_rows:=get_no_rows+1;
5410
5411 exception
5412 when others then null;
5413 end;
5414
5415 cHeader_where := cHeader_where || ' AND ' ||
5416 ' rownum < ' || get_no_rows;
5417 end if;
5418 /* 2279486
5419 Get the attachments(attached_document_id) pertaining only to the corrresponding
5420 release by using line id*/
5421 if centity_name = 'PO_LINES' then
5422 begin
5423 get_no_lrows := 0;
5424
5425 select count(distinct(attached_document_id)) into get_no_lrows
5426 from ece_attachment_headers
5427 where pk1_value = cPK1_value
5428 and entity_name like 'PO_LINES';
5429
5430 get_no_lrows:=get_no_lrows+1;
5431
5432 exception
5433 when others then null;
5434 end;
5435
5436 cHeader_where := cHeader_where || ' AND ' ||
5437 ' rownum < ' || get_no_lrows;
5438 end if;
5439
5440
5441 -- Bug 2187958
5442 /*cHeader_where := cHeader_where || ' ORDER BY ' ||
5443 cHeader_Interface || '.att_seq_num';*/
5444
5445 cHeader_where := cHeader_where || ' ORDER BY ' ||
5446 cHeader_Interface || '.attached_document_id';
5447
5448 cHeader_select := cHeader_select || ',' ||
5449 cHeader_Interface || '.rowid,' ||
5450 cHeader_X_Interface || '.rowid ';
5451
5452 -- Now form the complete SELECT SQL...
5453 cHeader_select := cHeader_select || cHeader_from || cHeader_where || ' FOR UPDATE';
5454 ec_debug.pl(3,'cHeader_select: ',cHeader_select);
5455
5456 -- Form DELETE SQL...
5457 cHeader_delete1 := 'DELETE FROM ' || cHeader_Interface || ' WHERE rowid = :col_rowid';
5458
5459 -- Form DELETE SQL for the Extension Table...
5460 cHeader_delete2 := 'DELETE FROM ' || cHeader_X_Interface || ' WHERE rowid = :col_rowid';
5461 if ec_debug.G_debug_level = 3 then
5462 ec_debug.pl(3,'cHeader_delete1: ',cHeader_delete1);
5463 ec_debug.pl(3,'cHeader_delete2: ',cHeader_delete2);
5464 end if;
5465
5466 -- Open Cursors
5467 xProgress := 'POOB-80-1090';
5468 Header_sel_c := dbms_sql.open_cursor;
5469
5470 xProgress := 'POOB-80-1100';
5471 Header_del_c1 := dbms_sql.open_cursor;
5472
5473 xProgress := 'POOB-80-1110';
5474 Header_del_c2 := dbms_sql.open_cursor;
5475
5476 -- Parse the SELECT SQL
5477 BEGIN
5478 xProgress := 'POOB-80-1120';
5479 dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
5480
5481 EXCEPTION
5482 WHEN OTHERS THEN
5483 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
5484 app_exception.raise_exception;
5485 END;
5486
5487
5488 -- Parse the DELETE1 SQL
5489 BEGIN
5490 xProgress := 'POOB-80-1130';
5491 dbms_sql.parse(Header_del_c1,cHeader_delete1,dbms_sql.native);
5492
5493 EXCEPTION
5494 WHEN OTHERS THEN
5495 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
5496 app_exception.raise_exception;
5497 END;
5498
5499 -- Parse the DELETE2 SQL
5500 BEGIN
5501 xProgress := 'POOB-80-1140';
5502 dbms_sql.parse(Header_del_c2,cHeader_delete2,dbms_sql.native);
5503
5504 EXCEPTION
5505 WHEN OTHERS THEN
5506 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
5507 app_exception.raise_exception;
5508 END;
5509
5510 --Set Counter
5511 xProgress := 'POOB-80-1150';
5512 iHeader_count := l_header_tbl.COUNT;
5513 if ec_debug.G_debug_level = 3 then
5514 ec_debug.pl(3,'iHeader_count: ',iHeader_count);
5515 end if;
5516
5517 xProgress := 'POOB-80-1160';
5518 ece_flatfile_pvt.define_interface_column_type(Header_sel_c,
5519 cHeader_select,
5520 ece_flatfile_pvt.G_MaxColWidth,
5521 l_header_tbl );
5522
5523 xProgress := 'POOB-80-1170';
5524 dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5525
5526 xProgress := 'POOB-80-1180';
5527 dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5528
5529 -- Bug 2198707
5530 dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5531 dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5532 dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5533 dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5534 IF cPK2_Value IS NOT NULL THEN
5535 dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5536 END IF;
5537 IF cPK3_Value IS NOT NULL THEN
5538 dbms_sql.bind_variable(Header_sel_c,'PK3_Value',cPK3_Value);
5539 END IF;
5540 IF cPK4_Value IS NOT NULL THEN
5541 dbms_sql.bind_variable(Header_sel_c,'PK4_Value',cPK4_Value);
5542 END IF;
5543 IF cPK5_Value IS NOT NULL THEN
5544 dbms_sql.bind_variable(Header_sel_c,'PK5_Value',cPK5_Value);
5545 END IF;
5546 --Execute the Cursor
5547 xProgress := 'POOB-80-1190';
5548 dummy := dbms_sql.execute(Header_sel_c);
5549
5550 xProgress := 'POOB-80-1200';
5551 WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP
5552 xProgress := 'POOB-80-1210';
5553 ece_flatfile_pvt.assign_column_value_to_tbl(Header_sel_c,l_header_tbl);
5554
5555 xProgress := 'POOB-80-1220';
5556 dbms_sql.column_value(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5557
5558 xProgress := 'POOB-80-1230';
5559 dbms_sql.column_value(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5560
5561 -- Find the Position of the Attachemnt Doc ID
5562 xProgress := 'POOB-80-1232';
5563 ece_flatfile_pvt.find_pos(l_header_tbl,'DOCUMENT_ID',nDoc_ID_pos);
5564 nDoc_ID := l_header_tbl(nDoc_ID_pos).value;
5565
5566 -- Find the Position of the Attachment Sequence Number
5567 xProgress := 'POOB-80-1240';
5568 ece_flatfile_pvt.find_pos(l_header_tbl,'ATT_SEQ_NUM',nSeq_Num_pos);
5569 -- Get the Attachment Sequence Number itself.
5570 nSeq_Num := l_header_tbl(nSeq_Num_pos).value;
5571 -- Bug 2187958
5572 ece_flatfile_pvt.find_pos(l_header_tbl,'ATTACHED_DOCUMENT_ID',nAtt_Doc_ID);
5573 -- Get the Attachment Document ID
5574 nAtt_Doc_ID := l_header_tbl(nAtt_Doc_ID).value;
5575
5576 if ec_debug.G_debug_level = 3 then
5577 ec_debug.pl(3,'nDoc_ID_pos: ',nDoc_ID_pos);
5578 ec_debug.pl(3,'nDoc_ID: ' ,nDoc_ID);
5579 ec_debug.pl(3,'nSeq_Num: ',nSeq_Num);
5580 ec_debug.pl(3,'nAtt_Doc_ID: ',nAtt_Doc_ID);
5581 end if;
5582
5583 xProgress := 'POOB-80-1250';
5584 ece_poo_transaction.write_to_file(cTransaction_Type,
5585 cCommunication_Method,
5586 cHeader_Interface,
5587 l_header_tbl,
5588 iOutput_width,
5589 iRun_id,
5590 cFile_Common_Key,
5591 null);
5592
5593 xProgress := 'POOB-80-1260';
5594 dbms_sql.bind_variable(Header_del_c1,'col_rowid',rHeader_rowid);
5595
5596 xProgress := 'POOB-80-1270';
5597 dbms_sql.bind_variable(Header_del_c2,'col_rowid',rHeader_X_rowid);
5598
5599 -- Execute the Cursor that deletes from the Interface Table
5600 xProgress := 'POOB-80-1280';
5601 dummy := dbms_sql.execute(Header_del_c1);
5602
5603 -- Execute the Cursor that deletes from the Extension Table
5604 xProgress := 'POOB-80-1290';
5605 dummy := dbms_sql.execute(Header_del_c2);
5606
5607 xProgress := 'POOB-80-1300';
5608 put_att_detail_to_output_table(cCommunication_Method,
5609 cTransaction_Type,
5610 iOutput_width,
5611 iRun_id,
5612 cDetail_Output_Level,
5613 cHeader_Interface,
5614 cDetail_Interface,
5615 nSeq_Num,
5616 cEntity_Name,
5617 cName,
5618 cPK1_Value,
5619 cPK2_Value,
5620 cPK3_Value,
5621 cPK4_Value,
5622 cPK5_Value,
5623 cFile_Common_Key,
5624 nAtt_Doc_ID );
5625 END LOOP;
5626
5627 -- Close the Cursors and Cleanup...
5628 xProgress := 'POOB-80-1310';
5629 dbms_sql.close_cursor(Header_sel_c);
5630 dbms_sql.close_cursor(Header_del_c1);
5631 dbms_sql.close_cursor(Header_del_c2);
5632 if ec_debug.G_debug_level >= 2 then
5633 ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5634 end if;
5635 EXCEPTION
5636 WHEN OTHERS THEN
5637 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
5638 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
5639
5640 app_exception.raise_exception;
5641
5642 END put_att_to_output_table;
5643
5644 PROCEDURE put_att_detail_to_output_table(
5645 cCommunication_Method IN VARCHAR2,
5646 cTransaction_Type IN VARCHAR2,
5647 iOutput_Width IN INTEGER,
5648 iRun_id IN INTEGER,
5649 cDetail_Output_Level IN NUMBER,
5650 cHeader_Interface IN VARCHAR2,
5651 cDetail_Interface IN VARCHAR2,
5652 cAtt_Seq_Num IN NUMBER,
5653 cEntity_Name IN VARCHAR2,
5654 cName IN VARCHAR2,
5655 cPK1_Value IN VARCHAR2,
5656 cPK2_Value IN VARCHAR2,
5657 cPK3_Value IN VARCHAR2,
5658 cPK4_Value IN VARCHAR2,
5659 cPK5_Value IN VARCHAR2,
5660 cFile_Common_Key IN VARCHAR2,
5661 cAtt_Doc_ID IN NUMBER) IS
5662
5663 xProgress VARCHAR2(80);
5664
5665 l_detail_tbl ece_flatfile_pvt.Interface_tbl_type;
5666
5667 c_detail_common_key_name VARCHAR2(40);
5668
5669 Detail_sel_c INTEGER;
5670 Detail_del_c1 INTEGER;
5671 Detail_del_c2 INTEGER;
5672 cDetail_select VARCHAR2(32000);
5673 cDetail_from VARCHAR2(32000);
5674 cDetail_where VARCHAR2(32000);
5675 cDetail_delete1 VARCHAR2(32000);
5676 cDetail_delete2 VARCHAR2(32000);
5677
5678 cDetail_X_Interface VARCHAR2(50);
5679
5680 iDetail_count NUMBER;
5681 dummy INTEGER;
5682 rDetail_rowid ROWID;
5683 rDetail_X_rowid ROWID;
5684 get_no_rows INTEGER; -- Bug 1892253
5685 get_no_lrows INTEGER;
5686 BEGIN
5687 if ec_debug.G_debug_level >= 2 then
5688 ec_debug.push('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
5689 ec_debug.pl(3,'cDetail_Output_Level: ',cDetail_Output_Level);
5690 ec_debug.pl(3,'cAtt_Seq_Num: ', cAtt_Seq_Num);
5691 ec_debug.pl(3,'cEntity_Name: ', cEntity_Name);
5692 ec_debug.pl(3,'cName: ', cName);
5693 ec_debug.pl(3,'cPK1_Value: ', cPK1_Value);
5694 ec_debug.pl(3,'cPK2_Value: ', cPK2_Value);
5695 ec_debug.pl(3,'cPK3_Value: ', cPK3_Value);
5696 ec_debug.pl(3,'cPK4_Value: ', cPK4_Value);
5697 ec_debug.pl(3,'cPK5_Value: ', cPK5_Value);
5698 ec_debug.pl(3,'cFile_Common_Key: ', cFile_Common_Key);
5699 ec_debug.pl(3,'cAtt_Doc_ID: ' ,cAtt_Doc_ID);
5700 end if;
5701
5702 xProgress := 'POOB-90-1000';
5703 ece_flatfile_pvt.select_clause(cTransaction_Type,
5704 cCommunication_Method,
5705 cDetail_Interface,
5706 cDetail_X_Interface,
5707 l_detail_tbl,
5708 c_detail_common_key_name,
5709 cDetail_select,
5710 cDetail_from,
5711 cDetail_where,
5712 cDetail_Output_Level);
5713
5714 -- Build the WHERE and the ORDER BY Clause.
5715 xProgress := 'POOB-90-1010';
5716 cDetail_where := cDetail_where || ' AND ' ||
5717 cDetail_Interface || '.run_id = :iRun_id';
5718
5719 -- Get the right Attachment Sequence.
5720 xProgress := 'POOB-90-1012';
5721 cDetail_where := cDetail_where || ' AND ' ||
5722 cDetail_Interface || '.att_seq_num = :cAtt_Seq_Num';
5723 -- Bug 2187958
5724 -- Get the Attachment document ID.
5725 xProgress := 'POOB-90-1012A';
5726 cDetail_where := cDetail_where || ' AND ' ||
5727 cDetail_Interface || '.attached_document_id = :cAtt_Doc_ID';
5728
5729
5730 -- Entity Name must not be NULL.
5731 xProgress := 'POOB-90-1014';
5732 cDetail_where := cDetail_where || ' AND ' ||
5733 cDetail_Interface || '.entity_name = :cEntity_Name';
5734
5735 -- Name must not be NULL.
5736 xProgress := 'POOB-90-1016';
5737 cDetail_where := cDetail_where || ' AND ' || 'UPPER(' ||
5738 cDetail_Interface || '.name) = UPPER(:cName)';
5739
5740 -- PK1 Value must not be NULL.
5741 xProgress := 'POOB-90-1018';
5742 cDetail_where := cDetail_where || ' AND ' ||
5743 cDetail_Interface || '.pk1_value = :cPK1_Value';
5744
5745 xProgress := 'POOB-90-1020';
5746 IF cPK2_Value IS NOT NULL THEN
5747 xProgress := 'POOB-90-1030';
5748 cDetail_where := cDetail_where || ' AND ' ||
5749 cDetail_Interface || '.pk2_value = :cPK2_Value';
5750 ELSE -- cPK2_Value IS NULL.
5751 xProgress := 'POOB-90-1040';
5752 cDetail_where := cDetail_where || ' AND ' ||
5753 cDetail_Interface || '.pk2_value IS NULL';
5754 END IF;
5755
5756 IF cPK3_Value IS NOT NULL THEN
5757 xProgress := 'POOB-90-1050';
5758 cDetail_where := cDetail_where || ' AND ' ||
5759 cDetail_Interface || '.pk3_value = :cPK3_Value';
5760 ELSE -- cPK3_Value IS NULL.
5761 xProgress := 'POOB-90-1060';
5762 cDetail_where := cDetail_where || ' AND ' ||
5763 cDetail_Interface || '.pk3_value IS NULL';
5764 END IF;
5765
5766 IF cPK4_Value IS NOT NULL THEN
5767 xProgress := 'POOB-90-1070';
5768 cDetail_where := cDetail_where || ' AND ' ||
5769 cDetail_Interface || '.pk4_value = :cPK4_Value';
5770 ELSE -- cPK4_Value IS NULL.
5771 xProgress := 'POOB-90-1080';
5772 cDetail_where := cDetail_where || ' AND ' ||
5773 cDetail_Interface || '.pk4_value IS NULL';
5774 END IF;
5775
5776 IF cPK5_Value IS NOT NULL THEN
5777 xProgress := 'POOB-90-1090';
5778 cDetail_where := cDetail_where || ' AND ' ||
5779 cDetail_Interface || '.pk5_value = :cPK5_Value';
5780 ELSE -- cPK5_Value IS NULL.
5781 xProgress := 'POOB-90-1100';
5782 cDetail_where := cDetail_where || ' AND ' ||
5783 cDetail_Interface || '.pk5_value IS NULL';
5784 END IF;
5785
5786 /* Bug 1892253.
5787 Added query to pick the number of segments
5788 based on pk2_value,pk1_value and sequence
5789 number and append the no of segments obtained
5790 to cDetail_where condition
5791 */
5792
5793 if centity_name = 'MTL_SYSTEM_ITEMS' then
5794 get_no_rows:=0;
5795 begin
5796
5797 select max(segment_number) into get_no_rows
5798 from ece_attachment_details
5799 where pk1_value = cPK1_value
5800 and pk2_value = cPK2_value
5801 and entity_name like 'MTL_SYSTEM_ITEMS'
5802 and attached_document_id = catt_doc_id;
5803
5804 get_no_rows := get_no_rows + 1;
5805
5806 exception
5807 when others then null;
5808 end;
5809
5810 cDetail_where := cDetail_where || ' AND ' ||
5811 ' rownum < ' || get_no_rows;
5812 end if;
5813 /* 2279486
5814 Get the no of detailed line attachments pertaining only to the corrresponding
5815 release by using line id and attached_document_id*/
5816
5817
5818 if centity_name = 'PO_LINES' then
5819 begin
5820 get_no_lrows := 0;
5821
5822 select count(distinct(segment_number)) into get_no_lrows
5823 from ece_attachment_details
5824 where pk1_value = cPK1_value
5825 and entity_name like 'PO_LINES'
5826 and attached_document_id = catt_doc_id;
5827
5828 get_no_lrows:=get_no_lrows+1;
5829
5830 exception
5831 when others then null;
5832 end;
5833
5834 cDetail_where := cDetail_where || ' AND ' ||
5835 ' rownum < ' || get_no_lrows;
5836 end if;
5837
5838
5839
5840 cDetail_where := cDetail_where || ' ORDER BY ' ||
5841 cDetail_Interface || '.segment_number';
5842
5843 cDetail_select := cDetail_select || ', ' ||
5844 cDetail_Interface || '.rowid, ' ||
5845 cDetail_X_Interface || '.rowid ';
5846
5847 -- Now form the complete SELECT SQL...
5848 cDetail_select := cDetail_select || cDetail_from || cDetail_where || ' FOR UPDATE';
5849
5850 -- Form DELETE SQL...
5851 cDetail_delete1 := 'DELETE FROM ' || cDetail_Interface || ' WHERE rowid = :col_rowid';
5852
5853 -- Form DELETE SQL for the Extension Table...
5854 cDetail_delete2 := 'DELETE FROM ' || cDetail_X_Interface || ' WHERE rowid = :col_rowid';
5855 if ec_debug.G_debug_level = 3 then
5856 ec_debug.pl(3,'cDetail_select: ',cDetail_select);
5857 ec_debug.pl(3,'cDetail_delete1: ',cDetail_delete1);
5858 ec_debug.pl(3,'cDetail_delete2: ',cDetail_delete2);
5859 end if;
5860
5861 -- Open Cursors
5862 xProgress := 'POOB-90-1110';
5863 Detail_sel_c := dbms_sql.open_cursor;
5864
5865 xProgress := 'POOB-90-1120';
5866 Detail_del_c1 := dbms_sql.open_cursor;
5867
5868 xProgress := 'POOB-90-1130';
5869 Detail_del_c2 := dbms_sql.open_cursor;
5870
5871 -- Parse the SELECT SQL
5872 BEGIN
5873 xProgress := 'POOB-90-1140';
5874 dbms_sql.parse(Detail_sel_c,cDetail_select,dbms_sql.native);
5875
5876 EXCEPTION
5877 WHEN OTHERS THEN
5878 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_select);
5879 app_exception.raise_exception;
5880 END;
5881
5882 -- Parse the DELETE1 SQL
5883 BEGIN
5884 xProgress := 'POOB-90-1150';
5885 dbms_sql.parse(Detail_del_c1,cDetail_delete1,dbms_sql.native);
5886
5887 EXCEPTION
5888 WHEN OTHERS THEN
5889 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete1);
5890 app_exception.raise_exception;
5891 END;
5892
5893 -- Parse the DELETE2 SQL
5894 BEGIN
5895 xProgress := 'POOB-90-1160';
5896 dbms_sql.parse(Detail_del_c2,cDetail_delete2,dbms_sql.native);
5897
5898 EXCEPTION
5899 WHEN OTHERS THEN
5900 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete2);
5901 app_exception.raise_exception;
5902 END;
5903
5904 --Set Counter
5905 xProgress := 'POOB-90-1170';
5906 iDetail_count := l_detail_tbl.COUNT;
5907 if ec_debug.G_debug_level = 3 then
5908 ec_debug.pl(3,'iDetail_count: ',iDetail_count);
5909 end if;
5910
5911 xProgress := 'POOB-90-1180';
5912 ece_flatfile_pvt.define_interface_column_type(Detail_sel_c,
5913 cDetail_select,
5914 ece_flatfile_pvt.G_MaxColWidth,
5915 l_detail_tbl);
5916
5917 xProgress := 'POOB-90-1190';
5918 dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5919
5920 xProgress := 'POOB-90-1200';
5921 dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5922
5923 --Bind Variables
5924 xProgress := 'POOB-90-1201';
5925 dbms_sql.bind_variable(Detail_sel_c,':iRun_id',iRun_id);
5926
5927 xProgress := 'POOB-90-1202';
5928 dbms_sql.bind_variable(Detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
5929
5930 xProgress := 'POOB-90-1202a';
5931 dbms_sql.bind_variable(Detail_sel_c,':cAtt_Doc_ID',cAtt_Doc_ID);
5932
5933 xProgress := 'POOB-90-1203';
5934 dbms_sql.bind_variable(Detail_sel_c,':cEntity_Name',cEntity_Name);
5935
5936 xProgress := 'POOB-90-1204';
5937 dbms_sql.bind_variable(Detail_sel_c,':cName',cName);
5938
5939 xProgress := 'POOB-90-1205';
5940 dbms_sql.bind_variable(Detail_sel_c,':cPK1_Value',cPK1_Value);
5941
5942 IF cPK2_Value IS NOT NULL THEN
5943 xProgress := 'POOB-90-1206';
5944 dbms_sql.bind_variable(Detail_sel_c,':cPK2_Value',cPK2_Value);
5945 END IF;
5946
5947 IF cPK3_Value IS NOT NULL THEN
5948 xProgress := 'POOB-90-1207';
5949 dbms_sql.bind_variable(Detail_sel_c,':cPK3_Value',cPK3_Value);
5950 END IF;
5951
5952 IF cPK4_Value IS NOT NULL THEN
5953 xProgress := 'POOB-90-1208';
5954 dbms_sql.bind_variable(Detail_sel_c,':cPK4_Value',cPK4_Value);
5955 END IF;
5956
5957 IF cPK5_Value IS NOT NULL THEN
5958 xProgress := 'POOB-90-1209';
5959 dbms_sql.bind_variable(Detail_sel_c,':cPK5_Value',cPK5_Value);
5960 END IF;
5961
5962 --Execute the Cursor
5963 xProgress := 'POOB-90-1210';
5964 dummy := dbms_sql.execute(Detail_sel_c);
5965
5966 xProgress := 'POOB-90-1220';
5967 WHILE dbms_sql.fetch_rows(Detail_sel_c) > 0 LOOP
5968 xProgress := 'POOB-90-1230';
5969 ece_flatfile_pvt.assign_column_value_to_tbl(Detail_sel_c,l_detail_tbl);
5970
5971 xProgress := 'POOB-90-1240';
5972 dbms_sql.column_value(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5973
5974 xProgress := 'POOB-90-1250';
5975 dbms_sql.column_value(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5976
5977 xProgress := 'POOB-90-1260';
5978 ece_poo_transaction.write_to_file(cTransaction_Type,
5979 cCommunication_Method,
5980 cDetail_Interface,
5981 l_detail_tbl,
5982 iOutput_width,
5983 iRun_id,
5984 cFile_Common_Key,
5985 null);
5986
5987 xProgress := 'POOB-90-1270';
5988 dbms_sql.bind_variable(Detail_del_c1,'col_rowid',rDetail_rowid);
5989
5990 xProgress := 'POOB-90-1280';
5991 dbms_sql.bind_variable(Detail_del_c2,'col_rowid',rDetail_X_rowid);
5992
5993 -- Execute the Cursor that deletes from the Interface Table
5994 xProgress := 'POOB-90-1290';
5995 dummy := dbms_sql.execute(Detail_del_c1);
5996
5997 -- Execute the Cursor that deletes from the Extension Table
5998 xProgress := 'POOB-90-1300';
5999 dummy := dbms_sql.execute(Detail_del_c2);
6000 END LOOP;
6001
6002 -- Close the Cursors and Cleanup...
6003 xProgress := 'POOB-90-1310';
6004 dbms_sql.close_cursor(Detail_sel_c);
6005 dbms_sql.close_cursor(Detail_del_c1);
6006 dbms_sql.close_cursor(Detail_del_c2);
6007 if ec_debug.G_debug_level >= 2 then
6008 ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
6009 end if;
6010 EXCEPTION
6011 WHEN OTHERS THEN
6012 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
6013 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
6014
6015 app_exception.raise_exception;
6016
6017 END put_att_detail_to_output_table;
6018
6019
6020 PROCEDURE write_to_file(
6021 cTransaction_Type IN VARCHAR2,
6022 cCommunication_Method IN VARCHAR2,
6023 cInterface_Table IN VARCHAR2,
6024 p_Interface_tbl IN ece_flatfile_pvt.Interface_tbl_type,
6025 iOutput_width IN INTEGER,
6026 iRun_id IN INTEGER,
6027 p_common_key IN VARCHAR2,
6028 p_foreign_key IN NUMBER) IS
6029
6030 xProgress VARCHAR2(30);
6031 cOutput_path VARCHAR2(120);
6032 iLine_pos INTEGER;
6033 iData_count INTEGER := p_Interface_tbl.COUNT;
6034 iStart_num INTEGER;
6035 iRow_num INTEGER;
6036 cInsert_stmt VARCHAR2(32000);
6037 l_common_key VARCHAR2(255) := p_common_key;
6038 l_count NUMBER;
6039 cValue VARCHAR2(32000);
6040 cSrc_tbl_val_wo_newl VARCHAR2(32000);
6041 cSrc_tbl_val_wo_frmf VARCHAR2(32000);
6042 cSrc_tbl_val_wo_tab VARCHAR2(32000);
6043 c_local_chr_10 VARCHAR2(1) := fnd_global.local_chr(10);
6044 c_local_chr_13 VARCHAR2(1) := fnd_global.local_chr(13);
6045 c_local_chr_9 VARCHAR2(1) := fnd_global.local_chr(9);
6046
6047
6048 BEGIN
6049 if EC_DEBUG.G_debug_level >= 2 then
6050 ec_debug.push('ECE_POO_TRANSACTION.WRITE_TO_FILE');
6051 end if;
6052 xProgress := 'POOB-WR-1020';
6053 FOR i IN 1..iData_count LOOP
6054 xProgress := 'POOB-WR-1030';
6055 l_count := i;
6056 xProgress := 'POOB-WR-1040';
6057 IF p_Interface_tbl(i).Record_num IS NOT NULL AND
6058 p_Interface_tbl(i).position IS NOT NULL AND
6059 p_Interface_tbl(i).data_length IS NOT NULL THEN
6060 xProgress := 'POOB-WR-1050';
6061 iRow_num := i;
6062 if EC_DEBUG.G_debug_level >= 3 then
6063 ec_debug.pl(3,'iRow_num : ',iRow_num);
6064 end if;
6065 xProgress := 'POOB-WR-1060';
6066 IF p_Interface_tbl(i).interface_column_name = 'RUN_ID' THEN
6067 cValue := iRun_id;
6068 elsif
6069 p_Interface_tbl(i).interface_column_name =
6070 'TRANSACTION_RECORD_ID' THEN
6071 cValue := p_foreign_key;
6072 else
6073 cSrc_tbl_val_wo_newl :=
6074 replace(p_Interface_tbl(i).value, c_local_chr_10,'');
6075 cSrc_tbl_val_wo_frmf :=
6076 replace(cSrc_tbl_val_wo_newl, c_local_chr_13,'');
6077 cSrc_tbl_val_wo_tab :=
6078 replace(cSrc_tbl_val_wo_frmf, c_local_chr_9,'');
6079 cValue := cSrc_tbl_val_wo_tab;
6080 end if;
6081
6082 cInsert_stmt := cInsert_stmt || substrb(rpad(nvl(cValue,' '),
6083 TO_CHAR(p_Interface_tbl(i).data_length),' '),1,
6084 p_Interface_tbl(i).data_length);
6085
6086 -- ******************************************************
6087 -- the following two lines is for testing/debug purpose
6088 -- ******************************************************
6089 -- cInsert_stmt := cInsert_stmt || rpad(substrb(p_Interface_tbl(i).interface_column_name,1,p_Interface_tbl(i).data_length-2)||
6090 -- substrb(TO_CHAR(p_Interface_tbl(i).data_length),1,2), TO_CHAR(p_Interface_tbl(i).data_length),' ');
6091 END IF;
6092
6093 xProgress := 'POOB-WR-1070';
6094 IF i < iData_count THEN
6095 xProgress := 'POOB-WR-1080';
6096 IF p_Interface_tbl(i).Record_num <> p_Interface_tbl(i+1).Record_num THEN
6097 xProgress := 'POOB-WR-1090';
6098 cInsert_stmt := l_common_key || LPAD(NVL(p_Interface_tbl(iRow_num).Record_num,0),4,'0') ||
6099 RPAD(NVL(p_Interface_tbl(iRow_num).layout_code,' '),2) ||
6100 RPAD(NVL(p_Interface_tbl(iRow_num).record_qualifier,' '),3) || cInsert_stmt;
6101
6102 xProgress := 'POOB-WR-1100';
6103 utl_file.put_line(ece_poo_transaction.uFile_type,cInsert_stmt);
6104 xProgress := 'POOB-WR-1110';
6105 cInsert_stmt := NULL;
6106 -- cInsert_stmt := '*' || TO_CHAR(p_Interface_tbl(i).Record_num);
6107 END IF;
6108 ELSE
6109 xProgress := 'POOB-WR-1120';
6110 /* Bug# 2108977 :- Added the following codition to prevent NULL records from causing
6111 erros */
6112
6113 IF iRow_num IS NOT NULL THEN
6114 cInsert_stmt := l_common_key || LPAD(NVL(p_Interface_tbl(iRow_num).Record_num,0),4,'0') ||
6115 RPAD(NVL(p_Interface_tbl(iRow_num).layout_code,' '),2) ||
6116 RPAD(NVL(p_Interface_tbl(iRow_num).record_qualifier,' '),3) || cInsert_stmt;
6117
6118 xProgress := 'POOB-WR-1130';
6119 utl_file.put_line(ece_poo_transaction.uFile_type,cInsert_stmt);
6120 END IF;
6121 END IF;
6122 END LOOP;
6123
6124 if EC_DEBUG.G_debug_level >= 2 then
6125 ec_debug.pop('ECE_POO_TRANSACTION.WRITE_TO_FILE');
6126 end if;
6127
6128 EXCEPTION
6129 WHEN utl_file.write_error THEN
6130 ec_debug.pl(0,'EC','ECE_UTL_WRITE_ERROR',NULL);
6131 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
6132 app_exception.raise_exception;
6133
6134 WHEN utl_file.invalid_path THEN
6135 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_PATH',NULL);
6136 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
6137 app_exception.raise_exception;
6138
6139 WHEN utl_file.invalid_operation THEN
6140 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_OPERATION',NULL);
6141 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
6142 app_exception.raise_exception;
6143
6144 WHEN OTHERS THEN
6145 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_POO_TRANSACTION.WRITE_TO_FILE');
6146 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
6147 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
6148 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
6149
6150 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', 'to_char(l_count)');
6151 ec_debug.pl(0,'EC','ECE_PLSQL_VALUE','COLUMN_NAME', 'p_interface_tbl(l_count).value');
6152 ec_debug.pl(0,'EC','ECE_PLSQL_DATA_TYPE','COLUMN_NAME', 'p_interface_tbl(l_count).data_type');
6153 ec_debug.pl(0,'EC','ECE_PLSQL_COLUMN_NAME','COLUMN_NAME', 'p_interface_tbl(l_count).base_column');
6154 app_exception.raise_exception;
6155
6156 END write_to_file;
6157
6158 END ECE_POO_TRANSACTION;
6159