Question

User pictures from AD

  • 27 January 2020
  • 5 replies
  • 163 views

Userlevel 3
Badge +4

Hi!

Have anyone done a integration between Active Director and IFS10, where the user pictures are imported?

We have user pictures in AD and would like to have the same in IFS10.

 

/Karl


5 replies

Userlevel 2
Badge +4

I’m not aware of such a solution throughout my years but I guess it should be possible to do any of the following:

  1. Write a c# application that does this or
  2. Use PowerShell and import pictures into IFS using eg data migration
  3. Use some RPA tool out there
  4. Use Azure logic apps or BizTalk
  5. Write a PLSQL script that reads from AD and imports / updates it
Badge

Hi Karl.

 

Did you find any solution to use pictures from AD?

 

br

Tobias

Userlevel 6
Badge +12

Here's a complete solution. I’ve only tested this in IFS Enterprise Explorer in Apps 9.

 

Prerequisite: Oracle Directory

CREATE DIRECTORY ad_picture AS '\\some_server_name\some_directory_name';

 

Step 1: PowerShell

Using the Remote Server Administration Tools, export each user's picture as an individual file.

(To ensure removals cascade, you’ll want to empty the target directory first.)

$output_directory = '\\some_server_name\some_directory_name'
$ad_group_name = 'some_ad_group_containing_your_ifs_users'
$user_pictures = Get-ADGroupMember -Identity $ad_group_name | Get-ADUser -Property thumbnailphoto
$user_pictures = $user_pictures | Where-Object thumbnailphoto
$user_pictures | ? {Set-Content "$($output_directory)\$($_.samaccountname.ToLower()).png" -Value $_.thumbnailphoto -Encoding Byte}

 

Step 2: PL/SQL

This assumes your Person IDs are the same as your User IDs. Missing pictures are nullified to ensure AD removals cascade.

SET ECHO ON
SET TERMOUT ON
SET LINESIZE 32767
SET PAGESIZE 32767
SET SQLBLANKLINES ON
SET TAB OFF
SET TRIMSPOOL ON
SET VERIFY ON
SET TIMING ON
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK ON

DECLARE
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2);

PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2)
IS
fileopen_failed_exc_ EXCEPTION;
PRAGMA EXCEPTION_INIT (fileopen_failed_exc_, -22288);
file_name_ VARCHAR2(32767);
picture_blob_ BLOB;
picture_bfile_ BFILE;
length_ NUMBER;
info_ VARCHAR2(32767);
objid_ VARCHAR2(32767);
objversion_ VARCHAR2(32767);
attr_ VARCHAR2(32767);
blob_id_ binary_object.blob_id%TYPE;
dest_offset_ NUMBER := 1;
src_offset_ NUMBER := 1;
BEGIN
language_sys.set_language('en');

file_name_ := LOWER(user_id_) || '.png';
picture_bfile_ := BFILENAME('AD_PICTURE', file_name_);
BEGIN
dbms_lob.fileopen(picture_bfile_, dbms_lob.file_readonly);
dbms_lob.createtemporary(picture_blob_, TRUE);
dbms_lob.loadblobfromfile (
dest_lob => picture_blob_,
src_bfile => picture_bfile_,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset_,
src_offset => src_offset_);
dbms_lob.fileclose(picture_bfile_);
EXCEPTION WHEN fileopen_failed_exc_ THEN
picture_blob_ := NULL;
dbms_lob.fileclose(picture_bfile_);
END;
length_ := dbms_lob.getlength(picture_blob_);

SELECT picture_id
INTO blob_id_
FROM person_info
WHERE person_id = UPPER(user_id_);

binary_object_api.create_or_replace(
blob_id_ /* IN OUT */ => blob_id_,
display_text_ /* IN */ => file_name_,
file_name_ /* IN */ => file_name_,
file_path_ /* IN */ => 'C:\',
external_storage_ /* IN */ => 'FALSE',
length_ /* IN */ => length_,
type_ /* IN */ => 'PICTURE',
application_data_ /* IN */ => '7');
binary_object_data_block_api.new__(
objversion_ /* OUT */ => objversion_,
objid_ /* OUT */ => objid_,
blob_id_ /* IN */ => blob_id_,
application_data_ /* IN */ => '7');
binary_object_data_block_api.write_data__(
objversion_ /* IN OUT */ => objversion_,
rowid_ /* IN */ => objid_,
lob_loc_ /* IN */ => picture_blob_);

