[Home] [Help]
PACKAGE BODY: APPS.ECE_POCO_TRANSACTION
Source
1 PACKAGE BODY ece_poco_transaction AS
2 -- $Header: ECPOCOB.pls 120.10.12010000.1 2008/07/25 07:25:44 appldev ship $
3 iOutput_width INTEGER := 4000; -- 2823215
4 i_path VARCHAR2(1000);
5 i_filename VARCHAR2(1000);
6 PROCEDURE extract_poco_outbound(errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 cOutput_Path IN VARCHAR2,
9 cOutput_Filename IN VARCHAR2,
10 cPO_Number_From IN VARCHAR2,
11 cPO_Number_To IN VARCHAR2,
12 cRDate_From IN VARCHAR2,
13 cRDate_To IN VARCHAR2,
14 cPC_Type IN VARCHAR2,
15 cVendor_Name IN VARCHAR2,
16 cVendor_Site_Code IN VARCHAR2,
17 v_debug_mode IN NUMBER DEFAULT 0) IS
18
19 xProgress VARCHAR2(80);
20 v_LevelProcessed VARCHAR2(40);
21 iRun_id NUMBER := 0;
22 iOutput_width INTEGER := 4000;
23 cTransaction_Type VARCHAR2(120) := 'POCO';
24 cCommunication_Method VARCHAR2(120) := 'EDI';
25 cHeader_Interface VARCHAR2(120) := 'ECE_PO_INTERFACE_HEADERS';
26 cLine_Interface VARCHAR2(120) := 'ECE_PO_INTERFACE_LINES';
27 cShipment_Interface VARCHAR2(120) := 'ECE_PO_INTERFACE_SHIPMENTS';
28 cProject_Interface VARCHAR2(120) := 'ECE_PO_DISTRIBUTIONS'; -- Bug 1891291
29 l_line_text VARCHAR2(2000);
30 cRevised_Date_From DATE := TO_DATE(cRDate_From,'YYYY/MM/DD HH24:MI:SS');
31 cRevised_Date_To DATE := TO_DATE(cRDate_To, 'YYYY/MM/DD HH24:MI:SS') + 1;
32 cEnabled VARCHAR2(1) := 'Y';
33 ece_transaction_disabled EXCEPTION;
34 xHeaderCount NUMBER;
35 cFilename VARCHAR2(30) := NULL; --2430822
36
37
38 CURSOR c_output IS
39 SELECT text
40 FROM ece_output
41 WHERE run_id = iRun_id
42 ORDER BY line_id;
43
44 BEGIN
45 xProgress := 'POCO-10-1000';
46 ec_debug.enable_debug(v_debug_mode);
47 ec_debug.push('ECE_POCO_TRANSACTION.EXTRACT_POCO_OUTBOUND' );
48 ec_debug.pl(3,'cOutput_Path: ', cOutput_Path );
49 ec_debug.pl(3,'cOutput_Filename: ', cOutput_Filename );
50 ec_debug.pl(3,'cPO_Number_From: ', cPO_Number_From );
51 ec_debug.pl(3,'cPO_Number_To: ', cPO_Number_To );
52 ec_debug.pl(3,'cRDate_From: ', cRDate_From );
53 ec_debug.pl(3,'cRDate_To: ', cRDate_To );
54 ec_debug.pl(3,'cPC_Type: ', cPC_Type );
55 ec_debug.pl(3,'cVendor_Name: ', cVendor_Name );
56 ec_debug.pl(3,'cVendor_Site_Code: ',cVendor_Site_Code );
57 ec_debug.pl(3,'v_debug_mode: ', v_debug_mode );
58
59 /* Check to see if the transaction is enabled. If not, abort */
60 xProgress := 'POCO-10-1001';
61 fnd_profile.get('ECE_' || cTransaction_Type || '_ENABLED',cEnabled);
62
63 xProgress := 'POCO-10-1002';
64 IF cEnabled = 'N' THEN
65 xProgress := 'POCO-10-1003';
66 RAISE ece_transaction_disabled;
67 END IF;
68
69 xProgress := 'POCO-10-1004';
70 BEGIN
71 SELECT ece_output_runs_s.NEXTVAL
72 INTO iRun_id
73 FROM DUAL;
74
75 EXCEPTION
76 WHEN NO_DATA_FOUND THEN
77 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_OUTPUT_RUNS_S');
78
79 END;
80
81 ec_debug.pl(3,'iRun_id: ',iRun_id);
82
83 xProgress := 'POCO-10-1005';
84 ec_debug.pl(0,'EC','ECE_POCO_START',NULL);
85
86 xProgress := 'POCO-10-1010';
87 ec_debug.pl(0,'EC','ECE_RUN_ID','RUN_ID',iRun_id);
88
89 ece_poo_transaction.project_sel_c:=0; --Bug 2490109
90
91 IF cOutput_Filename IS NULL THEN --Bug 2430822
92 cFilename := 'POCO' || iRun_id || '.dat';
93 ELSE
94 cFilename := cOutput_Filename;
95 END IF;
96
97 -- Open the file for write.
98 xProgress := 'POO-10-1040';
99 if ec_debug.G_debug_level = 1 then
100 ec_debug.pl(1,'Output File:',cFilename);
101 ec_debug.pl(1,'path --> ', cOutput_Path);
102 -- ec_debug.pl(1,'Open Output file'); --Bug 2034376
103 end if;
104 i_path := cOutput_Path;
105 i_filename := cFilename;
106 -- ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767); --Bug 2887790
107
108 xProgress := 'POCO-10-1020';
109 ec_debug.pl(1,'Call Populate Poco Trx procedure'); --Bug 2034376
110 ece_poco_transaction.populate_poco_trx(
111 cCommunication_Method,
112 cTransaction_Type,
113 iOutput_width,
114 SYSDATE,
115 iRun_id,
116 cHeader_Interface,
117 cLine_Interface,
118 cShipment_Interface,
119 cProject_Interface,
120 cRevised_Date_From,
121 cRevised_Date_To,
122 cVendor_Name,
123 cVendor_Site_Code,
124 cPC_Type,
125 cPO_Number_From,
126 cPO_Number_To);
127
128 /* xProgress := 'POCO-10-1030';
129 ec_debug.pl(1,'Call Put To Output Table procedure'); --Bug 2034376
130
131 select count(*)
132 into xHeaderCount
133 from ECE_PO_INTERFACE_HEADERS
134 where run_id = iRun_id; */
135
136
137
138 -- 2823215
139
140 /* ec_debug.pl(1,'NUMBER OF RECORDS PROCESSED IS ',xHeaderCount);
141 ece_poco_transaction.put_data_to_output_table(
142 cCommunication_Method,
143 cTransaction_Type,
144 iOutput_width,
145 iRun_id,
146 cHeader_Interface,
147 cLine_Interface,
148 cShipment_Interface,
149 cProject_Interface); */
150
151 xProgress := 'POCO-10-1090';
152 ec_debug.pl(1,'Close Output file'); --Bug 2034376
153 if (utl_file.is_open(ece_poo_transaction.uFile_type)) then
154 utl_file.fclose(ece_poo_transaction.uFile_type);
155 end if;
156
157
158
159 IF ec_mapping_utils.ec_get_trans_upgrade_status(cTransaction_Type) = 'U' THEN
160 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
161 retcode := 1;
162 END IF;
163
164 ec_debug.pop('ECE_POCO_TRANSACTION.EXTRACT_POCO_OUTBOUND');
165 ec_debug.disable_debug;
166 COMMIT;
167
168 EXCEPTION
169 WHEN ece_transaction_disabled THEN
170 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED','TRANSACTION',cTransaction_type);
171 retcode := 1;
172 ec_debug.disable_debug;
173 ROLLBACK;
174
175 WHEN utl_file.write_error THEN
176 ec_debug.pl(0,'EC','ECE_UTL_WRITE_ERROR',NULL);
177 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
178
179 retcode := 2;
180 ec_debug.disable_debug;
181 if (utl_file.is_open(ece_poo_transaction.uFile_type))
182 then
183 utl_file.fclose(ece_poo_transaction.uFile_type);
184 end if;
185 ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767);
186 utl_file.fclose(ece_poo_transaction.uFile_type);
187 ROLLBACK;
188
189 WHEN utl_file.invalid_path THEN
190 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_PATH',NULL);
191 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
192
193 retcode := 2;
194 ec_debug.disable_debug;
195 ROLLBACK;
196
197 WHEN utl_file.invalid_operation THEN
198 ec_debug.pl(0,'EC','ECE_UTIL_INVALID_OPERATION',NULL);
199 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
200
201 retcode := 2;
202 ec_debug.disable_debug;
203 if (utl_file.is_open(ece_poo_transaction.uFile_type))
204 then
205 utl_file.fclose(ece_poo_transaction.uFile_type);
206 end if;
207 ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767);
208 utl_file.fclose(ece_poo_transaction.uFile_type);
209 ROLLBACK;
210
211 WHEN OTHERS THEN
212 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
213 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
214
215 retcode := 2;
216 ec_debug.disable_debug;
217 if (utl_file.is_open(ece_poo_transaction.uFile_type))
218 then
219 utl_file.fclose(ece_poo_transaction.uFile_type);
220 end if;
221 ece_poo_transaction.uFile_type := utl_file.fopen(cOutput_Path,cFilename,'W',32767);
222 utl_file.fclose(ece_poo_transaction.uFile_type);
223 ROLLBACK;
224
225 END extract_poco_outbound;
226
227 PROCEDURE populate_poco_trx(cCommunication_Method IN VARCHAR2,
228 cTransaction_Type IN VARCHAR2,
229 iOutput_width IN INTEGER,
230 dTransaction_date IN DATE,
231 iRun_id IN INTEGER,
232 cHeader_Interface IN VARCHAR2,
233 cLine_Interface IN VARCHAR2,
234 cShipment_Interface IN VARCHAR2,
235 cProject_Interface IN VARCHAR2,
236 cRevised_Date_From IN DATE,
237 cRevised_Date_To IN DATE,
238 cSupplier_Name IN VARCHAR2,
239 cSupplier_Site IN VARCHAR2,
240 cDocument_Type IN VARCHAR2,
241 cPO_Number_From IN VARCHAR2,
242 cPO_Number_To IN VARCHAR2) IS
243
244 xProgress VARCHAR2(80);
245 v_LevelProcessed VARCHAR2(40);
246
247 cAtt_Header_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_HEADERS';
248 cAtt_Detail_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_DETAILS';
249
250 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
251 l_line_tbl ece_flatfile_pvt.Interface_tbl_type;
252 l_shipment_tbl ece_flatfile_pvt.Interface_tbl_type;
253 l_key_tbl ece_flatfile_pvt.Interface_tbl_type;
254
255 l_hdr_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
256 l_hdr_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
257 l_ln_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
258 l_ln_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
259 l_mi_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
260 l_mi_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
261 l_msi_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
262 l_msi_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
263 l_shp_att_hdr_tbl ece_flatfile_pvt.Interface_tbl_type;
264 l_shp_att_dtl_tbl ece_flatfile_pvt.Interface_tbl_type;
265
266 iAtt_hdr_pos NUMBER := 0;
267 iAtt_ln_pos NUMBER := 0;
268 iAtt_mi_pos NUMBER := 0;
269 iAtt_msi_pos NUMBER := 0;
270 iAtt_shp_pos NUMBER := 0;
271
272 v_project_acct_installed BOOLEAN;
273 v_project_acct_short_name VARCHAR2(2) := 'PA';
274 v_project_acct_status VARCHAR2(120);
275 v_project_acct_industry VARCHAR2(120);
276 v_project_acct_schema VARCHAR2(120);
277
278 v_att_enabled VARCHAR2(10);
279 v_header_att_enabled VARCHAR2(10);
280 v_line_att_enabled VARCHAR2(10);
281 v_mitem_att_enabled VARCHAR2(10);
282 v_iitem_att_enabled VARCHAR2(10);
283 v_ship_att_enabled VARCHAR2(10);
284 n_att_seg_size NUMBER;
285
286 v_entity_name VARCHAR2(120);
287 v_pk1_value VARCHAR2(120);
288 v_pk2_value VARCHAR2(120);
289
290 header_sel_c INTEGER;
291 line_sel_c INTEGER;
292 shipment_sel_c INTEGER;
293
294 cHeader_select VARCHAR2(32000);
295 cLine_select VARCHAR2(32000);
296 cShipment_select VARCHAR2(32000);
297
298 cHeader_from VARCHAR2(32000);
299 cLine_from VARCHAR2(32000);
300 cShipment_from VARCHAR2(32000);
301
302 cHeader_where VARCHAR2(32000);
303 cLine_where VARCHAR2(32000);
304 cShipment_where VARCHAR2(32000);
305
306 iHeader_count NUMBER := 0;
307 iLine_count NUMBER := 0;
308 iShipment_count NUMBER := 0;
309 --iKey_count NUMBER := 0;
310
311 l_header_fkey NUMBER;
312 l_line_fkey NUMBER;
313 l_shipment_fkey NUMBER;
314
315 nHeader_key_pos NUMBER;
316 nLine_key_pos NUMBER;
317 nShipment_key_pos NUMBER;
318
319 dummy INTEGER;
320 n_trx_date_pos NUMBER;
321 nDocument_type_pos NUMBER;
322 nPO_Number_pos NUMBER;
323 nPO_Type_pos NUMBER;
324 nRelease_num_pos NUMBER;
325 nRelease_ID_pos NUMBER;
326 nLine_num_pos NUMBER;
327 nLine_Location_ID_pos NUMBER;
328 nShip_Line_Location_ID_pos NUMBER;
329 nQuantity_pending_pos NUMBER;
330 nCancel_Flag_pos NUMBER;
331 nCancel_Date_pos NUMBER;
332 nCancel_Date_posl NUMBER;
333 l_document_type VARCHAR2(30);
334 nOrganization_ID NUMBER;
335 nItem_ID_pos NUMBER;
336
337 v_drop_ship_flag NUMBER;
338 rec_order_line_info OE_DROP_SHIP_GRP.Order_Line_Info_Rec_Type; --2887790
339 nHeader_Cancel_Flag_pos NUMBER;
340 nLine_Cancel_Flag_pos NUMBER;
341 nShipment_Cancel_Flag_pos NUMBER;
342 v_header_cancel_flag VARCHAR2(10);
343 v_line_cancel_flag VARCHAR2(10);
344 v_shipment_cancel_flag VARCHAR2(10);
345
346 nTrans_code_pos NUMBER; -- 2823215
347
348 c_file_common_key VARCHAR2(255); -- 2823215
349
350 -- Bug 2823215
351
352 nShip_Release_Num_pos NUMBER;
353 nLine_uom_code_pos NUMBER;
354 nLine_Location_uom_pos NUMBER;
355 nLp_att_cat_pos NUMBER;
356 nLp_att1_pos NUMBER;
357 nLp_att2_pos NUMBER;
358 nLp_att3_pos NUMBER;
359 nLp_att4_pos NUMBER;
360 nLp_att5_pos NUMBER;
361 nLp_att6_pos NUMBER;
362 nLp_att7_pos NUMBER;
363 nLp_att8_pos NUMBER;
364 nLp_att9_pos NUMBER;
365 nLp_att10_pos NUMBER;
366 nLp_att11_pos NUMBER;
367 nLp_att12_pos NUMBER;
368 nLp_att13_pos NUMBER;
369 nLp_att14_pos NUMBER;
370 nLp_att15_pos NUMBER;
371 nSt_cust_name_pos NUMBER;
372 nSt_cont_name_pos NUMBER;
373 nSt_cont_phone_pos NUMBER;
374 nSt_cont_fax_pos NUMBER;
375 nSt_cont_email_pos NUMBER;
376 nShipping_Instruct_pos NUMBER;
377 nPacking_Instruct_pos NUMBER;
378 nShipping_method_pos NUMBER;
379 nCust_po_num_pos NUMBER;
380 nCust_po_line_num_pos NUMBER;
381 nCust_po_ship_num_pos NUMBER;
382 nCust_prod_desc_pos NUMBER;
383 nDeliv_cust_loc_pos NUMBER;
384 nDeliv_cust_name_pos NUMBER;
385 nDeliv_cont_name_pos NUMBER;
386 nDeliv_cont_phone_pos NUMBER;
387 nDeliv_cont_fax_pos NUMBER;
388 nDeliv_cust_addr_pos NUMBER;
389 nDeliv_cont_email_pos NUMBER;
390 nHeader_cancel_date_pos NUMBER;
391 --Bug 2823215
392
393 -- Timezone enhancement
394 nRel_date_pos pls_integer;
395 nRel_dt_tz_pos pls_integer;
396 nRel_dt_off_pos pls_integer;
397 nCrtn_date_pos pls_integer;
398 nCrtn_dt_tz_pos pls_integer;
399 nCrtn_dt_off_pos pls_integer;
400 nRev_date_pos pls_integer;
401 nRev_dt_tz_pos pls_integer;
402 nRev_dt_off_pos pls_integer;
403 nAcc_due_dt_pos pls_integer;
404 nAcc_due_tz_pos pls_integer;
405 nAcc_due_off_pos pls_integer;
406 nBlkt_srt_dt_pos pls_integer;
407 nBlkt_srt_tz_pos pls_integer;
408 nBlkt_srt_off_pos pls_integer;
409 nBlkt_end_dt_pos pls_integer;
410 nBlkt_end_tz_pos pls_integer;
411 nBlkt_end_off_pos pls_integer;
412 nPcard_exp_dt_pos pls_integer;
413 nPcard_exp_tz_pos pls_integer;
414 nPcard_exp_off_pos pls_integer;
415 nLine_can_dt_pos pls_integer;
416 nLine_can_tz_pos pls_integer;
417 nLine_can_off_pos pls_integer;
418 nExprn_dt_pos pls_integer;
419 nExprn_tz_pos pls_integer;
420 nExprn_off_pos pls_integer;
421 nShip_need_dt_pos pls_integer;
422 nShip_need_tz_pos pls_integer;
423 nShip_need_off_pos pls_integer;
424 nShip_prom_dt_pos pls_integer;
425 nShip_prom_tz_pos pls_integer;
426 nShip_prom_off_pos pls_integer;
427 nShip_accept_dt_pos pls_integer;
428 nShip_accept_tz_pos pls_integer;
429 nShip_accept_off_pos pls_integer;
430 nShp_can_dt_pos pls_integer;
431 nShp_can_tz_pos pls_integer;
432 nShp_can_off_pos pls_integer;
433 nShp_strt_dt_pos pls_integer;
434 nShp_strt_tz_pos pls_integer;
435 nShp_strt_off_pos pls_integer;
436 nShp_end_dt_pos pls_integer;
437 nShp_end_tz_pos pls_integer;
438 nShp_end_off_pos pls_integer;
439 -- Timezone enhancement
440
441 nShp_uom_pos NUMBER;
442 nLine_uom_pos NUMBER;
443 init_msg_list VARCHAR2(20);
444 simulate VARCHAR2(20);
445 validation_level VARCHAR2(20);
446 commt VARCHAR2(20);
447 return_status VARCHAR2(20);
448 msg_count NUMBER;
449 msg_data VARCHAR2(2000); -- 3650215
450
451 cline_part_number VARCHAR2(80);
452 cline_part_attrib_category VARCHAR2(80);
453
454 -- bug 6511409
455 cline_part_attribute1 MTL_ITEM_FLEXFIELDS.ATTRIBUTE1%TYPE;
456 cline_part_attribute2 MTL_ITEM_FLEXFIELDS.ATTRIBUTE2%TYPE;
457 cline_part_attribute3 MTL_ITEM_FLEXFIELDS.ATTRIBUTE3%TYPE;
458 cline_part_attribute4 MTL_ITEM_FLEXFIELDS.ATTRIBUTE4%TYPE;
459 cline_part_attribute5 MTL_ITEM_FLEXFIELDS.ATTRIBUTE5%TYPE;
460 cline_part_attribute6 MTL_ITEM_FLEXFIELDS.ATTRIBUTE6%TYPE;
461 cline_part_attribute7 MTL_ITEM_FLEXFIELDS.ATTRIBUTE7%TYPE;
462 cline_part_attribute8 MTL_ITEM_FLEXFIELDS.ATTRIBUTE8%TYPE;
463 cline_part_attribute9 MTL_ITEM_FLEXFIELDS.ATTRIBUTE9%TYPE;
464 cline_part_attribute10 MTL_ITEM_FLEXFIELDS.ATTRIBUTE10%TYPE;
465 cline_part_attribute11 MTL_ITEM_FLEXFIELDS.ATTRIBUTE11%TYPE;
466 cline_part_attribute12 MTL_ITEM_FLEXFIELDS.ATTRIBUTE12%TYPE;
467 cline_part_attribute13 MTL_ITEM_FLEXFIELDS.ATTRIBUTE13%TYPE;
468 cline_part_attribute14 MTL_ITEM_FLEXFIELDS.ATTRIBUTE14%TYPE;
469 cline_part_attribute15 MTL_ITEM_FLEXFIELDS.ATTRIBUTE15%TYPE;
470
471 d_dummy_date DATE;
472 counter NUMBER;
473 cancel_flag_value VARCHAR2(1);
474 cancel_date_value DATE;
475 iMap_ID NUMBER;
476 c_header_common_key_name VARCHAR2(40);
477 c_line_common_key_name VARCHAR2(40);
478 c_shipment_key_name VARCHAR2(40);
479 n_header_common_key_pos NUMBER;
480 n_line_common_key_pos NUMBER;
481 n_ship_common_key_pos NUMBER;
482
483 fail_convert_to_ext EXCEPTION;
484
485 CURSOR c_org_id(p_line_id NUMBER) IS
486 SELECT DISTINCT ship_to_organization_id
487 FROM po_line_locations
488 WHERE po_line_id = p_line_id;
489
490 BEGIN
491 ec_debug.push('ECE_POCO_TRANSACTION.POPULATE_POCO_TRX');
492 ec_debug.pl(3,'cCommunication_Method: ',cCommunication_Method);
493 ec_debug.pl(3,'cTransaction_Type: ' ,cTransaction_Type);
494 ec_debug.pl(3,'iOutput_width: ' ,iOutput_width);
495 ec_debug.pl(3,'dTransaction_date: ' ,dTransaction_date);
496 ec_debug.pl(3,'iRun_id: ' ,iRun_id);
497 ec_debug.pl(3,'cHeader_Interface: ' ,cHeader_Interface);
498 ec_debug.pl(3,'cLine_Interface: ' ,cLine_Interface);
499 ec_debug.pl(3,'cShipment_Interface: ' ,cShipment_Interface);
500 ec_debug.pl(3,'cProject_Interface: ' ,cProject_Interface);
501 ec_debug.pl(3,'cRevised_Date_From: ' ,cRevised_Date_From);
502 ec_debug.pl(3,'cRevised_Date_To: ' ,cRevised_Date_To);
503 ec_debug.pl(3,'cSupplier_Name: ' ,cSupplier_Name);
504 ec_debug.pl(3,'cSupplier_Site: ' ,cSupplier_Site);
505 ec_debug.pl(3,'cDocument_Type: ' ,cDocument_Type);
506 ec_debug.pl(3,'cPO_Number_From: ' ,cPO_Number_From);
507 ec_debug.pl(3,'cPO_Number_To: ' ,cPO_Number_To);
508
509 xProgress := 'POCOB-10-1000';
510 BEGIN
511 SELECT inventory_organization_id
512 INTO nOrganization_ID
513 FROM financials_system_parameters;
514
515 EXCEPTION
516 WHEN NO_DATA_FOUND THEN
517 ec_debug.pl(0,
518 'EC',
519 'ECE_NO_ROW_SELECTED',
520 'PROGRESS_LEVEL',
521 xProgress,
522 'INFO',
523 'INVENTORY ORGANIZATION ID',
524 'TABLE_NAME',
525 'FINANCIALS_SYSTERM_PARAMETERS');
526 END;
527 ec_debug.pl(3,'nOrganization_ID: ',nOrganization_ID);
528
529 -- Let's See if Project Accounting is Installed
530 xProgress := 'POCOB-10-1001';
531 v_project_acct_installed := fnd_installation.get_app_info(
532 v_project_acct_short_name, -- i.e. 'PA'
533 v_project_acct_status, -- 'I' means it's installed
534 v_project_acct_industry,
535 v_project_acct_schema);
536
537 v_project_acct_status := NVL(v_project_acct_status,'X');
538 ec_debug.pl(3,'v_project_acct_status: ' ,v_project_acct_status);
539 ec_debug.pl(3,'v_project_acct_industry: ',v_project_acct_industry);
540 ec_debug.pl(3,'v_project_acct_schema: ' ,v_project_acct_schema);
541
542 -- Get Profile Option Values for Attachments
543 xProgress := 'POCOB-10-1002';
544 fnd_profile.get('ECE_' || cTransaction_Type || '_HEAD_ATT',v_header_att_enabled);
545 fnd_profile.get('ECE_' || cTransaction_Type || '_LINE_ATT',v_line_att_enabled);
546 fnd_profile.get('ECE_' || cTransaction_Type || '_MITEM_ATT',v_mitem_att_enabled);
547 fnd_profile.get('ECE_' || cTransaction_Type || '_IITEM_ATT',v_iitem_att_enabled);
548 fnd_profile.get('ECE_' || cTransaction_Type || '_SHIP_ATT',v_ship_att_enabled);
549 fnd_profile.get('ECE_' || cTransaction_Type || '_ATT_SEG_SIZE',n_att_seg_size);
550
551 -- Check to see if any attachments are enabled
552 xProgress := 'POCOB-10-1004';
553 IF NVL(v_header_att_enabled,'N') = 'Y' OR
554 NVL(v_mitem_att_enabled,'N') = 'Y' OR
555 NVL(v_iitem_att_enabled,'N') = 'Y' OR
556 NVL(v_ship_att_enabled,'N') = 'Y' THEN
557 v_att_enabled := 'Y';
558 END IF;
559
560 IF v_att_enabled = 'Y' THEN
561 BEGIN
562 IF n_att_seg_size < 1 OR n_att_seg_size > ece_poo_transaction.G_MAX_ATT_SEG_SIZE OR n_att_seg_size IS NULL THEN
563 RAISE invalid_number;
564 END IF;
565
566 EXCEPTION
567 WHEN value_error OR invalid_number THEN
568 ec_debug.pl(0,'EC','ECE_INVALID_SEGMENT_NUM','SEGMENT_VALUE',n_att_seg_size,'SEGMENT_DEFAULT',ece_poo_transaction.G_DEFAULT_ATT_SEG_SIZE);
569 n_att_seg_size := ece_poo_transaction.G_DEFAULT_ATT_SEG_SIZE;
570 END;
571 END IF;
572
573 ec_debug.pl(3,'v_header_att_enabled: ',v_header_att_enabled);
574 ec_debug.pl(3,'v_line_att_enabled: ' ,v_line_att_enabled);
575 ec_debug.pl(3,'v_mitem_att_enabled: ' ,v_mitem_att_enabled);
576 ec_debug.pl(3,'v_iitem_att_enabled: ' ,v_iitem_att_enabled);
577 ec_debug.pl(3,'v_ship_att_enabled: ' ,v_ship_att_enabled);
578 ec_debug.pl(3,'v_att_enabled: ' ,v_att_enabled);
579 ec_debug.pl(3,'n_att_seg_size: ' ,n_att_seg_size);
580
581 xProgress := 'POCOB-10-1010';
582 ece_flatfile_pvt.init_table(cTransaction_Type,cHeader_Interface,NULL,FALSE,l_header_tbl,l_key_tbl);
583
584 xProgress := 'POCOB-10-1020';
585 l_key_tbl := l_header_tbl;
586
587 xProgress := 'POCOB-10-1025';
588 --iKey_count := l_header_tbl.COUNT;
589 --ec_debug.pl(3,'iKey_count: ',iKey_count );
590
591 xProgress := 'POCOB-10-1030';
592 ece_flatfile_pvt.init_table(cTransaction_Type,cLine_Interface,NULL,TRUE,l_line_tbl,l_key_tbl);
593
594 xProgress := 'POCOB-10-1040';
595 ece_flatfile_pvt.init_table(cTransaction_Type,cShipment_Interface,NULL,TRUE,l_shipment_tbl,l_key_tbl);
596
597 -- ****************************************************************************
598 -- Here, I am building the SELECT, FROM, and WHERE clauses for the dynamic SQL
599 -- call. The ece_extract_utils_pub.select_clause uses the EDI data dictionary
600 -- for the build.
601 -- ****************************************************************************
602 BEGIN
603 SELECT map_id
604 INTO iMap_ID
605 FROM ece_mappings
606 WHERE map_code = 'EC_' || cTransaction_Type || '_FF';
607 EXCEPTION
608 WHEN OTHERS THEN
609 NULL;
610 END;
611 xProgress := 'POCOB-10-1050';
612 ece_extract_utils_pub.select_clause(cTransaction_Type,
613 cCommunication_Method,
614 cHeader_Interface,
615 l_header_tbl,
616 cHeader_select,
617 cHeader_from,
618 cHeader_where);
619 BEGIN
620 SELECT eit.key_column_name
621 INTO c_header_common_key_name
622 FROM ece_interface_tables eit
623 WHERE eit.transaction_type = cTransaction_Type AND
624 eit.interface_table_name = cHeader_Interface AND
625 eit.map_id = iMap_ID;
626 EXCEPTION
627 WHEN OTHERS THEN
628 NULL;
629 END;
630
631 xProgress := 'POCOB-10-1060';
632 ece_extract_utils_pub.select_clause(cTransaction_Type,
633 cCommunication_Method,
634 cLine_Interface,
635 l_line_tbl,
636 cLine_select,
637 cLine_from,
638 cLine_where);
639
640 BEGIN
641 SELECT eit.key_column_name
642 INTO c_line_common_key_name
643 FROM ece_interface_tables eit
644 WHERE eit.transaction_type = cTransaction_Type AND
645 eit.interface_table_name = cLine_Interface AND
646 eit.map_id = iMap_ID;
647 EXCEPTION
648 WHEN OTHERS THEN
649 NULL;
650 END;
651
652 xProgress := 'POCOB-10-1070';
653 ece_extract_utils_pub.select_clause(cTransaction_Type,
654 cCommunication_Method,
655 cShipment_Interface,
656 l_shipment_tbl,
657 cShipment_select,
658 cShipment_from,
659 cShipment_where);
660
661 BEGIN
662 SELECT eit.key_column_name
663 INTO c_shipment_key_name
664 FROM ece_interface_tables eit
665 WHERE eit.transaction_type = cTransaction_Type AND
666 eit.interface_table_name = cShipment_Interface AND
667 eit.map_id = iMap_ID;
668 EXCEPTION
669 WHEN OTHERS THEN
670 NULL;
671 END;
672
673
674 -- **************************************************************************
675 -- Here, I am customizing the WHERE clause to join the Interface tables together.
676 -- i.e. Headers -- Lines -- Line Details
677 --
678 -- Select Data1, Data2, Data3...........
679 -- From Header_View
680 -- Where A.Transaction_Record_ID = D.Transaction_Record_ID (+)
681 -- and B.Transaction_Record_ID = E.Transaction_Record_ID (+)
682 -- and C.Transaction_Record_ID = F.Transaction_Record_ID (+)
683 -- ******* (Customization should be added here) ********
684 -- and A.Communication_Method = 'EDI'
685 -- and A.xxx = B.xxx ........
686 -- and B.yyy = C.yyy .......
687 -- **************************************************************************
688 -- **************************************************************************
689 -- :po_header_id is a place holder for foreign key value.
690 -- A PL/SQL table (list of values) will be used to store data.
691 -- Procedure ece_flatfile.Find_pos will be used to locate the specific
692 -- data value in the PL/SQL table.
693 -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
694 -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
695 --
696 -- Let's use the above example:
697 --
698 -- 1. Execute dynamic SQL 1 for headers (A) data
699 -- Get value of A.xxx (foreign key to B)
700 --
701 -- 2. bind value A.xxx to variable B.xxx
702 --
703 -- 3. Execute dynamic SQL 2 for lines (B) data
704 -- Get value of B.yyy (foreigh key to C)
705 --
706 -- 4. bind value B.yyy to variable C.yyy
707 --
708 -- 5. Execute dynamic SQL 3 for line_details (C) data
709 -- **************************************************************************
710 xProgress := 'POCOB-10-1080';
711 cHeader_where := cHeader_where ||
712 ' communication_method = ' || ':cComm_Method';
713
714 xProgress := 'POCOB-10-1090';
715 IF cRevised_Date_From IS NOT NULL THEN
716 cHeader_where := cHeader_where || ' AND ' ||
717 ' revised_date >= ' || ':cRevised_Dt_From';
718 END IF;
719
720 xProgress := 'POCOB-10-1100';
721 IF cRevised_Date_To IS NOT NULL THEN
722 cHeader_where := cHeader_where || ' AND ' ||
723 ' revised_date <= ' || ':cRevised_Dt_To';
724 END IF;
725
726 xProgress := 'POCOB-10-1110';
727 IF cSupplier_Name IS NOT NULL THEN
728 cHeader_where := cHeader_where || ' AND ' ||
729 ' supplier_number = ' || ':cSuppl_Name';
730 END IF;
731
732 xProgress := 'POCOB-10-1120';
733 IF cSupplier_Site IS NOT NULL THEN
734 cHeader_where := cHeader_where || ' AND ' ||
735 ' vendor_site_id = ' || ':cSuppl_Site';
736 END IF;
737
738 xProgress := 'POCOB-10-1130';
739 IF cDocument_Type IS NOT NULL THEN
740 cHeader_where := cHeader_where || ' AND ' ||
741 ' document_type = ' || ':cDoc_Type';
742 END IF;
743
744 xProgress := 'POCOB-10-1140';
745 IF cPO_Number_From IS NOT NULL THEN
746 cHeader_where := cHeader_where || ' AND ' ||
747 ' po_number >= ' || ':cPO_Num_From';
748 END IF;
749
750 xProgress := 'POCOB-10-1150';
751 IF cPO_Number_To IS NOT NULL THEN
752 cHeader_where := cHeader_where || ' AND ' ||
753 ' po_number <= ' || ':cPO_Num_To';
754 END IF;
755
756 xProgress := 'POCOB-10-1160';
757 cHeader_where := cHeader_where ||
758 ' ORDER BY po_number, por_release_num';
759
760 xProgress := 'POCOB-10-1170';
761 cLine_where := cLine_where ||
762 ' ece_poco_lines_v.po_header_id = :po_header_id AND' ||
763 ' ece_poco_lines_v.por_release_num = :por_release_num ' ||
764 ' ORDER BY line_num';
765
766 xProgress := 'POCOB-10-1180';
767 cShipment_where := cShipment_where ||
768 ' ece_poco_shipments_v.po_header_id = :po_header_id AND' ||
769 ' ece_poco_shipments_v.po_line_id = :po_line_id AND' ||
770 ' ece_poco_shipments_v.por_release_id = :por_release_id' ||
771 --4645680 ' ece_poo_shipments_v.por_release_id = :por_release_id' ||
772 ' ORDER BY shipment_number'; --2823215
773 -- 3957851
774 -- ' ece_poco_shipments_v.por_release_id = :por_release_id AND' ||
775 -- ' ((ece_poco_shipments_v.por_release_id = 0) OR' ||
776 -- ' (ece_poco_shipments_v.por_release_id <> 0 AND' ||
777 -- ' ece_poco_shipments_v.shipment_number = :shipment_number))' ||
778 -- ' ORDER BY shipment_number'; --2823215
779
780 xProgress := 'POCOB-10-1190';
781 cHeader_select := cHeader_select ||
782 cHeader_from ||
783 cHeader_where;
784 ec_debug.pl(3,'cHeader_select: ',cHeader_select);
785
786 cLine_select := cLine_select ||
787 cLine_from ||
788 cLine_where;
789 ec_debug.pl(3,'cLine_select: ',cLine_select);
790
791 cShipment_select := cShipment_select ||
792 cShipment_from ||
793 cShipment_where;
794 ec_debug.pl(3,'cShipment_select: ',cShipment_select);
795
796 -- ***************************************************
797 -- *** Get data setup for the dynamic SQL call.
798 -- *** Open a cursor for each of the SELECT call
799 -- *** This tells the database to reserve spaces
800 -- *** for the data returned by the SQL statement
801 -- ***************************************************
802 xProgress := 'POCOB-10-1200';
803 header_sel_c := dbms_sql.open_cursor;
804
805 xProgress := 'POCOB-10-1210';
806 line_sel_c := dbms_sql.open_cursor;
807
808 xProgress := 'POCOB-10-1220';
809 shipment_sel_c := dbms_sql.open_cursor;
810
811 -- ***************************************************
812 -- Parse each of the SELECT statement
813 -- so the database understands the command
814 -- ***************************************************
815 xProgress := 'POCOB-10-1230';
816 BEGIN
817 dbms_sql.parse(header_sel_c,cHeader_select,dbms_sql.native);
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
822 app_exception.raise_exception;
823 END;
824
825 xProgress := 'POCOB-10-1240';
826 BEGIN
827 dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
828
829 EXCEPTION
830 WHEN OTHERS THEN
831 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
832 app_exception.raise_exception;
833 END;
834
835 xProgress := 'POCOB-10-1250';
836 BEGIN
837 dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
842 app_exception.raise_exception;
843 END;
844
845 -- ************
846 -- set counter
847 -- ************
848 xProgress := 'POCOB-10-1260';
849 iHeader_count := l_header_tbl.COUNT;
850 ec_debug.pl(3,'iHeader_count: ',iHeader_count);
851
852 xProgress := 'POCOB-10-1270';
853 iLine_count := l_line_tbl.COUNT;
854 ec_debug.pl(3,'iLine_count: ',iLine_count);
855
856 xProgress := 'POCOB-10-1280';
857 iShipment_count := l_shipment_tbl.COUNT;
858 ec_debug.pl(3,'iShipment_count: ',iShipment_count);
859
860 -- ******************************************************
861 -- Define TYPE for every columns in the SELECT statement
862 -- For each piece of the data returns, we need to tell
863 -- the database what type of information it will be.
864 -- e.g. ID is NUMBER, due_date is DATE
865 -- However, for simplicity, we will convert
866 -- everything to varchar2.
867 -- ******************************************************
868 xProgress := 'POCOB-10-1290';
869 ece_flatfile_pvt.define_interface_column_type(header_sel_c,cHeader_select,ece_extract_utils_PUB.G_MaxColWidth,l_header_tbl);
870
871 xProgress := 'POCOB-10-1300';
872 ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_extract_utils_PUB.G_MaxColWidth,l_line_tbl);
873
874 xProgress := 'POCOB-10-1310';
875 ece_flatfile_pvt.define_interface_column_type(shipment_sel_c,cShipment_select,ece_extract_utils_PUB.G_MaxColWidth,l_shipment_tbl);
876
877 -- **************************************************************
878 -- *** The following is custom tailored for this transaction
879 -- *** It finds the values and use them in the WHERE clause to
880 -- *** join tables together.
881 -- **************************************************************
882 -- ***************************************************
883 -- To complete the Line SELECT statement,
884 -- we will need values for the join condition.
885 -- ***************************************************
886 -- Header Level Positions
887 xProgress := 'POCOB-10-1320';
888 ece_extract_utils_pub.find_pos(l_header_tbl,ece_extract_utils_pub.G_TRANSACTION_DATE,n_trx_date_pos);
889 ec_debug.pl(3,'n_trx_date_pos: ',n_trx_date_pos);
890
891 xProgress := 'POCOB-10-1330';
892 ece_extract_utils_pub.find_pos(l_header_tbl,'PO_HEADER_ID',nHeader_key_pos);
893 ec_debug.pl(3,'nHeader_key_pos: ',nHeader_key_pos);
894
895 xProgress := 'POCOB-10-1340';
896 ece_extract_utils_pub.find_pos(l_header_tbl,'DOCUMENT_TYPE',nDocument_type_pos);
897 ec_debug.pl(3,'nDocument_type_pos: ',nDocument_type_pos);
898
899 xProgress := 'POCOB-10-1350';
900 ece_extract_utils_pub.find_pos(l_header_tbl,'PO_NUMBER',nPO_Number_pos);
901 ec_debug.pl(3,'nPO_Number_pos: ',nPO_Number_pos);
902
903 xProgress := 'POCOB-10-1360';
904 ece_extract_utils_pub.find_pos(l_header_tbl,'PO_TYPE',nPO_Type_pos);
905 ec_debug.pl(3,'nPO_Type_pos: ',nPO_Type_pos);
906
907 xProgress := 'POCOB-10-1370';
908 ece_extract_utils_pub.find_pos(l_header_tbl,'POR_RELEASE_NUM',nRelease_num_pos);
909 ec_debug.pl(3,'nRelease_num_pos: ',nRelease_num_pos);
910
911 xProgress := 'POCOB-10-1380';
912 ece_extract_utils_pub.find_pos(l_header_tbl,'POR_RELEASE_ID',nRelease_id_pos);
913 ec_debug.pl(3,'nRelease_id_pos: ',nRelease_id_pos);
914
915 xProgress := 'POCOB-10-1381';
916 ece_extract_utils_pub.find_pos(l_header_tbl,'CANCEL_FLAG',nHeader_Cancel_Flag_pos);
917
918 xProgress := 'POOB-10-1382';
919 ece_flatfile_pvt.find_pos(l_header_tbl,ece_flatfile_pvt.G_Translator_Code,nTrans_code_pos); --2823215
920
921 XProgress := 'POCOB-10-1283';
922 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_CANCELLED_DATE',nHeader_cancel_date_pos); --2823215
923
924 -- Line Level Positions
925 xProgress := 'POCOB-10-1390';
926 ece_extract_utils_pub.find_pos(l_line_tbl,'PO_LINE_LOCATION_ID',nLine_Location_ID_pos);
927 ec_debug.pl(3,'nLine_Location_ID_pos: ',nLine_Location_ID_pos);
928
929 xProgress := 'POCOB-10-1400';
930 ece_extract_utils_pub.find_pos(l_line_tbl,'LINE_NUM',nLine_num_pos);
931 ec_debug.pl(3,'nLine_num_pos: ',nLine_num_pos);
932
933 xProgress := 'POCOB-10-1402';
934 ece_extract_utils_pub.find_pos(l_line_tbl,'PO_LINE_ID',nLine_key_pos);
935 ec_debug.pl(3,'nLine_key_pos: ',nLine_key_pos);
936
937 xProgress := 'POCOB-10-1404';
938 ece_extract_utils_pub.find_pos(l_line_tbl,'ITEM_ID',nItem_id_pos);
939 ec_debug.pl(3,'nItem_id_pos: ',nItem_id_pos);
940
941 xProgress := 'POCOB-10-1405';
942 ece_extract_utils_pub.find_pos(l_line_tbl,'CANCEL_FLAG',nLine_Cancel_Flag_pos);
943
944 xProgress := 'POCOB-10-1406';
945 ece_extract_utils_pub.find_pos(l_line_tbl,'UOM_CODE',nLine_uom_code_pos);
946
947 xProgress := 'POCOB-10-1407';
948 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE_CATEGORY',nLp_att_cat_pos);
949
950 xProgress := 'POCOB-10-1408';
951 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE1',nLp_att1_pos);
952
953 xProgress := 'POCOB-10-1409';
954 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE2',nLp_att2_pos);
955
956 xProgress := 'POCOB-10-1410';
957 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE3',nLp_att3_pos);
958
959 xProgress := 'POCOB-10-1411';
960 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE4',nLp_att4_pos);
961
962 xProgress := 'POCOB-10-1412';
963 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE5',nLp_att5_pos);
964
965 xProgress := 'POCOB-10-1413';
966 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE6',nLp_att6_pos);
967
968 xProgress := 'POCOB-10-1414';
969 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE7',nLp_att7_pos);
970
971 xProgress := 'POCOB-10-1415';
972 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE8',nLp_att8_pos);
973
974 xProgress := 'POCOB-10-1416';
975 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE9',nLp_att9_pos);
976
977 xProgress := 'POCOB-10-1417';
978 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE10',nLp_att10_pos);
979
980 xProgress := 'POCOB-10-1418';
981 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE11',nLp_att11_pos);
982
983 xProgress := 'POCOB-10-1419';
984 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE12',nLp_att12_pos);
985
986 xProgress := 'POCOB-10-1420';
987 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE13',nLp_att13_pos);
988
989 xProgress := 'POCOB-10-1421';
990 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE14',nLp_att14_pos);
991
992 xProgress := 'POCOB-10-1422';
993 ece_extract_utils_pub.find_pos(l_line_tbl,'LP_ATTRIBUTE15',nLp_att15_pos);
994
995
996 -- Shipment Level Positions
997 xProgress := 'POCOB-10-1406';
998 ece_extract_utils_pub.find_pos(l_shipment_tbl,'LINE_LOCATION_ID',nShip_Line_Location_ID_pos);
999 ec_debug.pl(3,'nShip_Line_Location_ID_pos: ',nShip_Line_Location_ID_pos);
1000
1001 xProgress := 'POCOB-10-1407';
1002 ece_extract_utils_pub.find_pos(l_Shipment_tbl,'QUANTITY_PENDING',nQuantity_pending_pos);
1003
1004 xProgress := 'POCOB-10-1408';
1005 ece_extract_utils_pub.find_pos(l_shipment_tbl,'CANCELLED_FLAG',nShipment_Cancel_Flag_pos);
1006
1007 --2823215
1008
1009 xProgress := 'POOB-10-1025';
1010 ece_extract_utils_pub.find_pos(l_shipment_tbl,'POR_RELEASE_NUM',nShip_Release_Num_pos);
1011
1012 xProgress := 'POOB-10-1026';
1013 ece_extract_utils_pub.find_pos(l_shipment_tbl,'UOM_CODE',nLine_Location_uom_pos);
1014
1015 xProgress := 'POOB-10-1427';
1016 ece_extract_utils_pub.find_pos(l_shipment_tbl,'SHIPMENT_NUMBER',nShipment_key_pos);
1017
1018 xProgress := 'POOB-10-1428';
1019 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CUSTOMER_NAME',nSt_cust_name_pos);
1020
1021 xProgress := 'POOB-10-1429';
1022 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_NAME',nSt_cont_name_pos);
1023
1024 xProgress := 'POOB-10-1430';
1025 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_PHONE',nSt_cont_phone_pos);
1026
1027 xProgress := 'POOB-10-1431';
1028 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_FAX',nSt_cont_fax_pos);
1029
1030 xProgress := 'POOB-10-1432';
1031 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIP_TO_CONTACT_EMAIL',nSt_cont_email_pos);
1032
1033 xProgress := 'POOB-10-1433';
1034 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPPING_INSTRUCTIONS',nShipping_Instruct_pos);
1035
1036 xProgress := 'POOB-10-1434';
1037 ece_flatfile_pvt.find_pos(l_shipment_tbl,'PACKING_INSTRUCTIONS',nPacking_Instruct_pos);
1038
1039 xProgress := 'POOB-10-1435';
1040 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPPING_METHOD',nShipping_method_pos);
1041
1042 xProgress := 'POOB-10-1437';
1043 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_PO_NUMBER',nCust_po_num_pos);
1044
1045 xProgress := 'POOB-10-1438';
1046 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_PO_LINE_NUM',nCust_po_line_num_pos);
1047
1048 xProgress := 'POOB-10-1439';
1049 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_PO_SHIPMENT_NUM',nCust_po_ship_num_pos);
1050
1051 xProgress := 'POOB-10-1440';
1052 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CUSTOMER_ITEM_DESCRIPTION',nCust_prod_desc_pos);
1053
1054 xProgress := 'POOB-10-1441';
1055 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_LOCATION',nDeliv_cust_loc_pos);
1056
1057 xProgress := 'POOB-10-1442';
1058 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CUSTOMER_NAME',nDeliv_cust_name_pos);
1059
1060 xProgress := 'POOB-10-1443';
1061 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_NAME',nDeliv_cont_name_pos);
1062
1063 xProgress := 'POOB-10-1444';
1064 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_PHONE',nDeliv_cont_phone_pos);
1065
1066 xProgress := 'POOB-10-1445';
1067 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_FAX',nDeliv_cont_fax_pos);
1068
1069 xProgress := 'POOB-10-1446';
1070 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CUSTOMER_ADDRESS',nDeliv_cust_addr_pos);
1071
1072 xProgress := 'POOB-10-1447';
1073 ece_flatfile_pvt.find_pos(l_shipment_tbl,'DELIVER_TO_CONTACT_EMAIL',nDeliv_cont_email_pos);
1074
1075 -- 2823215
1076 xProgress := 'POCOB-10-1448';
1077 ece_flatfile_pvt.find_pos(l_line_tbl,'UOM_CODE',nLine_uom_pos);
1078
1079 xProgress := 'POCOB-10-1449';
1080 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CODE_UOM',nShp_uom_pos);
1081 -- 2412921 begin
1082
1083 xProgress := 'POCOB-10-1410';
1084 ece_extract_utils_pub.find_pos(l_header_tbl,'CANCEL_DATE',nCancel_Date_pos);
1085 ec_debug.pl(3,'nCancel_Date_pos -> ', nCancel_Date_pos);
1086
1087 xProgress := 'POCOB-10-1412';
1088 ece_extract_utils_pub.find_pos(l_line_tbl,'CANCEL_DATE',nCancel_Date_posl);
1089 ec_debug.pl(3,'nCancel_Date_posl -> ', nCancel_Date_posl);
1090 -- 2412921 end
1091
1092
1093 -- Timezone enhancement
1094 xProgress := 'POCOB-TZ-1000';
1095 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_DATE', nRel_date_pos);
1096
1097 xProgress := 'POCOB-TZ-1001';
1098 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_DT_TZ_CODE',nRel_dt_tz_pos);
1099
1100 xProgress := 'POCOB-TZ-1002';
1101 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_DT_OFF',nRel_dt_off_pos);
1102
1103 xProgress := 'POCOB-TZ-1003';
1104 ece_flatfile_pvt.find_pos(l_header_tbl,'CREATION_DATE',nCrtn_date_pos);
1105
1106 xProgress := 'POCOB-TZ-1004';
1107 ece_flatfile_pvt.find_pos(l_header_tbl,'CREATION_DT_TZ_CODE',nCrtn_dt_tz_pos);
1108
1109 xProgress := 'POCOB-TZ-1005';
1110 ece_flatfile_pvt.find_pos(l_header_tbl,'CREATION_DT_OFF',nCrtn_dt_off_pos);
1111
1112 xProgress := 'POCOB-TZ-1006';
1113 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_REVISION_DATE',nRev_date_pos);
1114
1115 xProgress := 'POCOB-TZ-1007';
1116 ece_flatfile_pvt.find_pos(l_header_tbl,'REVISION_DT_TZ_CODE',nRev_dt_tz_pos);
1117
1118 xProgress := 'POCOB-TZ-1008';
1119 ece_flatfile_pvt.find_pos(l_header_tbl,'REVISION_DT_OFF',nRev_dt_off_pos);
1120
1121 xProgress := 'POCOB-TZ-1009';
1122 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_ACCEPTANCE_DUE_BY_DATE',nAcc_due_dt_pos);
1123
1124 xProgress := 'POCOB-TZ-1010';
1125 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_ACCEPT_DUE_TZ_CODE',nAcc_due_tz_pos);
1126
1127 xProgress := 'POCOB-TZ-1011';
1128 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_ACCEPT_DUE_OFF',nAcc_due_off_pos);
1129
1130 xProgress := 'POCOB-TZ-1012';
1131 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_START_DATE',nBlkt_srt_dt_pos);
1132
1133 xProgress := 'POCOB-TZ-1013';
1134 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_SRT_DT_TZ_CODE',nBlkt_srt_tz_pos);
1135
1136 xProgress := 'POCOB-TZ-1014';
1137 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_SRT_DT_OFF',nBlkt_srt_off_pos);
1138
1139 xProgress := 'POCOB-TZ-1015';
1140 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_END_DATE',nBlkt_end_dt_pos);
1141
1142 xProgress := 'POCOB-TZ-1016';
1143 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_END_DT_TZ_CODE',nBlkt_end_tz_pos);
1144
1145 xProgress := 'POCOB-TZ-1017';
1146 ece_flatfile_pvt.find_pos(l_header_tbl,'BLANKET_END_DT_OFF',nBlkt_end_off_pos);
1147
1148 xProgress := 'POCOB-TZ-1018';
1149 ece_flatfile_pvt.find_pos(l_header_tbl,'PCARD_EXPIRATION_DATE',nPcard_exp_dt_pos);
1150
1151 xProgress := 'POCOB-TZ-1019';
1152 ece_flatfile_pvt.find_pos(l_header_tbl,'PCARD_EXPRN_DT_TZ_CODE',nPcard_exp_tz_pos);
1153
1154 xProgress := 'POCOB-TZ-1020';
1155 ece_flatfile_pvt.find_pos(l_header_tbl,'PCARD_EXPRN_DT_OFF',nPcard_exp_off_pos);
1156
1157
1158 xProgress := 'POCOB-TZ-1021';
1159 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_CANCELLED_DATE',nLine_can_dt_pos);
1160
1161 xProgress := 'POCOB-TZ-1022';
1162 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_CANCEL_DT_TZ_CODE',nLine_can_tz_pos);
1163
1164 xProgress := 'POCOB-TZ-1023';
1165 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_CANCEL_DT_OFF',nLine_can_off_pos);
1166
1167 xProgress := 'POCOB-TZ-1024';
1168 ece_flatfile_pvt.find_pos(l_line_tbl,'EXPIRATION_DATE',nExprn_dt_pos);
1169
1170 xProgress := 'POCOB-TZ-1025';
1171 ece_flatfile_pvt.find_pos(l_line_tbl,'EXPIRATION_DT_TZ_CODE',nExprn_tz_pos);
1172
1173 xProgress := 'POCOB-TZ-1026';
1174 ece_flatfile_pvt.find_pos(l_line_tbl,'EXPIRATION_DT_OFF',nExprn_off_pos);
1175
1176 xProgress := 'POCOB-TZ-1027';
1177 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_NEED_BY_DATE',nShip_need_dt_pos);
1178
1179 xProgress := 'POCOB-TZ-1028';
1180 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_NEED_DT_TZ_CODE',nShip_need_tz_pos);
1181
1182 xProgress := 'POCOB-TZ-1029';
1183 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_NEED_DT_OFF',nShip_need_off_pos);
1184
1185 xProgress := 'POCOB-TZ-1030';
1186 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_PROMISED_DATE',nShip_prom_dt_pos);
1187
1188 xProgress := 'POCOB-TZ-1031';
1189 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_PROM_DT_TZ_CODE',nShip_prom_tz_pos);
1190
1191 xProgress := 'POCOB-TZ-1032';
1192 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_PROM_DT_OFF',nShip_prom_off_pos);
1193
1194 xProgress := 'POCOB-TZ-1033';
1195 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_LAST_ACCEPTABLE_DATE',nShip_accept_dt_pos);
1196
1197 xProgress := 'POCOB-TZ-1034';
1198 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_LAST_ACC_DT_TZ_CODE',nShip_accept_tz_pos);
1199
1200 xProgress := 'POCOB-TZ-1035';
1201 ece_flatfile_pvt.find_pos(l_shipment_tbl,'SHIPMENT_LAST_ACC_DT_OFF',nShip_accept_off_pos);
1202
1203 xProgress := 'POCOB-TZ-1036';
1204 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CANCELLED_DATE',nShp_can_dt_pos);
1205
1206 xProgress := 'POCOB-TZ-1037';
1207 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CANCEL_DT_TZ_CODE',nShp_can_tz_pos);
1208
1209 xProgress := 'POCOB-TZ-1038';
1210 ece_flatfile_pvt.find_pos(l_shipment_tbl,'CANCEL_DT_OFF',nShp_can_off_pos);
1211
1212 xProgress := 'POCOB-TZ-1039';
1213 ece_flatfile_pvt.find_pos(l_shipment_tbl,'START_DATE',nShp_strt_dt_pos);
1214
1215 xProgress := 'POCOB-TZ-1040';
1216 ece_flatfile_pvt.find_pos(l_shipment_tbl,'START_DT_TZ_CODE',nShp_strt_tz_pos);
1217
1218 xProgress := 'POCOB-TZ-1041';
1219 ece_flatfile_pvt.find_pos(l_shipment_tbl,'START_DT_OFF',nShp_strt_off_pos);
1220
1221 xProgress := 'POCOB-TZ-1042';
1222 ece_flatfile_pvt.find_pos(l_shipment_tbl,'END_DATE',nShp_end_dt_pos);
1223
1224 xProgress := 'POCOB-TZ-1043';
1225 ece_flatfile_pvt.find_pos(l_shipment_tbl,'END_DT_TZ_CODE',nShp_end_tz_pos);
1226
1227 xProgress := 'POCOB-TZ-1044';
1228 ece_flatfile_pvt.find_pos(l_shipment_tbl,'END_DT_OFF',nShp_end_off_pos);
1229 -- Timezone enhancement
1230 xProgress := 'POCOB-09-1400';
1231 ece_flatfile_pvt.find_pos(l_header_tbl,c_header_common_key_name,n_header_common_key_pos);
1232
1233 xProgress := 'POCOB-09-1401';
1234 ece_flatfile_pvt.find_pos(l_line_tbl,c_line_common_key_name,n_line_common_key_pos);
1235
1236 xProgress := 'POCOB-09-1402';
1237 ece_flatfile_pvt.find_pos(l_shipment_tbl,c_shipment_key_name,n_ship_common_key_pos);
1238
1239 xProgress := 'POCOB-10-1413';
1240 dbms_sql.bind_variable(header_sel_c,'cComm_Method',cCommunication_Method);
1241 IF cRevised_Date_From IS NOT NULL THEN
1242 dbms_sql.bind_variable(header_sel_c,'cRevised_Dt_From',cRevised_Date_From);
1243 END IF;
1244
1245 IF cRevised_Date_To IS NOT NULL THEN
1246 dbms_sql.bind_variable(header_sel_c,'cRevised_Dt_To',cRevised_Date_To);
1247 END IF;
1248
1249 IF cSupplier_Name IS NOT NULL THEN
1250 dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1251 END IF;
1252
1253 IF cSupplier_Site IS NOT NULL THEN
1254 dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1255 END IF;
1256
1257 IF cDocument_Type IS NOT NULL THEN
1258 dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1259 END IF;
1260
1261 IF cPO_Number_From IS NOT NULL THEN
1262 dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1263 END IF;
1264
1265 IF cPO_Number_To IS NOT NULL THEN
1266 dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1267 END IF;
1268
1269 -- EXECUTE the SELECT statement
1270 xProgress := 'POCOB-10-1414';
1271 dummy := dbms_sql.execute(header_sel_c);
1272
1273 -- ***************************************************
1274 -- The model is:
1275 -- HEADER - LINE - SHIPMENT ...
1276 -- With data for each HEADER line, populate the header
1277 -- interfacetable then get all LINES that belongs
1278 -- to the HEADER. Then get all
1279 -- SHIPMENTS that belongs to the LINE.
1280 -- ***************************************************
1281 xProgress := 'POCOB-10-1410';
1282 WHILE dbms_sql.fetch_rows(header_sel_c) > 0 LOOP -- Header
1283
1284 if (NOT utl_file.is_open(ece_poo_transaction.uFile_type)) then
1285 ece_poo_transaction.uFile_type := utl_file.fopen(i_path,i_filename,'W',32767);
1286 end if;
1287 counter := 0;
1288 -- **************************************
1289 -- store internal values in pl/sql table
1290 -- **************************************
1291 xProgress := 'POCOB-10-1420';
1292 ece_flatfile_pvt.assign_column_value_to_tbl(header_sel_c,0,l_header_tbl,l_key_tbl);
1293
1294 -- ***************************************************
1295 -- also need to populate transaction_date and run_id
1296 -- ***************************************************
1297 xProgress := 'POCOB-10-1430';
1298 l_header_tbl(n_trx_date_pos).value := TO_CHAR(dTransaction_date,'YYYYMMDD HH24MISS');
1299
1300 -- The application specific feedback logic begins here.
1301 xProgress := 'POCOB-10-1440';
1302 BEGIN
1303 /* Bug 2396394 Added the document type CONTRACT in SQL below */
1304
1305 SELECT DECODE(l_header_tbl(nDocument_type_pos).value,
1306 'BLANKET' ,'NB',
1307 'STANDARD' ,'NS',
1308 'PLANNED' ,'NP',
1309 'RELEASE' ,'NR',
1310 'BLANKET RELEASE' ,'NR',
1311 'CONTRACT' ,'NC',
1312 'NR')
1313 INTO l_document_type
1314 FROM DUAL;
1315
1316 EXCEPTION
1317 WHEN NO_DATA_FOUND THEN
1318 ec_debug.pl(0,
1319 'EC',
1320 'ECE_DECODE_FAILED',
1321 'PROGRESS_LEVEL',
1322 xProgress,
1323 'CODE',
1324 l_header_tbl(nDocument_type_pos).value);
1325 END;
1326 ec_debug.pl(3, 'l_document_type: ',l_document_type);
1327
1328 xProgress := 'POCOB-10-1450';
1329 ece_poo_transaction.update_po(l_document_type,
1330 l_header_tbl(nPO_Number_pos).value,
1331 l_header_tbl(nPO_type_pos).value,
1332 l_header_tbl(nRelease_num_pos).value);
1333 xProgress := 'POCOB-TZ-1500';
1334 ece_timezone_api.get_server_timezone_details(
1335 to_date(l_header_tbl(nRel_date_pos).value,'YYYYMMDD HH24MISS'),
1336 l_header_tbl(nRel_dt_off_pos).value,
1337 l_header_tbl(nRel_dt_tz_pos).value
1338 );
1339
1340 xProgress := 'POCOB-TZ-1510';
1341
1342 ece_timezone_api.get_server_timezone_details
1343 (
1344 to_date(l_header_tbl(nCrtn_date_pos).value,'YYYYMMDD HH24MISS'),
1345 l_header_tbl(nCrtn_dt_off_pos).value,
1346 l_header_tbl(nCrtn_dt_tz_pos).value
1347 );
1348
1349 xProgress := 'POCOB-TZ-1520';
1350
1351 ece_timezone_api.get_server_timezone_details
1352 (
1353 to_date(l_header_tbl(nRev_date_pos).value,'YYYYMMDD HH24MISS'),
1354 l_header_tbl(nRev_dt_off_pos).value,
1355 l_header_tbl(nRev_dt_tz_pos).value
1356 );
1357
1358 xProgress := 'POCOB-TZ-1530';
1359 ece_timezone_api.get_server_timezone_details
1360 (
1361 to_date(l_header_tbl(nAcc_due_dt_pos).value,'YYYYMMDD HH24MISS'),
1362 l_header_tbl(nAcc_due_off_pos).value,
1363 l_header_tbl(nAcc_due_tz_pos).value
1364 );
1365
1366 xProgress := 'POCOB-TZ-1540';
1367 ece_timezone_api.get_server_timezone_details
1368 (
1369 to_date(l_header_tbl(nBlkt_srt_dt_pos).value,'YYYYMMDD HH24MISS'),
1370 l_header_tbl(nBlkt_srt_off_pos).value,
1371 l_header_tbl(nBlkt_srt_tz_pos).value
1372 );
1373
1374 xProgress := 'POCOB-TZ-1550';
1375 ece_timezone_api.get_server_timezone_details
1376 (
1377 to_date(l_header_tbl(nBlkt_end_dt_pos).value,'YYYYMMDD HH24MISS'),
1378 l_header_tbl(nBlkt_end_off_pos).value,
1379 l_header_tbl(nBlkt_end_tz_pos).value
1380 );
1381
1382 xProgress := 'POCOB-TZ-1560';
1383 ece_timezone_api.get_server_timezone_details
1384 (
1385 to_date(l_header_tbl(nPcard_exp_dt_pos).value,'YYYYMMDD HH24MISS'),
1386 l_header_tbl(nPcard_exp_off_pos).value,
1387 l_header_tbl(nPcard_exp_tz_pos).value
1388 );
1389
1390
1391 -- pass the pl/sql table in for xref
1392 xProgress := 'POCOB-10-1460';
1393 ec_code_conversion_pvt.populate_plsql_tbl_with_extval(p_api_version_number => 1.0,
1394 p_init_msg_list => init_msg_list,
1395 p_simulate => simulate,
1396 p_commit => commt,
1397 p_validation_level => validation_level,
1398 p_return_status => return_status,
1399 p_msg_count => msg_count,
1400 p_msg_data => msg_data,
1401 p_key_tbl => l_key_tbl,
1402 p_tbl => l_header_tbl);
1403
1404 -- ***************************
1405 -- insert into interface table
1406 -- ***************************
1407 xProgress := 'POCOB-10-1480';
1408 BEGIN
1409 SELECT ece_poco_header_s.NEXTVAL
1410 INTO l_header_fkey
1411 FROM DUAL;
1412
1413 EXCEPTION
1414 WHEN NO_DATA_FOUND THEN
1415 ec_debug.pl(0,
1416 'EC',
1417 'ECE_GET_NEXT_SEQ_FAILED',
1418 'PROGRESS_LEVEL',
1419 xProgress,
1420 'SEQ',
1421 'ECE_POCO_HEADER_S');
1422
1423 END;
1424 ec_debug.pl(3,'l_header_fkey: ',l_header_fkey);
1425
1426 xProgress := 'POOB-10-1490';
1427 --2823215
1428 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),1,25),25);
1429
1430 xProgress := 'POOB-10-1491';
1431 c_file_common_key := c_file_common_key ||
1432 RPAD(SUBSTRB(NVL(l_header_tbl(n_header_common_key_pos).value,' '),1,22),22) || RPAD(' ',22) || RPAD(' ',22);
1433
1434 ec_debug.pl(3, 'c_file_common_key: ',c_file_common_key);
1435 /* ece_extract_utils_pub.insert_into_interface_tbl(
1436 iRun_id,
1437 cTransaction_Type,
1438 cCommunication_Method,
1439 cHeader_Interface,
1440 l_header_tbl,
1441 l_header_fkey); */
1442
1443 -- Now update the columns values of which have been obtained
1444 -- thru the procedure calls.
1445
1446 -- ********************************************************
1447 -- Call custom program stub to populate the extension table
1448 -- ********************************************************
1449 xProgress := 'POCOB-10-1500';
1450 ece_poco_x.populate_ext_header(l_header_fkey,l_header_tbl);
1451
1452 -- 2823215
1453 ece_poo_transaction.write_to_file(cTransaction_Type,
1454 cCommunication_Method,
1455 cHeader_Interface,
1456 l_header_tbl,
1457 iOutput_width,
1458 iRun_id,
1459 c_file_common_key,
1460 l_header_fkey);
1461 -- 2823215
1462 -- Header Level Attachment Handler
1463 xProgress := 'POCOB-10-1501';
1464 IF v_header_att_enabled = 'Y' THEN
1465 xProgress := 'POCOB-10-1502';
1466 IF l_document_type = 'NR' THEN -- If this is a Release PO.
1467 xProgress := 'POCOB-10-1503';
1468 v_entity_name := 'PO_RELEASES';
1469 v_pk1_value := l_header_tbl(nRelease_id_pos).value;
1470 ec_debug.pl(3,'release_id: ',l_header_tbl(nRelease_id_pos).value);
1471 ELSE -- If this is a non-Release PO.
1472 xProgress := 'POCOB-10-1504';
1473 v_entity_name := 'PO_HEADERS';
1474 v_pk1_value := l_header_tbl(nHeader_key_pos).value;
1475 ec_debug.pl(3,'po_header_id: ',l_header_tbl(nHeader_key_pos).value);
1476 END IF;
1477
1478 xProgress := 'POCOB-10-1505';
1479 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1480 cTransaction_Type,
1481 iRun_id,
1482 2,
1483 3,
1484 cAtt_Header_Interface,
1485 cAtt_Detail_Interface,
1486 v_entity_name,
1487 'VENDOR',
1488 v_pk1_value,
1489 ECE_POO_TRANSACTION.C_ANY_VALUE, -- BUG:5367903
1490 ECE_POO_TRANSACTION.C_ANY_VALUE,
1491 ECE_POO_TRANSACTION.C_ANY_VALUE,
1492 ECE_POO_TRANSACTION.C_ANY_VALUE,
1493 n_att_seg_size,
1494 l_key_tbl,
1495 c_file_common_key,
1496 l_hdr_att_hdr_tbl,
1497 l_hdr_att_dtl_tbl,
1498 iAtt_hdr_pos); -- 2823215
1499 END IF;
1500
1501 -- ***************************************************
1502 -- From Header data, we can assign values to
1503 -- place holders (foreign keys) in Line_select and
1504 -- Line_detail_Select
1505 -- set values into binding variables
1506 -- ***************************************************
1507
1508 -- use the following bind_variable feature as you see fit.
1509 xProgress := 'POCOB-10-1510';
1510 dbms_sql.bind_variable(line_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1511
1512 xProgress := 'POCOB-10-1515';
1513 dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1514
1515 xProgress := 'POCOB-10-1520';
1516 dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1517
1518 xProgress := 'POOB-10-1525';
1519 dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1520
1521 xProgress := 'POCOB-10-1530';
1522 dummy := dbms_sql.execute(line_sel_c);
1523
1524 -- *********************
1525 -- Line Level Loop Starts Here
1526 -- *********************
1527 xProgress := 'POCOB-10-1540';
1528 WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
1529
1530 -- ****************************
1531 -- store values in pl/sql table
1532 -- ****************************
1533 xProgress := 'POCOB-10-1550';
1534 ece_flatfile_pvt.assign_column_value_to_tbl(line_sel_c,iHeader_count,l_line_tbl,l_key_tbl);
1535
1536 -- The following procedure gets the part number for the
1537 -- item ID returned
1538 xProgress := 'POCOB-10-1640';
1539 ece_inventory.get_item_number(l_line_tbl(nItem_ID_pos).value,
1540 nOrganization_ID,
1541 cline_part_number,
1542 cline_part_attrib_category,
1543 cline_part_attribute1,
1544 cline_part_attribute2,
1545 cline_part_attribute3,
1546 cline_part_attribute4,
1547 cline_part_attribute5,
1548 cline_part_attribute6,
1549 cline_part_attribute7,
1550 cline_part_attribute8,
1551 cline_part_attribute9,
1552 cline_part_attribute10,
1553 cline_part_attribute11,
1554 cline_part_attribute12,
1555 cline_part_attribute13,
1556 cline_part_attribute14,
1557 cline_part_attribute15);
1558
1559 begin
1560
1561 select uom_code into l_line_tbl(nLine_uom_pos).value
1562 from mtl_units_of_measure
1563 where unit_of_measure = l_line_tbl(nLine_uom_code_pos).value;
1564 exception
1565 when others then
1566 null;
1567 end;
1568
1569 xProgress := 'POCOB-TZ-2500';
1570 ece_timezone_api.get_server_timezone_details
1571 (
1572 to_date(l_line_tbl(nLine_can_dt_pos).value,'YYYYMMDD HH24MISS'),
1573 l_line_tbl(nLine_can_off_pos).value,
1574 l_line_tbl(nLine_can_tz_pos).value
1575 );
1576
1577 xProgress := 'POCOB-TZ-2510';
1578
1579 ece_timezone_api.get_server_timezone_details
1580 (
1581 to_date(l_line_tbl(nExprn_dt_pos).value,'YYYYMMDD HH24MISS'),
1582 l_line_tbl(nExprn_off_pos).value,
1583 l_line_tbl(nExprn_tz_pos).value
1584 );
1585 -- pass the pl/sql table in for xref
1586 xProgress := 'POCOB-10-1570';
1587 ec_code_conversion_pvt.populate_plsql_tbl_with_extval(
1588 p_api_version_number => 1.0,
1589 p_init_msg_list => init_msg_list,
1590 p_simulate => simulate,
1591 p_commit => commt,
1592 p_validation_level => validation_level,
1593 p_return_status => return_status,
1594 p_msg_count => msg_count,
1595 p_msg_data => msg_data,
1596 p_key_tbl => l_key_tbl,
1597 p_tbl => l_line_tbl);
1598
1599 xProgress := 'POCOB-10-1590';
1600 BEGIN
1601 SELECT ece_poco_line_s.NEXTVAL INTO l_line_fkey
1602 FROM DUAL;
1603
1604 EXCEPTION
1605 WHEN NO_DATA_FOUND THEN
1606 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_POCO_LINE_S');
1607
1608 END;
1609 ec_debug.pl(3,'l_line_fkey: ',l_line_fkey);
1610
1611 -- Insert into Interface Table
1612 /* xProgress := 'POCOB-10-1600';
1613 ece_extract_utils_pub.insert_into_interface_tbl(
1614 iRun_id,
1615 cTransaction_Type,
1616 cCommunication_Method,
1617 cLine_Interface,
1618 l_line_tbl,
1619 l_line_fkey); */ -- 2823215
1620
1621 if ec_debug.G_debug_level = 3 then
1622 ec_debug.pl(3,'cline_part_number: ' ,cline_part_number);
1623 ec_debug.pl(3,'cline_part_attrib_category: ',cline_part_attrib_category);
1624 ec_debug.pl(3,'cline_part_attribute1: ' ,cline_part_attribute1);
1625 ec_debug.pl(3,'cline_part_attribute2: ' ,cline_part_attribute2);
1626 ec_debug.pl(3,'cline_part_attribute3: ' ,cline_part_attribute3);
1627 ec_debug.pl(3,'cline_part_attribute4: ' ,cline_part_attribute4);
1628 ec_debug.pl(3,'cline_part_attribute5: ' ,cline_part_attribute5);
1629 ec_debug.pl(3,'cline_part_attribute6: ' ,cline_part_attribute6);
1630 ec_debug.pl(3,'cline_part_attribute7: ' ,cline_part_attribute7);
1631 ec_debug.pl(3,'cline_part_attribute8: ' ,cline_part_attribute8);
1632 ec_debug.pl(3,'cline_part_attribute9: ' ,cline_part_attribute9);
1633 ec_debug.pl(3,'cline_part_attribute10: ' ,cline_part_attribute10);
1634 ec_debug.pl(3,'cline_part_attribute11: ' ,cline_part_attribute11);
1635 ec_debug.pl(3,'cline_part_attribute12: ' ,cline_part_attribute12);
1636 ec_debug.pl(3,'cline_part_attribute13: ' ,cline_part_attribute13);
1637 ec_debug.pl(3,'cline_part_attribute14: ' ,cline_part_attribute14);
1638 ec_debug.pl(3,'cline_part_attribute15: ' ,cline_part_attribute15);
1639 END if;
1640
1641 xProgress := 'POOB-10-1591';
1642 -- 2823215
1643 l_line_tbl(nLp_att_cat_pos).value := cline_part_attrib_category;
1644 l_line_tbl(nLp_att1_pos).value := cline_part_attribute1;
1645 l_line_tbl(nLp_att2_pos).value := cline_part_attribute2;
1646 l_line_tbl(nLp_att3_pos).value := cline_part_attribute3;
1647 l_line_tbl(nLp_att4_pos).value := cline_part_attribute4;
1648 l_line_tbl(nLp_att5_pos).value := cline_part_attribute5;
1649 l_line_tbl(nLp_att6_pos).value := cline_part_attribute6;
1650 l_line_tbl(nLp_att7_pos).value := cline_part_attribute7;
1651 l_line_tbl(nLp_att8_pos).value := cline_part_attribute8;
1652 l_line_tbl(nLp_att9_pos).value := cline_part_attribute9;
1653 l_line_tbl(nLp_att10_pos).value := cline_part_attribute10;
1654 l_line_tbl(nLp_att11_pos).value := cline_part_attribute11;
1655 l_line_tbl(nLp_att12_pos).value := cline_part_attribute12;
1656 l_line_tbl(nLp_att13_pos).value := cline_part_attribute13;
1657 l_line_tbl(nLp_att14_pos).value := cline_part_attribute14;
1658 l_line_tbl(nLp_att15_pos).value := cline_part_attribute15;
1659
1660 xProgress := 'POOB-10-1600';
1661 c_file_common_key := RPAD(SUBSTRB(NVL
1662 (l_header_tbl(nTrans_code_pos).value,' '),
1663 1,
1664 25),25) ||
1665 RPAD(SUBSTRB(NVL
1666 (l_header_tbl(n_header_common_key_pos).value,' '),
1667 1,
1668 22),22) ||
1669 RPAD(SUBSTRB(NVL
1670 (l_line_tbl(n_line_common_key_pos).value,' '),
1671 1,
1672 22),22) ||
1673 RPAD(' ',22);
1674
1675 if ec_debug.G_debug_level = 3 then
1676 ec_debug.pl(3,'c_file_common_key: ',c_file_common_key);
1677 end if;
1678
1679 xProgress := 'POOB-10-1621';
1680
1681 -- 2823215
1682
1683 -- Now update the columns values of which have been obtained thru the procedure calls.
1684 /* xProgress := 'POCOB-10-1610';
1685 UPDATE ece_po_interface_lines
1686 SET line_part_number = cline_part_number,
1687 line_part_attrib_category = cline_part_attrib_category,
1688 line_part_attribute1 = cline_part_attribute1,
1689 line_part_attribute2 = cline_part_attribute2,
1690 line_part_attribute3 = cline_part_attribute3,
1691 line_part_attribute4 = cline_part_attribute4,
1692 line_part_attribute5 = cline_part_attribute5,
1693 line_part_attribute6 = cline_part_attribute6,
1694 line_part_attribute7 = cline_part_attribute7,
1695 line_part_attribute8 = cline_part_attribute8,
1696 line_part_attribute9 = cline_part_attribute9,
1697 line_part_attribute10 = cline_part_attribute10,
1698 line_part_attribute11 = cline_part_attribute11,
1699 line_part_attribute12 = cline_part_attribute12,
1700 line_part_attribute13 = cline_part_attribute13,
1701 line_part_attribute14 = cline_part_attribute14,
1702 line_part_attribute15 = cline_part_attribute15
1703 WHERE transaction_record_id = l_line_fkey; */
1704
1705 --2412921 begin
1706 ec_debug.pl(3,'document type ',l_header_tbl(nDocument_type_pos).value);
1707 if l_header_tbl(nDocument_type_pos).value NOT IN ('RELEASE','BLANKET RELEASE') then
1708
1709 begin
1710 cancel_flag_value := l_header_tbl(nHeader_Cancel_Flag_pos).value;
1711 ec_debug.pl(3,'cancel_flag_value->' ,cancel_flag_value);
1712
1713 if cancel_flag_value = 'Y' then
1714 cancel_date_value := to_date(l_line_tbl(nCancel_Date_posl).value,'YYYYMMDD HH24MISS');
1715 ec_debug.pl(3,'cancel_date_value->' ,l_line_tbl(nCancel_Date_posl).value);
1716 end if;
1717
1718 if cancel_date_value is not null then counter := counter + 1;
1719 end if;
1720
1721 ec_debug.pl(3,'counter -->' ,counter);
1722
1723 /* If Header is already updated with cancel date from line, then no need
1724 to update again */
1725 if counter = 1 then
1726 /* update ece_po_interface_headers set
1727 po_cancelled_date = cancel_date_value
1728 where po_header_id = l_header_tbl(nHeader_key_pos).value; */
1729 l_header_tbl(nHeader_cancel_date_pos).value := cancel_date_value;
1730 end if;
1731
1732 exception
1733 when no_data_found then
1734 null;
1735 when others then
1736 null;
1737 end;
1738
1739 end if;
1740 -- 2823215
1741 xProgress := 'POCOB-10-1620';
1742 ece_poco_x.populate_ext_line(l_line_fkey,l_line_tbl);
1743
1744 ece_poo_transaction.write_to_file( cTransaction_Type,
1745 cCommunication_Method,
1746 cLine_Interface,
1747 l_line_tbl,
1748 iOutput_width,
1749 iRun_id,
1750 c_file_common_key,
1751 l_line_fkey);
1752 -- 2823215
1753 -- 2412921 end
1754
1755 IF SQL%NOTFOUND THEN
1756 ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','LINE PART','TABLE_NAME','ECE_PO_INTERFACE_LINES');
1757 END IF;
1758
1759 -- ********************************************************
1760 -- Call custom program stub to populate the extension table
1761 -- ********************************************************
1762
1763 /***************************
1764 * Line Level Attachments *
1765 ***************************/
1766 IF v_line_att_enabled = 'Y' THEN
1767 xProgress := 'POCOB-10-1621';
1768 /* Bug 2235872 IF l_document_type = 'NR' THEN -- If this is a Release PO.
1769 xProgress := 'POCOB-10-1622';
1770 v_entity_name := 'PO_SHIPMENTS';
1771 v_pk1_value := l_line_tbl(nLine_Location_ID_pos).value; -- LINE_LOCATION_ID
1772 ec_debug.pl(3,'PO_LINE_LOCATION_ID: ',l_line_tbl(nLine_Location_ID_pos).value);
1773 ELSE -- If this is a non-Release PO. */
1774 xProgress := 'POCOB-10-1623';
1775 v_entity_name := 'PO_LINES';
1776 v_pk1_value := l_line_tbl(nLine_key_pos).value; -- LINE_ID
1777 ec_debug.pl(3,'PO_LINE_ID: ',l_line_tbl(nLine_key_pos).value);
1778 -- END IF;
1779
1780 xProgress := 'POCOB-10-1624';
1781 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1782 cTransaction_Type,
1783 iRun_id,
1784 5,
1785 6,
1786 cAtt_Header_Interface,
1787 cAtt_Detail_Interface,
1788 v_entity_name,
1789 'VENDOR',
1790 v_pk1_value,
1791 ECE_POO_TRANSACTION.C_ANY_VALUE, -- BUG:5367903
1792 ECE_POO_TRANSACTION.C_ANY_VALUE,
1793 ECE_POO_TRANSACTION.C_ANY_VALUE,
1794 ECE_POO_TRANSACTION.C_ANY_VALUE,
1795 n_att_seg_size,
1796 l_key_tbl,
1797 c_file_common_key,
1798 l_ln_att_hdr_tbl,
1799 l_ln_att_dtl_tbl,
1800 iAtt_ln_pos);
1801
1802 END IF;
1803
1804 /***************************
1805 * Master Org Attachments *
1806 ***************************/
1807 IF v_mitem_att_enabled = 'Y' THEN
1808 xProgress := 'POCOB-10-1625';
1809 v_entity_name := 'MTL_SYSTEM_ITEMS';
1810 v_pk1_value := nOrganization_ID; -- Master Inventory Org ID
1811 ec_debug.pl(3,'Master Org ID: ',v_pk1_value);
1812
1813 v_pk2_value := l_line_tbl(nitem_id_pos).value; -- Item ID
1814 ec_debug.pl(3,'Item ID: ',v_pk2_value);
1815
1816 xProgress := 'POCOB-10-1626';
1817 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1818 cTransaction_Type,
1819 iRun_id,
1820 7,
1821 8,
1822 cAtt_Header_Interface,
1823 cAtt_Detail_Interface,
1824 v_entity_name,
1825 'VENDOR',
1826 v_pk1_value,
1827 v_pk2_value,
1828 NULL,
1829 NULL,
1830 NULL,
1831 n_att_seg_size,
1832 l_key_tbl,
1833 c_file_common_key,
1834 l_mi_att_hdr_tbl,
1835 l_mi_att_dtl_tbl,
1836 iAtt_mi_pos);
1837 END IF;
1838
1839 /******************************
1840 * Inventory Org Attachments *
1841 ******************************/
1842 IF v_iitem_att_enabled = 'Y' THEN
1843 xProgress := 'POCOB-10-1627';
1844 v_entity_name := 'MTL_SYSTEM_ITEMS';
1845 v_pk2_value := l_line_tbl(nitem_id_pos).value; -- Item ID
1846 ec_debug.pl(3,'Item ID: ',v_pk2_value);
1847
1848 xProgress := 'POCOB-10-1628';
1849 FOR v_org_id IN c_org_id(l_line_tbl(nLine_key_pos).value) LOOP -- Value passed is the Line ID
1850 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
1851 v_pk1_value := v_org_id.ship_to_organization_id;
1852 ec_debug.pl(3,'Inventory Org ID: ',v_pk1_value);
1853
1854 xProgress := 'POCOB-10-1626';
1855 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
1856 cTransaction_Type,
1857 iRun_id,
1858 9,
1859 10,
1860 cAtt_Header_Interface,
1861 cAtt_Detail_Interface,
1862 v_entity_name,
1863 'VENDOR',
1864 v_pk1_value,
1865 v_pk2_value,
1866 NULL,
1867 NULL,
1868 NULL,
1869 n_att_seg_size,
1870 l_key_tbl,
1871 c_file_common_key,
1872 l_msi_att_hdr_tbl,
1873 l_msi_att_dtl_tbl,
1874 iAtt_msi_pos);
1875 END IF;
1876 END LOOP;
1877 END IF;
1878 -- **********************
1879 -- set LINE_NUMBER values
1880 -- **********************
1881 -- Removed based on bug:3957851
1882 -- xProgress := 'POOB-10-1627';
1883 -- dbms_sql.bind_variable(shipment_sel_c,'shipment_number',l_line_tbl(nLine_num_pos).value);
1884
1885 xProgress := 'POCOB-10-1630';
1886 dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1887
1888 xProgress := 'POCOB-10-1640';
1889 dummy := dbms_sql.execute(shipment_sel_c);
1890
1891 -- *************************
1892 -- Shipment loop starts here
1893 -- *************************
1894 xProgress := 'POCOB-10-1650';
1895 WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipment
1896
1897 -- ****************************
1898 -- store values in pl/sql table
1899 -- ****************************
1900 xProgress := 'POCOB-10-1660';
1901 ece_flatfile_pvt.assign_column_value_to_tbl(shipment_sel_c,iHeader_count + iLine_count,l_Shipment_tbl,l_key_tbl);
1902
1903 -- Calculate Pending Quantity
1904 xProgress := 'POCOB-10-1665';
1905 l_shipment_tbl(nQuantity_pending_pos).value := NVL(rcv_quantities_s.get_pending_qty(l_shipment_tbl(nShip_Line_location_id_pos).value),0);
1906 ec_debug.pl(3,'l_Shipment_tbl(nQuantity_pending_pos).value: ',l_shipment_tbl(nQuantity_pending_pos).value);
1907
1908 xProgress := 'POCOB-10-1670';
1909 BEGIN
1910 SELECT ece_poco_shipment_s.NEXTVAL INTO l_shipment_fkey
1911 FROM DUAL;
1912
1913 EXCEPTION
1914 WHEN NO_DATA_FOUND THEN
1915 ec_debug.pl(0,'EC','ECE_GET_NEXT_SEQ_FAILED','PROGRESS_LEVEL',xProgress,'SEQ','ECE_POCO_SHIPMENT_S');
1916 END;
1917 ec_debug.pl(3, 'l_Shipment_fkey: ',l_Shipment_fkey);
1918
1919 l_shipment_tbl(nLine_Location_uom_pos).value := l_line_tbl(nLine_uom_code_pos).value; -- bug 2823215
1920
1921 l_shipment_tbl(nShip_Release_Num_pos).value := l_header_tbl(nRelease_num_pos).value; -- bug 2823215
1922
1923 l_shipment_tbl(nShp_uom_pos).value := l_line_tbl(nLine_uom_pos).value;
1924
1925 xProgress := 'POCOB-TZ-3500';
1926 ece_timezone_api.get_server_timezone_details
1927 (
1928 to_date(l_shipment_tbl(nShip_need_dt_pos).value,'YYYYMMDD HH24MISS'),
1929 l_shipment_tbl(nShip_need_off_pos).value,
1930 l_shipment_tbl(nShip_need_tz_pos).value
1931 );
1932
1933 xProgress := 'POCOB-TZ-3510';
1934 ece_timezone_api.get_server_timezone_details
1935 (
1936 to_date(l_shipment_tbl(nShip_prom_dt_pos).value,'YYYYMMDD HH24MISS'),
1937 l_shipment_tbl(nShip_prom_off_pos).value,
1938 l_shipment_tbl(nShip_prom_tz_pos).value
1939 );
1940
1941 xProgress := 'POCOB-TZ-3520';
1942 ece_timezone_api.get_server_timezone_details
1943 (
1944 to_date(l_shipment_tbl(nShip_accept_dt_pos).value,'YYYYMMDD HH24MISS'),
1945 l_shipment_tbl(nShip_accept_off_pos).value,
1946 l_shipment_tbl(nShip_accept_tz_pos).value
1947 );
1948
1949 xProgress := 'POCOB-TZ-3530';
1950 ece_timezone_api.get_server_timezone_details
1951 (
1952 to_date(l_shipment_tbl(nShp_can_dt_pos).value,'YYYYMMDD HH24MISS'),
1953 l_shipment_tbl(nShp_can_off_pos).value,
1954 l_shipment_tbl(nShp_can_tz_pos).value
1955 );
1956
1957 xProgress := 'POCOB-TZ-3540';
1958 ece_timezone_api.get_server_timezone_details
1959 (
1960 to_date(l_shipment_tbl(nShp_strt_dt_pos).value,'YYYYMMDD HH24MISS'),
1961 l_shipment_tbl(nShp_strt_off_pos).value,
1962 l_shipment_tbl(nShp_strt_tz_pos).value
1963 );
1964
1965 xProgress := 'POCOB-TZ-3550';
1966 ece_timezone_api.get_server_timezone_details
1967 (
1968 to_date(l_shipment_tbl(nShp_end_dt_pos).value,'YYYYMMDD HH24MISS'),
1969 l_shipment_tbl(nShp_end_off_pos).value,
1970 l_shipment_tbl(nShp_end_tz_pos).value
1971 );
1972
1973 -- pass the pl/sql table in for xref
1974 xProgress := 'POCOB-10-1680';
1975 ec_code_conversion_pvt.populate_plsql_tbl_with_extval(p_api_version_number => 1.0,
1976 p_init_msg_list => init_msg_list,
1977 p_simulate => simulate,
1978 p_commit => commt,
1979 p_validation_level => validation_level,
1980 p_return_status => return_status,
1981 p_msg_count => msg_count,
1982 p_msg_data => msg_data,
1983 p_key_tbl => l_key_tbl,
1984 p_tbl => l_Shipment_tbl);
1985
1986 xProgress := 'POCOB-10-1700';
1987 /* ece_extract_utils_pub.insert_into_interface_tbl(
1988 iRun_id,
1989 cTransaction_Type,
1990 cCommunication_Method,
1991 cShipment_Interface,
1992 l_shipment_tbl,
1993 l_shipment_fkey); */
1994
1995 xProgress := 'POOB-10-1690';
1996 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value ,' '),1,25),25) ||
1997 RPAD(SUBSTRB(NVL(l_header_tbl(n_header_common_key_pos).value ,' '),1,22),22) ||
1998 RPAD(SUBSTRB(NVL(l_line_tbl(n_line_common_key_pos).value ,' '),1,22),22) ||
1999 RPAD(SUBSTRB(NVL(l_shipment_tbl(n_ship_common_key_pos).value,' '),1,22),22);
2000 if ec_debug.G_debug_level = 3 then
2001 ec_debug.pl(3, 'c_file_common_key: ',c_file_common_key);
2002 end if;
2003
2004
2005 xProgress := 'POOB-10-1700';
2006 ece_poco_x.populate_ext_shipment(l_shipment_fkey,l_shipment_tbl);
2007
2008 -- Drop shipment
2009 xProgress := 'POCOB-10-1701';
2010 v_drop_ship_flag := OE_DROP_SHIP_GRP.PO_Line_Location_Is_Drop_Ship(l_shipment_tbl(nShip_Line_Location_ID_pos).value);
2011 xProgress := 'POCOB-10-1702';
2012
2013 if ec_debug.G_debug_level = 3 then
2014 ec_debug.pl(3, 'Drop Ship Flag:',v_drop_ship_flag);
2015 end if;
2016
2017 IF (v_drop_ship_flag is NOT NULL) THEN
2018
2019 v_header_cancel_flag := l_header_tbl(nHeader_Cancel_Flag_pos).value;
2020 v_line_cancel_flag := l_line_tbl(nLine_Cancel_Flag_pos).value;
2021 v_shipment_cancel_flag := l_shipment_tbl(nShipment_Cancel_Flag_pos).value;
2022
2023 if ec_debug.G_debug_level = 3 then
2024 ec_debug.pl(3,'v_header_cancel_flag:',v_header_cancel_flag);
2025 ec_debug.pl(3,'v_line_cancel_flag:',v_line_cancel_flag);
2026 ec_debug.pl(3,'v_shipment_cancel_flag:',v_shipment_cancel_flag);
2027 END IF;
2028
2029 if ((nvl(v_header_cancel_flag,'N') <> 'Y')
2030 and (nvl(v_line_cancel_flag,'N') <> 'Y')
2031 and (nvl(v_shipment_cancel_flag,'N') <> 'Y')) then
2032
2033 xProgress := 'POCOB-10-1703';
2034 OE_DROP_SHIP_GRP.GET_ORDER_LINE_INFO(1.0,
2035 l_header_tbl(nHeader_key_pos).value,
2036 l_line_tbl(nLine_key_pos).value,
2037 l_shipment_tbl(nShip_Line_Location_ID_pos).value,
2038 l_header_tbl(nRelease_id_pos).value,
2039 2,
2040 rec_order_line_info,
2041 msg_data,
2042 msg_count,
2043 return_status
2044 );
2045 xProgress := 'POCOB-10-1704';
2046
2047 if ec_debug.G_debug_level = 3 then
2048 ec_debug.pl(3,'Ship to Customer Name:',rec_order_line_info.ship_to_customer_name);
2049 ec_debug.pl(3,'Ship to Contact Name:',rec_order_line_info.ship_to_contact_name);
2050 ec_debug.pl(3,'Ship to Contact Phone:',rec_order_line_info.ship_to_contact_phone);
2051 ec_debug.pl(3,'Ship to Contact Fax:',rec_order_line_info.ship_to_contact_fax);
2052 ec_debug.pl(3,'Ship to Contact Email:',rec_order_line_info.ship_to_contact_email);
2053 ec_debug.pl(3,'Shipping Instructions:',rec_order_line_info.shipping_instructions);
2054 ec_debug.pl(3,'Packing Instructions:',rec_order_line_info.packing_instructions);
2055 ec_debug.pl(3,'Shipping Method:',rec_order_line_info.shipping_method);
2056 ec_debug.pl(3,'Customer PO Number:',rec_order_line_info.customer_po_number);
2057 ec_debug.pl(3,'Customer PO Line Number:',rec_order_line_info.customer_po_line_number);
2058 ec_debug.pl(3,'Customer PO Shipment Num:',rec_order_line_info.customer_po_shipment_number);
2059 ec_debug.pl(3,'Customer Item Description:',rec_order_line_info.customer_product_description);
2060 ec_debug.pl(3,'Deliver to Location:',rec_order_line_info.deliver_to_customer_location);
2061 ec_debug.pl(3,'Deliver to Customer Name:',rec_order_line_info.deliver_to_customer_name);
2062 ec_debug.pl(3,'Deliver to Contact Name:',rec_order_line_info.deliver_to_customer_name);
2063 ec_debug.pl(3,'Deliver to Contact Phone:',rec_order_line_info.deliver_to_contact_phone);
2064 ec_debug.pl(3,'Deliver to Contact Fax:',rec_order_line_info.deliver_to_contact_fax);
2065 ec_debug.pl(3,'Deliver to Customer Address:',rec_order_line_info.deliver_to_customer_address);
2066 ec_debug.pl(3,'Deliver to Contact Email:', rec_order_line_info.deliver_to_contact_email);
2067 end if;
2068
2069
2070 -- 2823215
2071 l_shipment_tbl(nSt_cust_name_pos).value := rec_order_line_info.ship_to_customer_name;
2072 l_shipment_tbl(nSt_cont_name_pos).value := rec_order_line_info.ship_to_contact_name;
2073 l_shipment_tbl(nSt_cont_phone_pos).value := rec_order_line_info.ship_to_contact_phone;
2074 l_shipment_tbl(nSt_cont_fax_pos).value := rec_order_line_info.ship_to_contact_fax;
2075 l_shipment_tbl(nSt_cont_email_pos).value := rec_order_line_info.ship_to_contact_email;
2076 l_shipment_tbl(nShipping_Instruct_pos).value := rec_order_line_info.shipping_instructions;
2077 l_shipment_tbl(nPacking_Instruct_pos).value := rec_order_line_info.packing_instructions;
2078 l_shipment_tbl(nShipping_method_pos).value := rec_order_line_info.shipping_method;
2079 l_shipment_tbl(nCust_po_num_pos).value := rec_order_line_info.customer_po_number;
2080 l_shipment_tbl(nCust_po_line_num_pos).value := rec_order_line_info.customer_po_line_number;
2081 l_shipment_tbl(nCust_po_ship_num_pos).value := rec_order_line_info.customer_po_shipment_number;
2082 l_shipment_tbl(nCust_prod_desc_pos).value := rec_order_line_info.customer_product_description;
2083 l_shipment_tbl(nDeliv_cust_loc_pos).value := rec_order_line_info.deliver_to_customer_location;
2084 l_shipment_tbl(nDeliv_cust_name_pos).value := rec_order_line_info.deliver_to_customer_name;
2085 l_shipment_tbl(nDeliv_cont_name_pos).value := rec_order_line_info.deliver_to_contact_name;
2086 l_shipment_tbl(nDeliv_cont_phone_pos).value := rec_order_line_info.deliver_to_contact_phone;
2087 l_shipment_tbl(nDeliv_cont_fax_pos).value := rec_order_line_info.deliver_to_contact_fax;
2088 l_shipment_tbl(nDeliv_cust_addr_pos).value := rec_order_line_info.deliver_to_customer_address;
2089 l_shipment_tbl(nDeliv_cont_email_pos).value := rec_order_line_info.deliver_to_contact_email;
2090 -- 2823215
2091 end if;
2092
2093 END IF;
2094
2095
2096 ece_poo_transaction.write_to_file(cTransaction_Type,
2097 cCommunication_Method,
2098 cShipment_Interface,
2099 l_shipment_tbl,
2100 iOutput_width,
2101 iRun_id,
2102 c_file_common_key,
2103 l_shipment_fkey);
2104
2105 -- ********************************************************
2106 -- Call custom program stub to populate the extension table
2107 -- ********************************************************
2108 /* xProgress := 'POCOB-10-1710';
2109 ece_poco_x.populate_ext_shipment(l_shipment_fkey,l_shipment_tbl);
2110 */
2111 -- Shipment Level Attachment Handler
2112 IF v_ship_att_enabled = 'Y' THEN
2113 v_entity_name := 'PO_SHIPMENTS';
2114 v_pk1_value := l_shipment_tbl(nShip_Line_Location_ID_pos).value;
2115 ec_debug.pl(3,'Ship Level Line Location ID: ',l_shipment_tbl(nShip_Line_Location_ID_pos).value);
2116
2117 xProgress := 'POCOB-10-1720';
2118 ece_poo_transaction.populate_text_attachment(cCommunication_Method,
2119 cTransaction_Type,
2120 iRun_id,
2121 12,
2122 13,
2123 cAtt_Header_Interface,
2124 cAtt_Detail_Interface,
2125 v_entity_name,
2126 'VENDOR',
2127 v_pk1_value,
2128 ECE_POO_TRANSACTION.C_ANY_VALUE, -- BUG:5367903
2129 ECE_POO_TRANSACTION.C_ANY_VALUE,
2130 ECE_POO_TRANSACTION.C_ANY_VALUE,
2131 ECE_POO_TRANSACTION.C_ANY_VALUE,
2132 n_att_seg_size,
2133 l_key_tbl,
2134 c_file_common_key,
2135 l_shp_att_hdr_tbl,
2136 l_shp_att_dtl_tbl,
2137 iAtt_shp_pos);
2138 END IF;
2139
2140 -- Project Level Handler
2141 xProgress := 'POCOB-10-1730';
2142 -- if project_acct_status = 'I' THEN -- Project Accounting is Installed bug 1891291
2143 ece_poo_transaction.populate_distribution_info(
2144 cCommunication_Method,
2145 cTransaction_Type,
2146 iRun_id,
2147 cProject_Interface,
2148 l_key_tbl,
2149 l_header_tbl(nHeader_key_pos).value, -- PO_HEADER_ID
2150 l_header_tbl(nRelease_id_pos).value, -- PO_RELEASE_ID
2151 l_line_tbl(nLine_key_pos).value, -- PO_LINE_ID
2152 l_shipment_tbl(nShip_Line_Location_ID_pos).value, -- LINE_LOCATION_ID
2153 c_file_common_key); --2823215
2154 -- END IF;
2155
2156 END LOOP; -- SHIPMENT Level Loop
2157
2158 xProgress := 'POCOB-10-1740';
2159 IF dbms_sql.last_row_count = 0 THEN
2160 v_LevelProcessed := 'SHIPMENT';
2161 ec_debug.pl(0,'EC','ECE_NO_DB_ROW_PROCESSED','PROGRESS_LEVEL',xProgress,'LEVEL_PROCESSED',v_LevelProcessed,'TRANSACTION_TYPE',cTransaction_Type);
2162 END IF;
2163
2164 END LOOP; -- LINE Level Loop
2165
2166 xProgress := 'POCOB-10-1750';
2167 IF dbms_sql.last_row_count = 0 THEN
2168 v_LevelProcessed := 'LINE';
2169 ec_debug.pl(0,'EC','ECE_NO_DB_ROW_PROCESSED','PROGRESS_LEVEL',xProgress,'LEVEL_PROCESSED',v_LevelProcessed,'TRANSACTION_TYPE',cTransaction_Type);
2170 END IF;
2171
2172 END LOOP; -- HEADER Level Loop
2173
2174 xProgress := 'POCOB-10-1760';
2175 IF dbms_sql.last_row_count = 0 THEN
2176 v_LevelProcessed := 'HEADER';
2177 ec_debug.pl(0,'EC','ECE_NO_DB_ROW_PROCESSED','LEVEL_PROCESSED',v_LevelProcessed,'PROGRESS_LEVEL',xProgress,'TRANSACTION_TYPE',cTransaction_Type);
2178 END IF;
2179
2180 xProgress := 'POCOB-10-1770';
2181
2182 if (ece_poo_transaction.project_sel_c >0) then --Bug 2819176
2183
2184 dbms_sql.close_cursor(ece_poo_transaction.project_sel_c); --Bug 2490109
2185 end if;
2186 xProgress := 'POCOB-10-1780';
2187
2188 dbms_sql.close_cursor(shipment_sel_c);
2189
2190 xProgress := 'POCOB-10-1790';
2191 dbms_sql.close_cursor(line_sel_c);
2192
2193 xProgress := 'POCOB-10-1800';
2194 dbms_sql.close_cursor(header_sel_c);
2195
2196 ec_debug.pop('ECE_POCO_TRANSACTION.POPULATE_POCO_TRX');
2197
2198 EXCEPTION
2199 WHEN OTHERS THEN
2200 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
2201 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2202
2203 app_exception.raise_exception;
2204
2205 END populate_poco_trx;
2206
2207 PROCEDURE put_data_to_output_table(cCommunication_Method IN VARCHAR2,
2208 cTransaction_Type IN VARCHAR2,
2209 iOutput_width IN INTEGER,
2210 iRun_id IN INTEGER,
2211 cHeader_Interface IN VARCHAR2,
2212 cLine_Interface IN VARCHAR2,
2213 cShipment_Interface IN VARCHAR2,
2214 cProject_Interface IN VARCHAR2) IS
2215
2216 xProgress VARCHAR2(80);
2217 v_LevelProcessed VARCHAR2(40);
2218
2219 cAtt_Header_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_HEADERS';
2220 cAtt_Detail_Interface VARCHAR2(120) := 'ECE_ATTACHMENT_DETAILS';
2221
2222 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
2223 l_line_tbl ece_flatfile_pvt.Interface_tbl_type;
2224 l_shipment_tbl ece_flatfile_pvt.Interface_tbl_type;
2225
2226 l_document_type VARCHAR2(30);
2227
2228 c_header_common_key_name VARCHAR2(40);
2229 c_line_common_key_name VARCHAR2(40);
2230 c_shipment_key_name VARCHAR2(40);
2231 c_file_common_key VARCHAR2(255);
2232
2233 nHeader_key_pos NUMBER;
2234 nLine_key_pos NUMBER;
2235 nShipment_key_pos NUMBER;
2236 nTrans_code_pos NUMBER;
2237
2238 header_sel_c INTEGER;
2239 line_sel_c INTEGER;
2240 shipment_sel_c INTEGER;
2241
2242 header_del_c1 INTEGER;
2243 line_del_c1 INTEGER;
2244 shipment_del_c1 INTEGER;
2245
2246 header_del_c2 INTEGER;
2247 line_del_c2 INTEGER;
2248 shipment_del_c2 INTEGER;
2249
2250 cHeader_select VARCHAR2(32000);
2251 cLine_select VARCHAR2(32000);
2252 cShipment_select VARCHAR2(32000);
2253
2254 cHeader_from VARCHAR2(32000);
2255 cLine_from VARCHAR2(32000);
2256 cShipment_from VARCHAR2(32000);
2257
2258 cHeader_where VARCHAR2(32000);
2259 cLine_where VARCHAR2(32000);
2260 cShipment_where VARCHAR2(32000);
2261
2262 cHeader_delete1 VARCHAR2(32000);
2263 cLine_delete1 VARCHAR2(32000);
2264 cShipment_delete1 VARCHAR2(32000);
2265
2266 cHeader_delete2 VARCHAR2(32000);
2267 cLine_delete2 VARCHAR2(32000);
2268 cShipment_delete2 VARCHAR2(32000);
2269
2270 iHeader_count NUMBER;
2271 iLine_count NUMBER;
2272 iShipment_count NUMBER;
2273
2274 rHeader_rowid ROWID;
2275 rLine_rowid ROWID;
2276 rShipment_rowid ROWID;
2277
2278 cHeader_X_Interface VARCHAR2(50);
2279 cLine_X_Interface VARCHAR2(50);
2280 cShipment_X_Interface VARCHAR2(50);
2281
2282 rHeader_X_rowid ROWID;
2283 rLine_X_rowid ROWID;
2284 rShipment_X_rowid ROWID;
2285
2286 iHeader_start_num INTEGER;
2287 iLine_start_num INTEGER;
2288 iShipment_start_num INTEGER;
2289 dummy INTEGER;
2290
2291 nDocument_type_pos NUMBER;
2292 nPos1 NUMBER;
2293 nTrans_id NUMBER;
2294 n_po_header_id NUMBER;
2295 nRelease_id NUMBER;
2296 nRelease_id_pos NUMBER;
2297 n_po_line_id NUMBER;
2298 nPO_Line_Location_ID_pos NUMBER;
2299 nPO_Line_Location_ID NUMBER;
2300 nLine_Location_ID_pos NUMBER;
2301 nLine_Location_ID NUMBER;
2302 nLine_num_pos NUMBER;
2303 nLine_num NUMBER;
2304 nRelease_num NUMBER;
2305 nRelease_num_pos NUMBER;
2306 nOrganization_ID NUMBER;
2307 nItem_id_pos NUMBER;
2308 nItem_ID NUMBER;
2309
2310 v_project_acct_installed BOOLEAN;
2311 v_project_acct_short_name VARCHAR2(2) := 'PA';
2312 v_project_acct_status VARCHAR2(120);
2313 v_project_acct_industry VARCHAR2(120);
2314 v_project_acct_schema VARCHAR2(120);
2315
2316 v_entity_name VARCHAR2(120);
2317 v_pk1_value VARCHAR2(120);
2318 v_pk2_value VARCHAR2(120);
2319
2320 CURSOR c_org_id(p_line_id NUMBER) IS
2321 SELECT DISTINCT ship_to_organization_id
2322 FROM po_line_locations
2323 WHERE po_line_id = p_line_id;
2324
2325 BEGIN
2326 ec_debug.push('ECE_POCO_TRANSACTION.PUT_DATA_TO_OUTPUT_TABLE');
2327 ec_debug.pl(3,'cCommunication_Method: ',cCommunication_Method);
2328 ec_debug.pl(3,'cTransaction_Type: ' ,cTransaction_Type);
2329 ec_debug.pl(3,'iOutput_width: ' ,iOutput_width);
2330 ec_debug.pl(3,'iRun_id: ' ,iRun_id);
2331 ec_debug.pl(3,'cHeader_Interface: ' ,cHeader_Interface);
2332 ec_debug.pl(3,'cLine_Interface: ' ,cLine_Interface);
2333 ec_debug.pl(3,'cShipment_Interface: ' ,cShipment_Interface);
2334
2335 BEGIN
2336 SELECT inventory_organization_id
2337 INTO norganization_id
2338 FROM financials_system_parameters;
2339
2340 EXCEPTION
2341 WHEN NO_DATA_FOUND THEN
2342 ec_debug.pl(0,
2343 'EC',
2344 'ECE_NO_ROW_SELECTED',
2345 'PROGRESS_LEVEL',
2346 xProgress,
2347 'INFO',
2348 'INVENTORY ORGANIZATION ID',
2349 'TABLE_NAME',
2350 'FINANCIALS_SYSTEM_PARAMETERS');
2351 END;
2352 ec_debug.pl(3,'norganization_id: ',norganization_id);
2353
2354 -- Let's See if Project Accounting is Installed
2355 xProgress := 'POCOB-20-1000';
2356 v_project_acct_installed := fnd_installation.get_app_info(
2357 v_project_acct_short_name, -- i.e. 'PA'
2358 v_project_acct_status, -- 'I' means it's installed
2359 v_project_acct_industry,
2360 v_project_acct_schema);
2361
2362 v_project_acct_status := NVL(v_project_acct_status,'X');
2363 ec_debug.pl(3,'v_project_acct_status: ' ,v_project_acct_status);
2364 ec_debug.pl(3,'v_project_acct_industry: ',v_project_acct_industry);
2365 ec_debug.pl(3,'v_project_acct_schema: ' ,v_project_acct_schema);
2366
2367 xProgress := 'POCOB-20-1005';
2368 ece_flatfile_pvt.select_clause(cTransaction_Type,
2369 cCommunication_Method,
2370 cHeader_Interface,
2371 cHeader_X_Interface,
2372 l_header_tbl,
2373 c_header_common_key_name,
2374 cHeader_select,
2375 cHeader_from,
2376 cHeader_where);
2377
2378 xProgress := 'POCOB-20-1010';
2379 ece_flatfile_pvt.select_clause(cTransaction_Type,
2380 cCommunication_Method,
2381 cLine_Interface,
2382 cLine_X_Interface,
2383 l_line_tbl,
2384 c_line_common_key_name,
2385 cLine_select,
2386 cLine_from,
2387 cLine_where);
2388
2389 xProgress := 'POCOB-20-1020';
2390 ece_flatfile_pvt.select_clause(cTransaction_Type,
2391 cCommunication_Method,
2392 cShipment_Interface,
2393 cShipment_X_Interface,
2394 l_shipment_tbl,
2395 c_shipment_key_name,
2396 cShipment_select,
2397 cShipment_from,
2398 cShipment_where);
2399
2400 -- Header Level Find Positions
2401 xProgress := 'POCOB-20-1021';
2402 ece_flatfile_pvt.find_pos(l_header_tbl,ece_flatfile_pvt.G_Translator_Code,nTrans_code_pos);
2403 ec_debug.pl(3,'nTrans_code_pos: ',nTrans_code_pos);
2404
2405 xProgress := 'POCOB-20-1022';
2406 ece_flatfile_pvt.find_pos(l_header_tbl,c_header_common_key_name,nHeader_key_pos);
2407 ec_debug.pl(3,'nHeader_key_pos: ',nHeader_key_pos);
2408
2409 xProgress := 'POCOB-20-1023';
2410 ece_flatfile_pvt.find_pos(l_header_tbl,'RELEASE_NUMBER',nRelease_num_pos);
2411 ec_debug.pl(3,'nRelease_num_pos: ',nRelease_num_pos);
2412
2413 xProgress := 'POCOB-20-1024';
2414 ece_flatfile_pvt.find_pos(l_header_tbl,'PO_RELEASE_ID',nRelease_id_pos);
2415 ec_debug.pl(3,'nRelease_id_pos: ',nRelease_id_pos);
2416
2417 xProgress := 'POCOB-20-1025';
2418 ece_flatfile_pvt.find_pos(l_header_tbl,'DOCUMENT_TYPE',nDocument_type_pos);
2419 ec_debug.pl(3,'nDocument_type_pos: ',nDocument_type_pos);
2420
2421 -- Line Level Find Positions
2422 xProgress := 'POCOB-20-1026';
2423 ece_flatfile_pvt.find_pos(l_line_tbl,c_line_common_key_name,nLine_key_pos);
2424 ec_debug.pl(3,'nLine_key_pos: ',nLine_key_pos);
2425
2426 xProgress := 'POCOB-20-1027';
2427 ece_flatfile_pvt.find_pos(l_line_tbl,'LINE_NUMBER',nLine_num_pos);
2428 ec_debug.pl(3,'nLine_num_pos: ',nLine_num_pos);
2429
2430 xProgress := 'POCOB-20-1028';
2431 ece_flatfile_pvt.find_pos(l_line_tbl,'PO_LINE_LOCATION_ID',nPO_Line_Location_ID_pos);
2432 ec_debug.pl(3,'nPO_Line_Location_ID_pos: ',nPO_Line_Location_ID_pos);
2433
2434 xProgress := 'POCOB-20-1029';
2435 ece_flatfile_pvt.find_pos(l_line_tbl,'ITEM_ID',nItem_id_pos);
2436 ec_debug.pl(3,'nItem_id_pos: ',nItem_id_pos);
2437
2438 -- Shipment Level Find Positions
2439 xProgress := 'POCOB-20-1030';
2440 ece_flatfile_pvt.find_pos(l_shipment_tbl,'LINE_LOCATION_ID',nLine_Location_ID_pos);
2441 ec_debug.pl(3,'nLine_Location_ID_pos: ',nLine_Location_ID_pos);
2442
2443 xProgress := 'POCOB-20-1032';
2444 ece_flatfile_pvt.find_pos(l_shipment_tbl,c_shipment_key_name,nShipment_key_pos);
2445 ec_debug.pl(3,'nShipment_key_pos: ',nShipment_key_pos);
2446
2447 -- Build SELECT Statement
2448 xProgress := 'POCOB-20-1035';
2449 cHeader_where := cHeader_where ||
2450 ' AND ' ||
2451 cHeader_Interface ||
2452 '.run_id = ' ||
2453 ':Run_id';
2454
2455 cLine_where := cLine_where ||
2456 ' AND ' ||
2457 cLine_Interface ||
2458 '.run_id = ' ||
2459 ':Run_id' ||
2460 ' AND ' ||
2461 cLine_Interface ||
2462 '.po_header_id = :po_header_id AND ' ||
2463 cLine_Interface ||
2464 '.release_number = :por_release_num';
2465
2466 cShipment_where := cShipment_where ||
2467 ' AND ' ||
2468 cShipment_Interface ||
2469 '.RUN_ID =' ||
2470 ':Run_id' ||
2471 ' AND ' ||
2472 cShipment_Interface ||
2473 '.po_header_id = :po_header_id AND ' ||
2474 cShipment_Interface ||
2475 '.po_line_id = :po_line_id AND ' ||
2476 cShipment_Interface ||
2477 '.release_number = :por_release_num AND ((' ||
2478 cShipment_Interface ||
2479 '.release_number = 0) OR (' ||
2480 cShipment_Interface ||
2481 '.release_number <> 0 AND ' ||
2482 cShipment_Interface ||
2483 '.shipment_number = :shipment_number))';
2484
2485 xProgress := 'POCOB-20-1040';
2486 cHeader_select := cHeader_select ||
2487 ',' ||
2488 cHeader_Interface ||
2489 '.rowid,' ||
2490 cHeader_X_Interface ||
2491 '.rowid,' ||
2492 cHeader_Interface ||
2493 '.po_header_id,' ||
2494 cHeader_Interface ||
2495 '.release_number ';
2496
2497 cLine_select := cLine_select ||
2498 ',' ||
2499 cLine_Interface ||
2500 '.rowid,' ||
2501 cLine_X_Interface ||
2502 '.rowid,' ||
2503 cLine_Interface ||
2504 '.po_line_id,' ||
2505 cLine_Interface ||
2506 '.line_number ';
2507
2508 cShipment_select := cShipment_select ||
2509 ',' ||
2510 cShipment_Interface ||
2511 '.rowid,' ||
2512 cShipment_X_Interface ||
2513 '.rowid,' ||
2514 cShipment_Interface ||
2515 '.shipment_number ';
2516
2517 xProgress := 'POCOB-20-1050';
2518 cHeader_select := cHeader_select ||
2519 cHeader_from ||
2520 cHeader_where ||
2521 ' ORDER BY ' ||
2522 cHeader_Interface ||
2523 '.po_header_id,' ||
2524 cHeader_Interface ||
2525 '.release_number ' ||
2526 ' FOR UPDATE';
2527 ec_debug.pl(3,'cHeader_select: ',cHeader_select);
2528
2529 cLine_select := cLine_select ||
2530 cLine_from ||
2531 cLine_where ||
2532 ' ORDER BY ' ||
2533 cLine_Interface ||
2534 '.line_number ' ||
2535 ' FOR UPDATE';
2536 ec_debug.pl(3, 'cLine_select: ',cLine_select);
2537
2538 cShipment_select := cShipment_select ||
2539 cShipment_from ||
2540 cShipment_where ||
2541 ' ORDER BY ' ||
2542 cShipment_Interface ||
2543 '.shipment_number ' ||
2544 ' FOR UPDATE';
2545 ec_debug.pl(3, 'cShipment_select: ',cShipment_select);
2546
2547 xProgress := 'POCOB-20-1060';
2548 cHeader_delete1 := 'DELETE FROM ' || cHeader_Interface || ' WHERE rowid = :col_rowid';
2549 ec_debug.pl(3,'cHeader_delete1: ',cHeader_delete1);
2550
2551 cLine_delete1 := 'DELETE FROM ' || cLine_Interface || ' WHERE rowid = :col_rowid';
2552 ec_debug.pl(3,'cLine_delete1: ',cLine_delete1);
2553
2554 cShipment_delete1 := 'DELETE FROM ' || cShipment_Interface || ' WHERE rowid = :col_rowid';
2555 ec_debug.pl(3,'cShipment_delete1: ',cShipment_delete1);
2556
2557 xProgress := 'POCOB-20-1070';
2558 cHeader_delete2 := 'DELETE FROM ' || cHeader_X_Interface || ' WHERE rowid = :col_rowid';
2559 ec_debug.pl(3,'cHeader_delete2: ',cHeader_delete2);
2560
2561 cLine_delete2 := 'DELETE FROM ' || cLine_X_Interface || ' WHERE rowid = :col_rowid';
2562 ec_debug.pl(3,'cLine_delete2: ',cLine_delete2);
2563
2564 cShipment_delete2 := 'DELETE FROM ' || cShipment_X_Interface || ' WHERE rowid = :col_rowid';
2565 ec_debug.pl(3,'cShipment_delete2: ',cShipment_delete2);
2566
2567 -- ***************************************************
2568 -- *** Get data setup for the dynamic SQL call.
2569 -- *** Open a cursor for each of the SELECT call
2570 -- *** This tells the database to reserve spaces
2571 -- *** for the data returned by the SQL statement
2572 -- ***************************************************
2573 xProgress := 'POCOB-20-1080';
2574 header_sel_c := dbms_sql.open_cursor;
2575
2576 xProgress := 'POCOB-20-1090';
2577 line_sel_c := dbms_sql.open_cursor;
2578
2579 xProgress := 'POCOB-20-1100';
2580 shipment_sel_c := dbms_sql.open_cursor;
2581
2582 xProgress := 'POCOB-20-1110';
2583 header_del_c1 := dbms_sql.open_cursor;
2584
2585 xProgress := 'POCOB-20-1120';
2586 line_del_c1 := dbms_sql.open_cursor;
2587
2588 xProgress := 'POCOB-20-1130';
2589 shipment_del_c1 := dbms_sql.open_cursor;
2590
2591 xProgress := 'POCOB-20-1140';
2592 header_del_c2 := dbms_sql.open_cursor;
2593
2594 xProgress := 'POCOB-20-1150';
2595 line_del_c2 := dbms_sql.open_cursor;
2596
2597 xProgress := 'POCOB-20-1160';
2598 shipment_del_c2 := dbms_sql.open_cursor;
2599
2600 -- *****************************************
2601 -- Parse each of the SELECT statement
2602 -- so the database understands the command
2603 -- *****************************************
2604 xProgress := 'POCOB-20-1170';
2605 BEGIN
2606 dbms_sql.parse(header_sel_c,cHeader_select,dbms_sql.native);
2607
2608 EXCEPTION
2609 WHEN OTHERS THEN
2610 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
2611 app_exception.raise_exception;
2612
2613 END;
2614
2615 xProgress := 'POCOB-20-1180';
2616 BEGIN
2617 dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
2618
2619 EXCEPTION
2620 WHEN OTHERS THEN
2621 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
2622 app_exception.raise_exception;
2623
2624 END;
2625
2626 xProgress := 'POCOB-20-1190';
2627 BEGIN
2628 dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
2629
2630 EXCEPTION
2631 WHEN OTHERS THEN
2632 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
2633 app_exception.raise_exception;
2634
2635 END;
2636
2637 xProgress := 'POCOB-20-1200';
2638 BEGIN
2639 dbms_sql.parse(Header_del_c1,cHeader_delete1,dbms_sql.native);
2640
2641 EXCEPTION
2642 WHEN OTHERS THEN
2643 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
2644 app_exception.raise_exception;
2645
2646 END;
2647
2648 xProgress := 'POCOB-20-1210';
2649 BEGIN
2650 dbms_sql.parse(Line_del_c1,cLine_delete1,dbms_sql.native);
2651
2652 EXCEPTION
2653 WHEN OTHERS THEN
2654 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete1);
2655 app_exception.raise_exception;
2656
2657 END;
2658
2659 xProgress := 'POCOB-20-1220';
2660 BEGIN
2661 dbms_sql.parse(shipment_del_c1,cShipment_delete1,dbms_sql.native);
2662
2663 EXCEPTION
2664 WHEN OTHERS THEN
2665 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete1);
2666 app_exception.raise_exception;
2667
2668 END;
2669
2670 xProgress := 'POCOB-20-1230';
2671 BEGIN
2672 dbms_sql.parse(header_del_c2,cHeader_delete2,dbms_sql.native);
2673
2674 EXCEPTION
2675 WHEN OTHERS THEN
2676 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
2677 app_exception.raise_exception;
2678
2679 END;
2680
2681 xProgress := 'POCOB-20-1240';
2682 BEGIN
2683 dbms_sql.parse(line_del_c2,cLine_delete2,dbms_sql.native);
2684
2685 EXCEPTION
2686 WHEN OTHERS THEN
2687 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete2);
2688 app_exception.raise_exception;
2689
2690 END;
2691
2692 xProgress := 'POCOB-20-1250';
2693 BEGIN
2694 dbms_sql.parse(shipment_del_c2,cShipment_delete2,dbms_sql.native);
2695
2696 EXCEPTION
2697 WHEN OTHERS THEN
2698 ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete2);
2699 app_exception.raise_exception;
2700
2701 END;
2702
2703 -- *************
2704 -- set counter
2705 -- *************
2706 xProgress := 'POCOB-20-1260';
2707 iHeader_count := l_header_tbl.COUNT;
2708 iLine_count := l_line_tbl.COUNT;
2709 iShipment_count := l_shipment_tbl.COUNT;
2710
2711 ec_debug.pl(3,'iHeader_count: ' ,iHeader_count);
2712 ec_debug.pl(3,'iLine_count: ' ,iLine_count);
2713 ec_debug.pl(3,'iShipment_count: ',iShipment_count);
2714
2715 -- ******************************************************
2716 -- Define TYPE for every columns in the SELECT statement
2717 -- For each piece of the data returns, we need to tell
2718 -- the database what type of information it will be.
2719 -- e.g. ID is NUMBER, due_date is DATE
2720 -- However, for simplicity, we will convert
2721 -- everything to varchar2.
2722 -- ******************************************************
2723 xProgress := 'POCOB-20-1270';
2724 ece_flatfile_pvt.define_interface_column_type(header_sel_c,
2725 cHeader_select,
2726 ece_flatfile_pvt.G_MaxColWidth,
2727 l_header_tbl);
2728
2729 -- ***************************************************
2730 -- Need rowid for delete (Header Level)
2731 -- ***************************************************
2732 xProgress := 'POCOB-20-1280';
2733 dbms_sql.define_column_rowid(header_sel_c,iHeader_count + 1,rHeader_rowid);
2734
2735 xProgress := 'POCOB-20-1290';
2736 dbms_sql.define_column_rowid(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2737
2738 xProgress := 'POCOB-20-1300';
2739 dbms_sql.define_column(header_sel_c,iHeader_count + 3,n_po_header_id);
2740
2741 xProgress := 'POCOB-20-1310';
2742 ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_flatfile_pvt.G_MaxColWidth,l_line_tbl);
2743
2744 -- ***************************************************
2745 -- Need rowid for delete (Line Level)
2746 -- ***************************************************
2747 xProgress := 'POCOB-20-1320';
2748 dbms_sql.define_column_rowid(line_sel_c,iLine_count + 1,rLine_rowid);
2749
2750 xProgress := 'POCOB-20-1330';
2751 dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2752
2753 xProgress := 'POCOB-20-1340';
2754 dbms_sql.define_column(line_sel_c,iLine_count + 3,n_po_line_id);
2755
2756 xProgress := 'POCOB-20-1350';
2757 ece_flatfile_pvt.define_interface_column_type(Shipment_sel_c,cShipment_select,ece_flatfile_pvt.G_MaxColWidth,l_Shipment_tbl);
2758
2759 -- ***************************************************
2760 -- Need rowid for delete (Shipment Level)
2761 -- ***************************************************
2762 xProgress := 'POCOB-20-1360';
2763 dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 1,rShipment_rowid);
2764
2765 xProgress := 'POCOB-20-1370';
2766 dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
2767
2768 xProgress := 'POCOB-20-1375';
2769 dbms_sql.bind_variable(header_sel_c,'Run_id',iRun_id);
2770 dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2771 dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2772
2773
2774 --- EXECUTE the SELECT statement
2775 xProgress := 'POCOB-20-1380';
2776 dummy := dbms_sql.execute(header_sel_c);
2777
2778 -- ********************************************************************
2779 -- *** With data for each HEADER line, populate the ECE_OUTPUT table
2780 -- *** then populate ECE_OUTPUT with data from all LINES that belongs
2781 -- *** to the HEADER. Then populate ECE_OUTPUT with data from all
2782 -- *** LINE TAX that belongs to the LINE.
2783 -- ********************************************************************
2784
2785 -- HEADER - LINE - SHIPMENT ...
2786 xProgress := 'POCOB-20-1390';
2787 WHILE dbms_sql.fetch_rows(header_sel_c) > 0 LOOP -- Header
2788 -- ******************************
2789 -- store values in pl/sql table
2790 -- ******************************
2791 xProgress := 'POCOB-20-1400';
2792 ece_flatfile_pvt.assign_column_value_to_tbl(header_sel_c,l_header_tbl);
2793
2794 xProgress := 'POCOB-20-1410';
2795 dbms_sql.column_value(header_sel_c,iHeader_count + 1,rHeader_rowid);
2796
2797 xProgress := 'POCOB-20-1420';
2798 dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2799
2800 xProgress := 'POCOB-20-1430';
2801 dbms_sql.column_value(header_sel_c,iHeader_count + 3,n_po_header_id);
2802
2803 xProgress := 'POCOB-20-1440';
2804 nRelease_num := l_header_tbl(nRelease_num_pos).value;
2805 ec_debug.pl(3,'nRelease_num: ',nRelease_num);
2806
2807 xProgress := 'POCOB-20-1450';
2808 nRelease_ID := l_header_tbl(nRelease_id_pos).value;
2809 ec_debug.pl(3,'nRelease_ID: ',nRelease_ID);
2810
2811 BEGIN
2812 xProgress := 'POCOB-20-1455';
2813 /* Bug 2396394 Added the document type CONTRACT in SQL below */
2814
2815 SELECT DECODE(l_header_tbl(nDocument_type_pos).value,
2816 'BLANKET' ,'NB',
2817 'STANDARD' ,'NS',
2818 'PLANNED' ,'NP',
2819 'RELEASE' ,'NR',
2820 'BLANKET RELEASE' ,'NR',
2821 'CONTRACT' ,'NC',
2822 'NR')
2823 INTO l_document_type
2824 FROM DUAL;
2825
2826 EXCEPTION
2827 WHEN NO_DATA_FOUND THEN
2828 ec_debug.pl(0,
2829 'EC',
2830 'ECE_DECODE_FAILED',
2831 'PROGRESS_LEVEL',
2832 xProgress,
2833 'CODE',
2834 l_header_tbl(nDocument_type_pos).value);
2835 END;
2836 ec_debug.pl(3,'l_document_type: ',l_document_type);
2837
2838 xProgress := 'POCOB-20-1460';
2839 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),1,25),25);
2840
2841 xProgress := 'POCOB-20-1470';
2842 c_file_common_key := c_file_common_key ||
2843 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value,' '),1,22),22) || RPAD(' ',22) || RPAD(' ',22);
2844
2845 ec_debug.pl(3,'c_file_common_key: ',c_file_common_key);
2846
2847 xProgress := 'POCOB-20-1480';
2848 ece_poo_transaction.write_to_file(cTransaction_Type,
2849 cCommunication_Method,
2850 cHeader_Interface,
2851 l_header_tbl,
2852 iOutput_width,
2853 iRun_id,
2854 c_file_common_key,
2855 null );
2856
2857 IF l_document_type = 'NR' THEN -- If this is a Release PO.
2858 xProgress := 'POCOB-20-1481';
2859 v_entity_name := 'PO_RELEASES';
2860 v_pk1_value := nRelease_ID;
2861 ec_debug.pl(3,'release_id: ',nRelease_ID);
2862 ELSE -- If this is a non-Release PO.
2863 xProgress := 'POCOB-20-1482';
2864 v_entity_name := 'PO_HEADERS';
2865 v_pk1_value := n_po_header_id;
2866 ec_debug.pl(3,'po_header_id: ',n_po_header_id);
2867 END IF;
2868
2869 xProgress := 'POCOB-20-1483';
2870 ece_poo_transaction.put_att_to_output_table(cCommunication_Method,
2871 cTransaction_Type,
2872 iOutput_width,
2873 iRun_id,
2874 2,
2875 3,
2876 cAtt_Header_Interface,
2877 cAtt_Detail_Interface,
2878 v_entity_name,
2879 'VENDOR',
2880 v_pk1_value,
2881 NULL,
2882 NULL,
2883 NULL,
2884 NULL,
2885 c_file_common_key);
2886
2887 -- ***************************************************
2888 -- With Header data at hand, we can assign values to
2889 -- place holders (foreign keys) in Line_select and
2890 -- Line_detail_Select
2891 -- ***************************************************
2892 -- ******************************************
2893 -- set values into binding variables
2894 -- ******************************************
2895 xProgress := 'POCOB-20-1490';
2896 dbms_sql.bind_variable(line_sel_c,'po_header_id',n_po_header_id);
2897
2898 xProgress := 'POCOB-20-1500';
2899 dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_id);
2900
2901 xProgress := 'POCOB-20-1505';
2902 dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2903
2904 xProgress := 'POCOB-20-1506';
2905 dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2906
2907 xProgress := 'POCOB-10-1510';
2908 dummy := dbms_sql.execute(line_sel_c);
2909
2910 -- ***************************************************
2911 -- line loop starts here
2912 -- ***************************************************
2913 xProgress := 'POCOB-20-1520';
2914 WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
2915
2916 -- ***************************************************
2917 -- store values in pl/sql table
2918 -- ***************************************************
2919 xProgress := 'POCOB-20-1530';
2920 ece_flatfile_pvt.assign_column_value_to_tbl(line_sel_c,l_line_tbl);
2921
2922 xProgress := 'POCOB-20-1533';
2923 dbms_sql.column_value(line_sel_c,iLine_count + 1,rLine_rowid);
2924
2925 xProgress := 'POCOB-20-1535';
2926 dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2927
2928 xProgress := 'POCOB-20-1537';
2929 dbms_sql.column_value(line_sel_c,iLine_count + 3,n_po_line_id);
2930 ec_debug.pl(3,'n_po_line_id: ',n_po_line_id);
2931
2932 xProgress := 'POCOB-20-1540';
2933 nLine_num := l_line_tbl(nLine_num_pos).value;
2934 ec_debug.pl(3,'nLine_num: ',nLine_num);
2935
2936 xProgress := 'POCOB-20-1544';
2937 nPO_Line_Location_ID := l_line_tbl(nPO_Line_Location_ID_pos).value;
2938 ec_debug.pl(3,'nPO_Line_Location_ID: ',nPO_Line_Location_ID);
2939
2940 xProgress := 'POCOB-20-1545';
2941 nItem_ID := l_line_tbl(nItem_id_pos).value;
2942 ec_debug.pl(3,'nItem_ID: ',nItem_ID);
2943
2944 xProgress := 'POCOB-20-1550';
2945 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),1,25),25) ||
2946 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value,' '),1,22),22) ||
2947 RPAD(SUBSTRB(NVL(l_line_tbl(nLine_key_pos).value ,' '),1,22),22) ||
2948 RPAD(' ',22);
2949 ec_debug.pl(3,'c_file_common_key: ',c_file_common_key);
2950
2951 xProgress := 'POCOB-20-1551';
2952 ece_poo_transaction.write_to_file(cTransaction_Type,
2953 cCommunication_Method,
2954 cLine_Interface,
2955 l_line_tbl,
2956 iOutput_width,
2957 iRun_id,
2958 c_file_common_key,
2959 null);
2960
2961 -- Line Level Attachment Handler
2962 /* Bug 2235872 IF l_document_type = 'NR' THEN -- If this is a Release PO.
2963 xProgress := 'POCOB-20-1552';
2964 v_entity_name := 'PO_SHIPMENTS';
2965 v_pk1_value := nPO_Line_Location_ID; -- LINE_LOCATION_ID
2966 ELSE -- If this is a non-Release PO. */
2967 xProgress := 'POCOB-20-1553';
2968 v_entity_name := 'PO_LINES';
2969 v_pk1_value := n_po_line_id; -- LINE_ID
2970 -- END IF;
2971
2972 xProgress := 'POCOB-20-1554';
2973 ece_poo_transaction.put_att_to_output_table(cCommunication_Method,
2974 cTransaction_Type,
2975 iOutput_width,
2976 iRun_id,
2977 5,
2978 6,
2979 cAtt_Header_Interface,
2980 cAtt_Detail_Interface,
2981 v_entity_name,
2982 'VENDOR',
2983 v_pk1_value,
2984 NULL,
2985 NULL,
2986 NULL,
2987 NULL,
2988 c_file_common_key);
2989
2990 -- Master Item Attachment Handler
2991 xProgress := 'POCOB-20-1555';
2992 v_entity_name := 'MTL_SYSTEM_ITEMS';
2993 v_pk1_value := nOrganization_ID; -- Master Inventory Org ID
2994 ec_debug.pl(3,'Master Org ID: ',v_pk1_value);
2995
2996 v_pk2_value := nItem_ID; -- Item ID
2997 ec_debug.pl(3,'Item ID: ',v_pk2_value);
2998
2999 xProgress := 'POCOB-20-1556';
3000 ece_poo_transaction.put_att_to_output_table(cCommunication_Method,
3001 cTransaction_Type,
3002 iOutput_width,
3003 iRun_id,
3004 7,
3005 8,
3006 cAtt_Header_Interface,
3007 cAtt_Detail_Interface,
3008 v_entity_name,
3009 'VENDOR',
3010 v_pk1_value,
3011 v_pk2_value,
3012 NULL,
3013 NULL,
3014 NULL,
3015 c_file_common_key);
3016
3017 -- Inventory Item Attachment Handler
3018 xProgress := 'POCOB-20-1557';
3019 FOR v_org_id IN c_org_id(n_po_line_id) LOOP -- Value passed is the Line ID
3020 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
3021 v_pk1_value := v_org_id.ship_to_organization_id;
3022 ec_debug.pl(3,'Inventory Org ID: ',v_pk1_value);
3023
3024 xProgress := 'POCOB-20-1558';
3025 ece_poo_transaction.put_att_to_output_table(cCommunication_Method,
3026 cTransaction_Type,
3027 iOutput_width,
3028 iRun_id,
3029 9,
3030 10,
3031 cAtt_Header_Interface,
3032 cAtt_Detail_Interface,
3033 v_entity_name,
3034 'VENDOR',
3035 v_pk1_value,
3036 v_pk2_value,
3037 NULL,
3038 NULL,
3039 NULL,
3040 c_file_common_key);
3041 END IF;
3042 END LOOP;
3043
3044 -- **************************
3045 -- set LINE_NUMBER values
3046 -- **************************
3047 xProgress := 'POCOB-20-1560';
3048 dbms_sql.bind_variable(shipment_sel_c,'po_line_id',n_po_line_id);
3049
3050 xProgress := 'POCOB-20-1575';
3051 dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3052
3053 xProgress := 'POCOB-20-1580';
3054 dummy := dbms_sql.execute(shipment_sel_c);
3055
3056 -- ****************************
3057 -- Shipment loop starts here
3058 -- ****************************
3059 xProgress := 'POCOB-20-1590';
3060 WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipments
3061
3062 -- *********************************
3063 -- store values in pl/sql table
3064 -- *********************************
3065 xProgress := 'POCOB-20-1600';
3066 ece_flatfile_pvt.assign_column_value_to_tbl(shipment_sel_c,l_shipment_tbl);
3067
3068 xProgress := 'POCOB-20-1603';
3069 dbms_sql.column_value(shipment_sel_c,iShipment_count + 1,rShipment_rowid);
3070
3071 xProgress := 'POCOB-20-1606';
3072 dbms_sql.column_value(shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
3073
3074 xProgress := 'POCOB-20-1610';
3075 nLine_Location_ID := l_shipment_tbl(nLine_Location_ID_pos).value;
3076 ec_debug.pl(3,'Ship Level Line Location ID: ',nLine_Location_ID);
3077
3078 xProgress := 'POCOB-20-1620';
3079 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value ,' '),1,25),25) ||
3080 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value ,' '),1,22),22) ||
3081 RPAD(SUBSTRB(NVL(l_line_tbl(nLine_key_pos).value ,' '),1,22),22) ||
3082 RPAD(SUBSTRB(NVL(l_shipment_tbl(nShipment_key_pos).value,' '),1,22),22);
3083 ec_debug.pl(3, 'c_file_common_key: ',c_file_common_key);
3084
3085 xProgress := 'POCOB-20-1630';
3086 ece_poo_transaction.write_to_file(cTransaction_Type,
3087 cCommunication_Method,
3088 cShipment_Interface,
3089 l_shipment_tbl,
3090 iOutput_width,
3091 iRun_id,
3092 c_file_common_key,
3093 null);
3094
3095 -- Shipment Level Attachment Handler
3096 v_entity_name := 'PO_SHIPMENTS';
3097 v_pk1_value := nLine_Location_ID;
3098
3099 xProgress := 'POCOB-20-1632';
3100 ece_poo_transaction.put_att_to_output_table(cCommunication_Method,
3101 cTransaction_Type,
3102 iOutput_width,
3103 iRun_id,
3104 12,
3105 13,
3106 cAtt_Header_Interface,
3107 cAtt_Detail_Interface,
3108 v_entity_name,
3109 'VENDOR',
3110 v_pk1_value,
3111 NULL,
3112 NULL,
3113 NULL,
3114 NULL,
3115 c_file_common_key);
3116
3117 -- Project Level Handler
3118 xProgress := 'POCOB-20-1634';
3119 -- IF v_project_acct_status = 'I' THEN -- Project Accounting is Installed bug1891291
3120 ece_poo_transaction.put_distdata_to_out_tbl(
3121 cCommunication_Method,
3122 cTransaction_Type,
3123 iOutput_width,
3124 iRun_id,
3125 cProject_Interface,
3126 n_po_header_ID, -- PO_HEADER_ID
3127 nRelease_ID, -- PO_RELEASE_ID
3128 n_po_line_ID, -- PO_LINE_ID
3129 nLine_Location_ID, -- LINE_LOCATION_ID
3130 c_file_common_key);
3131 -- END IF;
3132
3133 xProgress := 'POCOB-20-1640';
3134 dbms_sql.bind_variable(shipment_del_c1,'col_rowid',rShipment_rowid);
3135
3136 xProgress := 'POCOB-20-1650';
3137 dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3138
3139 xProgress := 'POCOB-20-1660';
3140 dummy := dbms_sql.execute(shipment_del_c1);
3141
3142 xProgress := 'POCOB-20-1670';
3143 dummy := dbms_sql.execute(shipment_del_c2);
3144
3145 END LOOP; -- Shipment Level
3146
3147 xProgress := 'POCOB-20-1674';
3148 IF dbms_sql.last_row_count = 0 THEN
3149 v_LevelProcessed := 'SHIPMENT';
3150 ec_debug.pl(0,
3151 'EC',
3152 'ECE_NO_DB_ROW_PROCESSED',
3153 'PROGRESS_LEVEL',
3154 xProgress,
3155 'LEVEL_PROCESSED',
3156 v_LevelProcessed,
3157 'TRANSACTION_TYPE',
3158 cTransaction_Type);
3159 END IF;
3160
3161 -- *********************
3162 -- Use rowid for delete
3163 -- *********************
3164 xProgress := 'POCOB-20-1680';
3165 dbms_sql.bind_variable(line_del_c1,'col_rowid',rLine_rowid);
3166
3167 xProgress := 'POCOB-20-1690';
3168 dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3169
3170 xProgress := 'POCOB-20-1700';
3171 dummy := dbms_sql.execute(line_del_c1);
3172
3173 xProgress := 'POCOB-20-1710';
3174 dummy := dbms_sql.execute(line_del_c2);
3175
3176 END LOOP; -- Line Level
3177
3178 xProgress := 'POCOB-20-1714';
3179 IF dbms_sql.last_row_count = 0 THEN
3180 v_LevelProcessed := 'LINE';
3181 ec_debug.pl(0,
3182 'EC',
3183 'ECE_NO_DB_ROW_PROCESSED',
3184 'PROGRESS_LEVEL',
3185 xProgress,
3186 'LEVEL_PROCESSED',
3187 v_LevelProcessed,
3188 'TRANSACTION_TYPE',
3189 cTransaction_Type);
3190 END IF;
3191
3192 xProgress := 'POCOB-20-1720';
3193 dbms_sql.bind_variable(header_del_c1,'col_rowid',rHeader_rowid);
3194
3195 xProgress := 'POCOB-20-1730';
3196 dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3197
3198 xProgress := 'POCOB-20-1740';
3199 dummy := dbms_sql.execute(header_del_c1);
3200
3201 xProgress := 'POCOB-20-1750';
3202 dummy := dbms_sql.execute(header_del_c2);
3203
3204 END LOOP; -- Header Level
3205
3206 xProgress := 'POCOB-20-1754';
3207 IF dbms_sql.last_row_count = 0 THEN
3208 v_LevelProcessed := 'HEADER';
3209 ec_debug.pl(0,
3210 'EC',
3211 'ECE_NO_DB_ROW_PROCESSED',
3212 'PROGRESS_LEVEL',
3213 xProgress,
3214 'LEVEL_PROCESSED',
3215 v_LevelProcessed,
3216 'TRANSACTION_TYPE',
3217 cTransaction_Type);
3218 END IF;
3219
3220 xProgress := 'POCOB-20-1760';
3221 dbms_sql.close_cursor(header_sel_c);
3222
3223 xProgress := 'POCOB-20-1770';
3224 dbms_sql.close_cursor(line_sel_c);
3225
3226 xProgress := 'POCOB-20-1780';
3227 dbms_sql.close_cursor(shipment_sel_c);
3228
3229 xProgress := 'POCOB-20-1790';
3230 dbms_sql.close_cursor(header_del_c1);
3231
3232 xProgress := 'POCOB-20-1800';
3233 dbms_sql.close_cursor(line_del_c1);
3234
3235 xProgress := 'POCOB-20-1812';
3236 dbms_sql.close_cursor(shipment_del_c1);
3237
3238 xProgress := 'POCOB-20-1814';
3239 dbms_sql.close_cursor(header_del_c2);
3240
3241 xProgress := 'POCOB-20-1816';
3242 dbms_sql.close_cursor(line_del_c2);
3243
3244 xProgress := 'POCOB-20-1818';
3245 dbms_sql.close_cursor(shipment_del_c2);
3246
3247 -- Bug 2490109 Closing the distribution cursors.
3248 xProgress := 'POCOB-50-1819';
3249 if (ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3250 dbms_sql.close_cursor(ece_poo_transaction.project_sel_c);
3251
3252 xProgress := 'POCOB-50-1820';
3253 dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3254
3255 xProgress := 'POCOB-50-1821';
3256 dbms_sql.close_cursor(ece_poo_transaction.project_del_c2);
3257 end if;
3258
3259 xProgress := 'POCOB-20-1820';
3260 ec_debug.pop('ECE_POCO_TRANSACTION.PUT_DATA_TO_OUTPUT_TABLE');
3261
3262 EXCEPTION
3263 WHEN OTHERS THEN
3264 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
3265 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
3266
3267 app_exception.raise_exception;
3268
3269 END put_data_to_output_table;
3270
3271 END ece_poco_transaction;
3272