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