[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_SYSTERM_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;
ec_debug.pl(3,'cHeader_select: ',cHeader_select);
cLine_select := cLine_select ||
cLine_from ||
cLine_where;
ec_debug.pl(3,'cLine_select: ',cLine_select);
cShipment_select := cShipment_select ||
cShipment_from ||
cShipment_where;
ec_debug.pl(3,'cShipment_select: ',cShipment_select);
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);
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_poco_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_poco_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); */ -- 2823215
UPDATE ece_po_interface_lines
SET line_part_number = cline_part_number,
line_part_attrib_category = cline_part_attrib_category,
line_part_attribute1 = cline_part_attribute1,
line_part_attribute2 = cline_part_attribute2,
line_part_attribute3 = cline_part_attribute3,
line_part_attribute4 = cline_part_attribute4,
line_part_attribute5 = cline_part_attribute5,
line_part_attribute6 = cline_part_attribute6,
line_part_attribute7 = cline_part_attribute7,
line_part_attribute8 = cline_part_attribute8,
line_part_attribute9 = cline_part_attribute9,
line_part_attribute10 = cline_part_attribute10,
line_part_attribute11 = cline_part_attribute11,
line_part_attribute12 = cline_part_attribute12,
line_part_attribute13 = cline_part_attribute13,
line_part_attribute14 = cline_part_attribute14,
line_part_attribute15 = cline_part_attribute15
WHERE transaction_record_id = l_line_fkey; */
/* If Header is already updated with cancel date from line, then no need
to update again */
if counter = 1 then
/* update ece_po_interface_headers set
po_cancelled_date = cancel_date_value
where po_header_id = l_header_tbl(nHeader_key_pos).value; */
ec_debug.pl(0,'EC','ECE_NO_ROW_UPDATED','PROGRESS_LEVEL',xProgress,'INFO','LINE PART','TABLE_NAME','ECE_PO_INTERFACE_LINES');
SELECT ece_poco_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';
ec_debug.pl(3,'cHeader_delete1: ',cHeader_delete1);
cLine_delete1 := 'DELETE FROM ' || cLine_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cLine_delete1: ',cLine_delete1);
cShipment_delete1 := 'DELETE FROM ' || cShipment_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cShipment_delete1: ',cShipment_delete1);
cHeader_delete2 := 'DELETE FROM ' || cHeader_X_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cHeader_delete2: ',cHeader_delete2);
cLine_delete2 := 'DELETE FROM ' || cLine_X_Interface || ' WHERE rowid = :col_rowid';
ec_debug.pl(3,'cLine_delete2: ',cLine_delete2);
cShipment_delete2 := 'DELETE FROM ' || cShipment_X_Interface || ' WHERE rowid = :col_rowid';
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;