[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT location_id, object_version_number
FROM hr_locations
WHERE location_code = p_location_code;
HR_LOCATION_API.update_location(
p_effective_date => sysdate,
p_language_code => userenv('LANG'),
p_style => p_style,
p_description => p_description,
p_address_line_1 => p_addr_line_1,
p_region_1 => NULL,
p_region_2 => NULL,
p_town_or_city => NULL,
p_postal_code => NULL,
p_country => p_country,
p_location_id => l_location_id,
p_object_version_number => l_obj_ver_num );
itg_debug_pub.Add('HR_LOCATION_API.update_location returns normal' ,1);
SELECT tp_header_id
FROM ecx_tp_headers
WHERE party_type = p_party_type
AND party_id = p_party_id
AND party_site_id = p_party_site_id;
SELECT location_id
INTO l_loc_id
FROM hr_locations_all
WHERE location_code = p_location_code;
ecx_tp_api.update_trading_partner(
x_return_status => l_retcode,
x_msg => l_retmesg,
p_tp_header_id => l_tp_hdr_id,
p_company_admin_email => p_email_id
);
itg_debug_pub.Add('ecx_tp_api.update_trading_partner returns normal',1);
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_PO_RELEASE','OAG','ITG','SYNC_PO_RELEASE','OUT',
'itg_sync_po_release_007_out','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','CANCEL_PO_RELEASE','OAG','ITG','CANCEL_PO_RELEASE','OUT',
'itg_cancel_po_release_006_out','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','CANCEL_PO','OAG','ITG','CANCEL_PO','OUT',
'itg_cancel_po_006_out','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','ADD_REQUISITN','OAG','ITG','ADD_REQUISITN','OUT',
'itg_add_requisitn_005_out','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','CHANGE_REQUISITN','OAG','ITG','CHANGE_REQUISITN','OUT',
'itg_change_requisitn_005_out','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','CANCEL_REQUISITN','OAG','ITG','CANCEL_REQUISITN','OUT',
'itg_cancel_requisitn_005','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_PO','OAG','ITG','SYNC_PO','OUT',
'itg_sync_po_007_out','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','UPDATE_DELIVERY','OAG','ITG','UPDATE_DELIVERY','OUT',
'itg_update_delivery_005','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
itg_debug_pub.Add('UPDATE_DELIVERY - ' || l_retcode || l_retmesg, 1);
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','LOAD_PLINVOICE','OAG','ITG','LOAD_PLINVOICE','OUT',
'itg_load_plinvoice_004','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
itg_debug_pub.Add('UPDATE_DELIVERY - ' || l_retcode || l_retmesg, 1);
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ECX','CBODO','OAG','BOD','CONFIRM','OUT',
'ECX_CBODO_OAG72_OUT_CONFIRM','DIRECT',null,'ITG03','129.0.0.1',
'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_FIELD_004','OAG','ITG','SYNC_FIELD_004','IN',
'itg_sync_field_004_in',null,null,null,null,
null,null,null,'ITG03',null,0,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_UOMGROUP_003','OAG','ITG','SYNC_UOMGROUP_003','IN',
'itg_sync_uomgroup_003_in',null,null,null,null,
null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_SUPPLIER_005','OAG','ITG','SYNC_SUPPLIER_005','IN',
'itg_sync_supplier_005_in',null,null,null,null,
null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_EXCHNGRATE_003','OAG','ITG','SYNC_EXCHNGRATE_003','IN',
'itg_sync_exchngrate_003_in',null,null,null,null,
null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_ITEM_006','OAG','ITG','SYNC_ITEM_006','IN',
'itg_Sync_Item_006_in',null,null,null,null,
null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_COA_003','OAG','ITG','SYNC_COA_003','IN',
'itg_sync_coa_003_in',null,null,null,null,
null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'ITG','SYNC_PO','OAG','ITG','SYNC_PO_007','IN',
'ITG_sync_po_007_in',null,null,null,null,
null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
'CLN','NBOD','OAG','CLN','NBOD','IN',
'CLN_NBODI_OAG72_IN_CONFIRM',null,null,null,null,
null,null,null,'ITG03',null,0,p_tp_hdr_id,'I');
PROCEDURE add_or_update_tp_detail(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
x_tp_dtl_id NUMBER,
p_txn_type VARCHAR2,
p_txn_subtype VARCHAR2,
p_std_code VARCHAR2,
p_ext_type VARCHAR2,
p_ext_subtype VARCHAR2,
p_direction VARCHAR2,
p_map VARCHAR2,
p_conn_type VARCHAR2,
p_hub_user_id NUMBER,
p_protocol VARCHAR2,
p_protocol_addr VARCHAR2,
p_user VARCHAR2,
p_passwd VARCHAR2,
p_routing_id NUMBER,
p_src_loc VARCHAR2,
p_ext_loc VARCHAR2,
p_doc_conf NUMBER,
p_tp_hdr_id NUMBER,
p_party_type VARCHAR2
)
IS
CURSOR get_tp_detail_id(
p_tp_hdr_id VARCHAR2,
p_standard_code VARCHAR2,
p_ext_type VARCHAR2,
p_ext_subtype VARCHAR2,
p_direction VARCHAR2,
p_txn_type VARCHAR2,
p_txn_subtype VARCHAR2
)IS
SELECT tp_detail_id
FROM
ecx_tp_details tpd,
ecx_tp_headers tph,
ecx_ext_processes extp,
ecx_transactions txn,
ecx_standards svl
WHERE 1=1
AND tph.tp_header_id = tpd.tp_header_id
AND tpd.ext_process_id = extp.ext_process_id
AND extp.transaction_id = txn.transaction_id
AND extp.standard_id = svl.standard_id
AND svl.standard_code = p_std_code
AND extp.ext_type = p_ext_type
AND extp.ext_subtype = p_ext_subtype
AND extp.direction = p_direction
AND txn.transaction_type = p_txn_type
AND txn.transaction_subtype = p_txn_subtype
AND tph.tp_header_id = p_tp_hdr_id;
G_EXEC_PROC_NAME := 'add_or_update_tp_detail';
itg_debug_pub.Add('ENTERING add_or_update_tp_detail API' ,2);
itg_debug_pub.Add('Procedure add_or_update_tp_detail with params' ,1 );
SELECT extp.ext_process_id
INTO l_ext_process_id
FROM ecx_ext_processes extp,
ecx_transactions txn,
ecx_standards svl
WHERE 1=1
AND extp.transaction_id = txn.transaction_id
AND extp.standard_id = svl.standard_id
AND svl.standard_code = p_std_code
AND extp.ext_type = p_ext_type
AND extp.ext_subtype = p_ext_subtype
AND extp.direction = p_direction
AND txn.party_type = p_party_type
AND txn.transaction_type = p_txn_type
AND txn.transaction_subtype = p_txn_subtype;
SELECT count(*)
INTO l_count
FROM ecx_ext_processes extp,
ecx_tp_details tpd,
ecx_standards svl
WHERE 1=1
AND extp.standard_id = svl.standard_id
AND svl.standard_code = p_std_code
AND extp.ext_process_id = tpd.ext_process_id
AND extp.ext_type = p_ext_type
AND extp.ext_subtype = p_ext_subtype
AND extp.direction = p_direction
AND tpd.source_tp_location_code = p_src_loc
AND tpd.tp_header_id <> p_tp_hdr_id;
|| '), please delete this record and resubmit the Setup program.';
ecx_tp_api.update_tp_detail(
x_return_status => l_retcode,
x_msg => l_retmesg,
p_tp_detail_id => l_tp_dtl_id,
p_map_code => p_map,
p_ext_process_id => l_ext_process_id,
p_connection_type => p_conn_type,
p_hub_user_id => p_hub_user_id,
p_protocol_type => p_protocol,
p_protocol_address => p_protocol_addr,
p_username => p_user,
p_password => p_passwd,
p_routing_id => p_routing_id,
p_source_tp_location_code => p_src_loc,
p_external_tp_location_code => p_ext_loc,
p_confirmation => p_doc_conf,
p_passupd_flag => 'Y'
);
itg_debug_pub.Add('ecx_tp_api.update_tp_detail returns normal',1);
itg_debug_pub.Add('EXITING itg_setup.add_or_update_tp_detail returns normal', 2);
itg_debug_pub.Add('EXITING itg_setup.add_or_update_tp_detail fails,returns on exception',6);