SELECT objid,
objversion
INTO objid_,
objversion_
FROM person_info
WHERE person_id = UPPER(user_id_);
client_sys.clear_attr(attr_);
client_sys.add_to_attr('PICTURE_ID', blob_id_, attr_);
person_info_api.modify__(info_, objid_, objversion_, attr_, 'DO');
END set_ad_picture;
BEGIN
FOR rec_ IN (
SELECT fu.identity
FROM fnd_user fu
JOIN person_info pi
ON pi.person_id = fu.identity
AND pi.user_id = fu.identity
ORDER BY 1
) LOOP
set_ad_picture(rec_.identity);
END LOOP;
END;
/

COMMIT;

EXIT;

 

Userlevel 6
Badge +12

(I recommend this gets moved to the Technology forum.)

Userlevel 6
Badge +12

This updated version works in both Apps 9 and Apps 10.

(Aurena throws an error if the user picture is present but empty. This now removes the picture if it’s empty.)

DECLARE
PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2);

PROCEDURE set_ad_picture(
user_id_ IN VARCHAR2)
IS
fileopen_failed_exc_ EXCEPTION;
PRAGMA EXCEPTION_INIT (fileopen_failed_exc_, -22288);
file_name_ VARCHAR2(32767);
picture_blob_ BLOB;
picture_bfile_ BFILE;
length_ NUMBER;
info_ VARCHAR2(32767);
objid_ VARCHAR2(32767);
objversion_ VARCHAR2(32767);
attr_ VARCHAR2(32767);
blob_id_ binary_object.blob_id%TYPE;
dest_offset_ NUMBER := 1;
src_offset_ NUMBER := 1;
BEGIN
language_sys.set_language('en');

file_name_ := LOWER(user_id_) || '.png';
picture_bfile_ := BFILENAME('AD_PICTURE', file_name_);
BEGIN
dbms_lob.fileopen(picture_bfile_, dbms_lob.file_readonly);
dbms_lob.createtemporary(picture_blob_, TRUE);
dbms_lob.loadblobfromfile (
dest_lob => picture_blob_,
src_bfile => picture_bfile_,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset_,
src_offset => src_offset_);
dbms_lob.fileclose(picture_bfile_);
EXCEPTION WHEN fileopen_failed_exc_ THEN
picture_blob_ := NULL;
dbms_lob.fileclose(picture_bfile_);
END;
length_ := dbms_lob.getlength(picture_blob_);

SELECT picture_id
INTO blob_id_
FROM person_info
WHERE person_id = UPPER(user_id_);

IF picture_blob_ IS NOT NULL THEN
binary_object_api.create_or_replace(
blob_id_ /* IN OUT */ => blob_id_,
display_text_ /* IN */ => file_name_,
file_name_ /* IN */ => file_name_,
file_path_ /* IN */ => 'C:\',
external_storage_ /* IN */ => 'FALSE',
length_ /* IN */ => length_,
type_ /* IN */ => 'PICTURE',
application_data_ /* IN */ => '7');
binary_object_data_block_api.new__(
objversion_ /* OUT */ => objversion_,
objid_ /* OUT */ => objid_,
blob_id_ /* IN */ => blob_id_,
application_data_ /* IN */ => '7');
binary_object_data_block_api.write_data__(
objversion_ /* IN OUT */ => objversion_,
rowid_ /* IN */ => objid_,
lob_loc_ /* IN */ => picture_blob_);
ELSIF blob_id_ IS NOT NULL THEN
binary_object_api.do_delete(blob_id_);
blob_id_ := NULL;
END IF;

SELECT objid,
objversion
INTO objid_,
objversion_
FROM person_info
WHERE person_id = UPPER(user_id_);
client_sys.clear_attr(attr_);
client_sys.add_to_attr('PICTURE_ID', blob_id_, attr_);
person_info_api.modify__(info_, objid_, objversion_, attr_, 'DO');
END set_ad_picture;
BEGIN
FOR rec_ IN (
SELECT fu.identity
FROM fnd_user fu
JOIN person_info pi
ON pi.person_id = fu.identity
AND pi.user_id = fu.identity
ORDER BY 1
) LOOP
set_ad_picture(rec_.identity);
END LOOP;
END;
/

COMMIT;

 

Reply