1 PACKAGE BODY PO_AGENTS_SV1 AS
2 /* $Header: POXPIAGB.pls 120.0.12010000.1 2008/09/18 12:21:08 appldev noship $ */
3
4 /*===============================================================
5
6 FUNCTION NAME : derive_agent_id()
7
8 ================================================================*/
9
10 FUNCTION derive_agent_id(X_agent_name IN VARCHAR2)
11 return NUMBER IS
12
13 X_progress varchar2(3) := NULL;
14 X_agent_id_v number := NULL;
15
16 BEGIN
17
18 X_progress := '010';
19
20 /* get the agent_id by selecting employee_id from po_buyers_val_v
21 based on the agent_name provided from input parameter */
22
23 SELECT employee_id
24 INTO X_agent_id_v
25 FROM po_buyers_val_v
26 WHERE full_name = X_agent_name;
27
28 RETURN X_agent_id_v;
29
30 EXCEPTION
31
32 WHEN no_data_found THEN
33 RETURN NULL;
34 WHEN others THEN
35 po_message_s.sql_error('derive_agent_id',X_progress, sqlcode);
36 raise;
37
38 END derive_agent_id;
39
40 /*================================================================
41
42 FUNCTION NAME: val_agent_id()
43
44 ==================================================================*/
45 FUNCTION val_agent_id(x_agent_id IN NUMBER)RETURN BOOLEAN
46 IS
47
48 x_progress varchar2(3) := null;
49 x_temp binary_integer := 0;
50
51 BEGIN
52 x_progress := '010';
53
54 /* check to see if the given agnet_id is a valid agent_id in
55 po_buyers_val_v table */
56
57 SELECT count(*)
58 INTO x_temp
59 FROM po_buyers_val_v
60 WHERE employee_id = X_agent_id;
61
62 IF x_temp = 0 THEN
63 RETURN FALSE; /* validation fails */
64 ELSE
65 RETURN TRUE; /* validation succeeds */
66 END IF;
67
68 EXCEPTION
69 WHEN others THEN
70 po_message_s.sql_error('val_agent_id', x_progress,sqlcode);
71 raise;
72 END val_agent_id;
73
74 END PO_AGENTS_SV1;
[Home] [Help]