[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text
FROM ece_output
WHERE run_id = iRun_id
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL
INTO iRun_id
FROM DUAL;
select count(*)
into xHeaderCount
from ECE_PO_INTERFACE_HEADERS
where run_id = iRun_id;
cHeader_select VARCHAR2(32000);
cLine_select VARCHAR2(32000);
cShipment_select VARCHAR2(32000);
SELECT DISTINCT ship_to_organization_id
FROM po_line_locations
WHERE po_line_id = p_line_id;
SELECT inventory_organization_id
INTO norganization_id
FROM financials_system_parameters;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'INVENTORY ORGANIZATION ID',
'TABLE_NAME',
'FINANCIALS_SYSTEM_PARAMETERS');
SELECT map_id
INTO iMap_ID
FROM ece_mappings
WHERE map_code = 'EC_' || cTransaction_Type || '_FF';
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
cHeader_select,
cHeader_from,
cHeader_where);
SELECT eit.key_column_name
INTO c_header_common_key_name
FROM ece_interface_tables eit
WHERE eit.transaction_type = cTransaction_Type AND
eit.interface_table_name = cHeader_Interface AND
eit.map_id = iMap_ID;
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cLine_Interface,
l_line_tbl,
cLine_select,
cLine_from,
cLine_where);
SELECT eit.key_column_name
INTO c_line_common_key_name
FROM ece_interface_tables eit
WHERE eit.transaction_type = cTransaction_Type AND
eit.interface_table_name = cLine_Interface AND
eit.map_id = iMap_ID;
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cShipment_Interface,
l_shipment_tbl,
cShipment_select,
cShipment_from,
cShipment_where);
SELECT eit.key_column_name
INTO c_shipment_key_name
FROM ece_interface_tables eit
WHERE eit.transaction_type = cTransaction_Type AND
eit.interface_table_name = cShipment_Interface AND
eit.map_id = iMap_ID;
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where;
cLine_select := cLine_select ||
cLine_from ||
cLine_where;
cShipment_select := cShipment_select ||
cShipment_from ||
cShipment_where;
ec_debug.pl(3,'cHeader_select: ',cHeader_select);
ec_debug.pl(3,'cLine_select: ',cLine_select);
ec_debug.pl(3,'cShipment_select: ',cShipment_select);
cHeader_select,
dbms_sql.native);
cHeader_select);
cLine_select,
dbms_sql.native);
cLine_select);
cShipment_select,
dbms_sql.native);
cShipment_select);
ece_flatfile_pvt.define_interface_column_type(header_sel_c,cHeader_select,ece_extract_utils_PUB.G_MaxColWidth,l_header_tbl);
ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_extract_utils_PUB.G_MaxColWidth,l_line_tbl);
ece_flatfile_pvt.define_interface_column_type(shipment_sel_c,cShipment_select,ece_extract_utils_PUB.G_MaxColWidth,l_shipment_tbl);
SELECT DECODE(l_header_tbl(nDocument_type_pos).value,
'BLANKET' ,'NB',
'STANDARD' ,'NS',
'PLANNED' ,'NP',
'RELEASE' ,'NR',
'BLANKET RELEASE' ,'NR',
'CONTRACT' ,'NC',
'NR')
INTO l_document_type
FROM DUAL;
ece_poo_transaction.update_po(l_document_type,
l_header_tbl(nPO_Number_pos).value,
l_header_tbl(nPO_type_pos).value,
l_header_tbl(nRelease_num_pos).value);
SELECT ece_poo_header_s.NEXTVAL
INTO l_header_fkey
FROM DUAL;
/* ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,
cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
l_header_fkey); */
select uom_code into l_line_tbl(nLine_uom_pos).value
from mtl_units_of_measure
where unit_of_measure = l_line_tbl(nLine_uom_code_pos).value;
SELECT ece_poo_line_s.NEXTVAL INTO l_line_fkey
FROM DUAL;
ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,cTransaction_Type,cCommunication_Method,cLine_Interface,l_line_tbl,l_line_fkey); */
SELECT ece_poo_shipment_s.NEXTVAL INTO l_shipment_fkey
FROM DUAL;
ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,cTransaction_Type,cCommunication_Method,cShipment_Interface,l_shipment_tbl,l_shipment_fkey); */
cHeader_select VARCHAR2(32000);
cLine_select VARCHAR2(32000);
cShipment_select VARCHAR2(32000);
cHeader_delete1 VARCHAR2(32000);
cLine_delete1 VARCHAR2(32000);
cShipment_delete1 VARCHAR2(32000);
cHeader_delete2 VARCHAR2(32000);
cLine_delete2 VARCHAR2(32000);
cShipment_delete2 VARCHAR2(32000);
SELECT DISTINCT ship_to_organization_id
FROM po_line_locations
WHERE po_line_id = p_line_id;
SELECT inventory_organization_id
INTO norganization_id
FROM financials_system_parameters;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'INVENTORY ORGANIZATION ID',
'TABLE_NAME',
'FINANCIALS_SYSTEM_PARAMETERS');
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
cHeader_X_Interface,
l_header_tbl,
c_header_common_key_name,
cHeader_select,
cHeader_from,
cHeader_where);
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cLine_Interface,
cLine_X_Interface,
l_line_tbl,
c_line_common_key_name,
cLine_select,
cLine_from,
cLine_where);
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cShipment_Interface,
cShipment_X_Interface,
l_shipment_tbl,
c_shipment_key_name,
cShipment_select,
cShipment_from,
cShipment_where);
cHeader_select := cHeader_select ||
',' ||
cHeader_Interface ||
'.rowid,' ||
cHeader_X_Interface ||
'.rowid,' ||
cHeader_Interface ||
'.po_header_id,' ||
cHeader_Interface ||
'.release_number ';
cLine_select := cLine_select ||
',' ||
cLine_Interface ||
'.rowid,' ||
cLine_X_Interface ||
'.rowid,' ||
cLine_Interface ||
'.po_line_id,' ||
cLine_Interface ||
'.line_number ';
cShipment_select := cShipment_select ||
',' ||
cShipment_Interface ||
'.rowid,' ||
cShipment_X_Interface ||
'.rowid,' ||
cShipment_Interface ||
'.shipment_number ';
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where ||
' ORDER BY ' ||
cHeader_Interface ||
'.po_header_id,' ||
cHeader_Interface ||
'.release_number ' ||
' FOR UPDATE';
ec_debug.pl(3,'cHeader_select: ',cHeader_select);
cLine_select := cLine_select ||
cLine_from ||
cLine_where ||
' ORDER BY ' ||
cLine_Interface ||
'.line_number ' ||
' FOR UPDATE';
ec_debug.pl(3, 'cLine_select: ',cLine_select);
cShipment_select := cShipment_select ||
cShipment_from ||
cShipment_where ||
' ORDER BY ' ||
cShipment_Interface ||
'.shipment_number ' ||
' FOR UPDATE';
ec_debug.pl(3, 'cShipment_select: ',cShipment_select);
cHeader_delete1 := 'DELETE FROM ' || cHeader_Interface || ' WHERE rowid = :col_rowid';
cLine_delete1 := 'DELETE FROM ' || cLine_Interface || ' WHERE rowid = :col_rowid';
cShipment_delete1 := 'DELETE FROM ' || cShipment_Interface || ' WHERE rowid = :col_rowid';
cHeader_delete2 := 'DELETE FROM ' || cHeader_X_Interface || ' WHERE rowid = :col_rowid';
cLine_delete2 := 'DELETE FROM ' || cLine_X_Interface || ' WHERE rowid = :col_rowid';
cShipment_delete2 := 'DELETE FROM ' || cShipment_X_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cHeader_delete1: ',cHeader_delete1);
ec_debug.pl(3,'cLine_delete1: ',cLine_delete1);
ec_debug.pl(3,'cShipment_delete1: ',cShipment_delete1);
ec_debug.pl(3,'cHeader_delete2: ',cHeader_delete2);
ec_debug.pl(3,'cLine_delete2: ',cLine_delete2);
ec_debug.pl(3,'cShipment_delete2: ',cShipment_delete2);
dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
dbms_sql.parse(header_del_c1,cHeader_delete1,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
dbms_sql.parse(line_del_c1,cLine_delete1,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete1);
dbms_sql.parse(shipment_del_c1,cShipment_delete1,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete1);
dbms_sql.parse(header_del_c2,cHeader_delete2,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
dbms_sql.parse(line_del_c2,cLine_delete2,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete2);
dbms_sql.parse(shipment_del_c2,cShipment_delete2,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete2);
cHeader_select,
ece_flatfile_pvt.G_MaxColWidth,
l_header_tbl);
ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_flatfile_pvt.G_MaxColWidth,l_line_tbl);
ece_flatfile_pvt.define_interface_column_type(Shipment_sel_c,cShipment_select,ece_flatfile_pvt.G_MaxColWidth,l_shipment_tbl);
SELECT DECODE(l_header_tbl(nDocument_type_pos).value,
'BLANKET' ,'NB',
'STANDARD' ,'NS',
'PLANNED' ,'NP',
'RELEASE' ,'NR',
'BLANKET RELEASE' ,'NR',
'CONTRACT' ,'NC',
'NR')
INTO l_document_type
FROM DUAL;
PROCEDURE update_po(
document_type IN VARCHAR2,
po_number IN VARCHAR2,
po_type IN VARCHAR2,
release_number IN VARCHAR2) IS
xProgress VARCHAR2(80);
ec_debug.push('ECE_POO_TRANSACTION.UPDATE_PO');
SELECT po_header_id
INTO l_header_id
FROM po_headers
WHERE segment1 = po_number AND
type_lookup_code = po_type;
ec_debug.pl(0,'EC','ECE_NO_ROW_SELECTED','PROGRESS_LEVEL',xProgress,'INFO','PO HEADER ID','TABLE_NAME','PO_HEADERS');
UPDATE po_headers
SET last_update_date = SYSDATE,
printed_date = SYSDATE,
print_count = NVL(print_count,0) + 1,
edi_processed_flag = 'Y'
WHERE po_header_id = l_header_id;
ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_HEADERS');
UPDATE po_headers_archive
SET last_update_date = SYSDATE,
printed_date = SYSDATE,
print_count = NVL(print_count,0) + 1,
edi_processed_flag = 'Y'
WHERE po_header_id = l_header_id AND
latest_external_flag = 'Y';
ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_HEADERS_ARCHIVE');
SELECT po_release_id INTO l_release_id
FROM po_releases
WHERE release_num = release_number AND
po_header_id = l_header_id;
ec_debug.pl(0,'EC','ECE_NO_ROW_SELECTED','PROGRESS_LEVEL',xProgress,'INFO','PO RELEASE ID','TABLE_NAME','PO_RELEASES');
UPDATE po_releases
SET last_update_date = SYSDATE,
printed_date = SYSDATE,
print_count = NVL(print_count,0) + 1,
edi_processed_flag = 'Y'
WHERE po_release_id = l_release_id;
ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_RELEASES');
UPDATE po_releases_archive
SET last_update_date = SYSDATE,
printed_date = SYSDATE,
print_count = NVL(print_count,0) + 1,
edi_processed_flag = 'Y'
WHERE po_release_id = l_release_id AND
latest_external_flag = 'Y';
ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','EDI PROCESSED','TABLE_NAME','PO_RELEASES_ARCHIVE');
SELECT DECODE
(document_type,
'NS','PO',
'NC','PO',
'CS','PO',
'NB','PA',
'CB','PA',
'NP','PO',
'CP','PO',
'NR','RELEASE',
'CR','RELEASE'),
DECODE
(document_type,
'NR',DECODE
(po_type,
'PLANNED','SCHEDULED',
'BLANKET','BLANKET'),
'CR',DECODE(po_type,
'PLANNED','SCHEDULED',
'BLANKET','BLANKET'),
po_type)
INTO l_document_type,l_document_subtype
FROM DUAL;
ec_debug.pop('ECE_POO_TRANSACTION.UPDATE_PO');
END update_po;
cProject_select VARCHAR2(32000);
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cDistribution_Interface,
l_project_tbl,
cProject_select,
cProject_from,
cProject_where);
cProject_select := cProject_select ||
cProject_from ||
cProject_where;
ec_debug.pl(3,'cProject_select: ',cProject_select);
cProject_select,
dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
ece_flatfile_pvt.define_interface_column_type(project_sel_c,cProject_select,ece_extract_utils_PUB.G_MaxColWidth,l_project_tbl);
SELECT ece_po_project_info_s.NEXTVAL INTO l_project_fkey
FROM DUAL;
ece_extract_utils_pub.insert_into_interface_tbl(
iRun_id,
cTransaction_Type,
cCommunication_Method,
cDistribution_Interface,
l_project_tbl,
l_project_fkey); */
/* Update ECE_PO_DISTRIBUTIONS
with project related data
based on task_id and project_id
*/
xProgress := 'POOB-40-1240';
/* select task_id,project_id into
c_task_id,c_project_id
from
ECE_PO_DISTRIBUTIONS EPID
where
EPID.TRANSACTION_RECORD_ID = l_project_fkey; */
select PPE.PROJECT_NUMBER,PPE.PROJECT_TYPE,
PAT.TASK_NUMBER INTO
c_PROJECT_NUMBER,c_PROJECT_TYPE,
c_TASK_NUMBER
FROM
PA_PROJECTS_EXPEND_V
PPE,
PA_TASKS
PAT,
ECE_PO_DISTRIBUTIONS
EPID
WHERE
EPID.TASK_ID = PAT.TASK_ID (+) AND
EPID.PROJECT_ID = PAT.PROJECT_ID (+) AND
EPID.PROJECT_ID = PPE.PROJECT_ID (+) AND
EPID.TRANSACTION_RECORD_ID = l_project_fkey;
/* UPDATE ECE_PO_DISTRIBUTIONS EPID
SET EPID.PROJECT_NUMBER = c_PROJECT_NUMBER,
EPID.PROJECT_TYPE = c_PROJECT_TYPE,
EPID.TASK_NUMBER = c_TASK_NUMBER
WHERE EPID.TRANSACTION_RECORD_ID = l_project_fkey; */
cProject_select VARCHAR2(32000);
cProject_delete1 VARCHAR2(32000);
cProject_delete2 VARCHAR2(32000);
ece_poo_transaction.l_project_tbl.DELETE; -- Bug 2490109
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cDistribution_Interface,
cProject_X_Interface,
l_project_tbl,
c_project_common_key_name,
cProject_select,
cProject_from,
cProject_where,
iProject_output_level);
cProject_select := cProject_select || ',' ||
cDistribution_Interface || '.rowid' || ',' ||
cProject_X_Interface || '.rowid ';
cProject_select := cProject_select ||
cProject_from ||
cProject_where ||
' FOR UPDATE';
ec_debug.pl(3,'cProject_select: ',cProject_select);
cProject_delete1 := 'DELETE FROM ' || cDistribution_Interface || ' WHERE rowid = :col_rowid';
cProject_delete2 := 'DELETE FROM ' || cProject_X_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cProject_delete1: ',cProject_delete1);
ec_debug.pl(3,'cProject_delete2: ',cProject_delete2);
dbms_sql.parse(project_sel_c,cProject_select,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
dbms_sql.parse(project_del_c1,cProject_delete1,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete1);
dbms_sql.parse(project_del_c2,cProject_delete2,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete2);
cProject_select,
ece_flatfile_pvt.G_MaxColWidth,
l_project_tbl);
v_att_header_select VARCHAR2(32000);
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cAtt_Header_Interface,
l_att_header_tbl,
v_att_header_select,
v_att_header_from,
v_att_header_where);
v_att_header_select := v_att_header_select || v_att_header_from || v_att_header_where;
ec_debug.pl(3,'v_att_header_select: ',v_att_header_select);
dbms_sql.parse(n_att_header_sel_c,v_att_header_select,dbms_sql.native);
v_att_header_select);
v_att_header_select,
ece_extract_utils_pub.G_MAXCOLWIDTH,
l_att_header_tbl);
SELECT ece_attachment_headers_s.NEXTVAL INTO n_header_fkey
FROM DUAL;
/* ece_extract_utils_pub.insert_into_interface_tbl(iRun_id,
cTransaction_Type,
cCommunication_Method,
cAtt_Header_Interface,
l_att_header_tbl,
n_header_fkey); */
v_att_detail_select VARCHAR2(32000) := 'SELECT ';
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cAtt_Detail_Interface ,
l_att_detail_tbl,
v_att_detail_select,
v_att_detail_from,
v_att_detail_where);
v_att_detail_select := v_att_detail_select || ',short_text';
v_att_detail_select := v_att_detail_select || ',long_text';
v_att_detail_select := v_att_detail_select || v_att_detail_from || v_att_detail_where;
ec_debug.pl(3,'v_att_detail_select: ',v_att_detail_select);
dbms_sql.parse(n_att_detail_sel_c,v_att_detail_select,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,v_att_detail_select);
v_att_detail_select,
ece_extract_utils_pub.G_MAXCOLWIDTH,
l_att_detail_tbl);
/* dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,ece_extract_utils_pub.G_MAXCOLWIDTH);*/
dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
SELECT ece_attachment_details_s.NEXTVAL INTO n_detail_fkey
FROM DUAL;
/* ece_extract_utils_pub.insert_into_interface_tbl(
iRun_id,
cTransaction_Type,
cCommunication_Method,
cAtt_Detail_Interface,
l_att_detail_tbl,
n_detail_fkey); */
cHeader_select VARCHAR2(32000);
cHeader_delete1 VARCHAR2(32000);
cHeader_delete2 VARCHAR2(32000);
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
cHeader_X_Interface,
l_header_tbl,
c_header_common_key_name,
cHeader_select,
cHeader_from,
cHeader_where,
cHeader_Output_Level);
select count(distinct(att_seq_num)) into get_no_rows
from ece_attachment_headers
where pk1_value = cPK1_value
and pk2_value = cPK2_value
and entity_name like 'MTL_SYSTEM_ITEMS';
select count(distinct(attached_document_id)) into get_no_lrows
from ece_attachment_headers
where pk1_value = cPK1_value
and entity_name like 'PO_LINES';
cHeader_select := cHeader_select || ',' ||
cHeader_Interface || '.rowid,' ||
cHeader_X_Interface || '.rowid ';
cHeader_select := cHeader_select || cHeader_from || cHeader_where || ' FOR UPDATE';
ec_debug.pl(3,'cHeader_select: ',cHeader_select);
cHeader_delete1 := 'DELETE FROM ' || cHeader_Interface || ' WHERE rowid = :col_rowid';
cHeader_delete2 := 'DELETE FROM ' || cHeader_X_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cHeader_delete1: ',cHeader_delete1);
ec_debug.pl(3,'cHeader_delete2: ',cHeader_delete2);
dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
dbms_sql.parse(Header_del_c1,cHeader_delete1,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
dbms_sql.parse(Header_del_c2,cHeader_delete2,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
cHeader_select,
ece_flatfile_pvt.G_MaxColWidth,
l_header_tbl );
cDetail_select VARCHAR2(32000);
cDetail_delete1 VARCHAR2(32000);
cDetail_delete2 VARCHAR2(32000);
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cDetail_Interface,
cDetail_X_Interface,
l_detail_tbl,
c_detail_common_key_name,
cDetail_select,
cDetail_from,
cDetail_where,
cDetail_Output_Level);
select max(segment_number) into get_no_rows
from ece_attachment_details
where pk1_value = cPK1_value
and pk2_value = cPK2_value
and entity_name like 'MTL_SYSTEM_ITEMS'
and attached_document_id = catt_doc_id;
select count(distinct(segment_number)) into get_no_lrows
from ece_attachment_details
where pk1_value = cPK1_value
and entity_name like 'PO_LINES'
and attached_document_id = catt_doc_id;
cDetail_select := cDetail_select || ', ' ||
cDetail_Interface || '.rowid, ' ||
cDetail_X_Interface || '.rowid ';
cDetail_select := cDetail_select || cDetail_from || cDetail_where || ' FOR UPDATE';
cDetail_delete1 := 'DELETE FROM ' || cDetail_Interface || ' WHERE rowid = :col_rowid';
cDetail_delete2 := 'DELETE FROM ' || cDetail_X_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cDetail_select: ',cDetail_select);
ec_debug.pl(3,'cDetail_delete1: ',cDetail_delete1);
ec_debug.pl(3,'cDetail_delete2: ',cDetail_delete2);
dbms_sql.parse(Detail_sel_c,cDetail_select,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_select);
dbms_sql.parse(Detail_del_c1,cDetail_delete1,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete1);
dbms_sql.parse(Detail_del_c2,cDetail_delete2,dbms_sql.native);
ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete2);
cDetail_select,
ece_flatfile_pvt.G_MaxColWidth,
l_detail_tbl);
cInsert_stmt VARCHAR2(32000);
cInsert_stmt := cInsert_stmt || substrb(rpad(nvl(cValue,' '),
TO_CHAR(p_Interface_tbl(i).data_length),' '),1,
p_Interface_tbl(i).data_length);
cInsert_stmt := l_common_key || LPAD(NVL(p_Interface_tbl(iRow_num).Record_num,0),4,'0') ||
RPAD(NVL(p_Interface_tbl(iRow_num).layout_code,' '),2) ||
RPAD(NVL(p_Interface_tbl(iRow_num).record_qualifier,' '),3) || cInsert_stmt;
utl_file.put_line(ece_poo_transaction.uFile_type,cInsert_stmt);
cInsert_stmt := NULL;
cInsert_stmt := l_common_key || LPAD(NVL(p_Interface_tbl(iRow_num).Record_num,0),4,'0') ||
RPAD(NVL(p_Interface_tbl(iRow_num).layout_code,' '),2) ||
RPAD(NVL(p_Interface_tbl(iRow_num).record_qualifier,' '),3) || cInsert_stmt;
utl_file.put_line(ece_poo_transaction.uFile_type,cInsert_stmt);