[Brandon Macki]

I’m interested if this is still available. bmacki@maaero.com

[Brandon Macki]

Nadim Ghazzaoui wrote:

I had created for a customer a function that can be called in sql and return the value of the UDA. This will avoid adding the extra tables and joins in your code. It is something like:

SELECT

si_number,

get_uda_value(‘WOO’, ‘AUTH’, woo_auto_key) authority,

nvl(get_uda_value(‘WOO’, ‘REL’, woo_auto_key), ‘F’) release

FROM

wo_operation

WHERE

si_number = ‘WO1234’;

The parameters are:

AUTO_KEY_PREFIX

UDA_CODE

AUTO_KEY

For the checkboxes, use NVL (as above) or else you will get NULL for non-checked checkboxes.

The procedure is below. It can be added to QCTL. Procedures are not replaced during upgrades.

I called it GET_UDA_VALUE but you can use something shorter if you prefer.

CREATE OR REPLACE FUNCTION get_uda_value (

p_auto_key_prefix VARCHAR2,

p_uda_code VARCHAR2,

p_auto_key NUMBER

) RETURN VARCHAR2 IS

v_return uda_checked.attribute_value%TYPE;

BEGIN

SELECT

udc.attribute_value

INTO v_return

FROM

user_defined_attributes uda,

uda_checked udc

WHERE

uda.uda_auto_key = udc.uda_auto_key (+)

AND uda.auto_key_prefix = p_auto_key_prefix

AND uda.uda_code = p_uda_code

AND udc.auto_key = p_auto_key;

RETURN v_return;

EXCEPTION

WHEN no_data_found THEN

v_return := NULL;

RETURN v_return;

WHEN OTHERS THEN

RAISE;

END;

Thanks Nadim, I will give this a try.

[Brandon Macki]

Jake Anderson wrote:Can you take a screen shot of the field or fields you are looking for?

Hi Jake, See attached for what I am looking for. Just looking to create a crystal report that pulls the WO # and the Different Attributes listed in the attachment. Having a hard time with joining these tables.

[Brandon Macki]

Hi Jason,

Would you mind sending to me also? Thanks in advance.

Brandon – bmacki@maaero.com

[Brandon Macki]

Hi John, Ultimately the process and report works to provide the data but I am still required to show the actual script / query that pulls the data. I was hoping to find something in the SYS_QUERIES table but could not.

[Brandon Macki]

Disregard! I had the auto key wrong. This seems to work.

var

qc: TOracleDataset;

begin

qc := TOracleDataset.Create(nil);

qc.SetSession;

qc.sql.text :=’SELECT * from wo_work_type where wwt_auto_key = ‘+ WO_OPERATION[‘WWT_AUTO_KEY’];

qc.Open;

Text := qc.FieldByName[‘description’].AsString;

qc.Free;

end;

[Brandon Macki]

Here is the code i’m using. I get the green light but when I try to preview receive this error, “Text ORA-00936 missing expression”.

var

qc: TOracleDataset;

begin

qc := TOracleDataset.Create(nil);

qc.SetSession;

qc.sql.text :=’SELECT * from wo_work_type where wtt.auto_key = ‘+ WO_OPERATION[‘WTT_AUTO_KEY’];

qc.Open;

Text := qc.FieldByName[‘description’].AsString;

qc.Free;

end;

[Brandon Macki]

Mike Carey wrote:

Create the field on the form for the term code. Create an OnGetText event handler and use this code. It will pull the term_code for the company in the work order header

var

qc : TOracleDataset;

begin

qc := TOracleDataset.Create(nil);

qc.SetSession;

qc.Sql.text :=’select term_code from companies cmp, term_codes tmc where cmp.tmc_auto_key = tmc.tmc_auto_key and cmp.cmp_auto_key = ‘+ WO_OPERATION[‘CMP_AUTO_KEY’];

qc.Open;

Text := qc.FieldByName[‘term_code’].AsString;

qc.Free;

end;

This is great. Thank you!

[Brandon Macki]

Thanks Thiago! That worked.

Mike – i’ll give yours a shot too and see what it does. Thanks for the input.

[Brandon Macki]

It is for a BOM item sent out for Repair (NDT).

[Brandon Macki]

Thanks for the responses. Am I missing a setting somewhere possibly? The first certificate I issue gets linked to the stock line but any subsequent certificates do not get the stock line attached and won’t print out pn, description, s/n, etc.

[Brandon Macki]

Is it out of the box or custom created by you? Are you willing to share form design?