1 PACKAGE PO_UDA_PUB AUTHID CURRENT_USER AS
2 /* $Header: PO_UDA_PUB.pls 120.3 2010/07/18 12:11:05 bhuchand noship $ */
3
4 ------------------------------------------------------------------------------------------
5 -- Function: This function is used to get the sepecific UDA attribute value
6 -- depending on the parameters passed
7 --
8 -- Parameters:
9 -- IN
10 -- p_template_id : Template ID (Should be passed if p_entity_code is null)
11 -- p_entity_code : Entity Code (Should be passed if p_template_id is null)
12 -- Valid values for entity code are (PO_HEADER_EXT_ATTRS, PO_LINE_EXT_ATTRS, PO_SHIPMENTS_EXT_ATTRS,
13 -- PO_DISTRIBUTIONS_EXT_ATTRS, PO_REQ_HEADER_EXT_ATTRS, PO_REQ_LINE_EXT_ATTRS
14 -- PO_REQ_DIST_EXT_ATTRS, PON_AUC_HDRS_EXT_ATTRS, PON_AUC_PRICES_EXT_ATTRS,
15 -- PON_BID_HDRS_EXT_ATTRS, PON_BID_PRICES_EXT_ATTRS)
16 -- pk1_value : PK1 Value (Primary Key of the row to be identified)
17 -- pk2_value : PK2 Value (Primary Key of the row to be identified)
18 -- pk3_value : PK3 Value (Primary Key of the row to be identified)
19 -- pk4_value : PK4 Value (Primary Key of the row to be identified)
20 -- pk5_value : PK5 Value (Primary Key of the row to be identified)
21 -- p_attr_grp_id : Attribute Group ID (Should be passed if p_attr_grp_int_name is null)
22 -- p_attr_grp_int_name : Attribute Group Internal name (Should be passed if p_attr_grp_id is null)
23 -- p_attr_id : Attribute ID (Should be passed if p_attr_int_name is null)
24 -- p_attr_int_name : Attribute Internal name (Should be passed if p_attr_id is null)
25 -- p_mode : Mode (Valid values 'DISPLAY_VALUE' and 'INTERNAL_VALUE'
26 -- p_mode specifies whether to return the internal code of the attribute value or the display value from the associated value set
27 -- RETURN
28 -- VARCHAR2 : The value of the UDA attribute specified
29 ------------------------------------------------------------------------------------------
30
31 function get_single_attr_value (
32 p_template_id IN NUMBER DEFAULT NULL,
33 p_entity_code IN VARCHAR2 DEFAULT null,
34 pk1_value IN VARCHAR2 DEFAULT NULL,
35 pk2_value IN VARCHAR2 DEFAULT NULL,
36 pk3_value IN VARCHAR2 DEFAULT NULL,
37 pk4_value IN VARCHAR2 DEFAULT NULL,
38 pk5_value IN VARCHAR2 DEFAULT NULL,
39 p_attr_grp_id IN NUMBER DEFAULT NULL,
40 p_attr_grp_int_name IN VARCHAR2 DEFAULT NULL,
41 p_attr_id IN NUMBER DEFAULT NULL,
42 p_attr_int_name IN VARCHAR2 DEFAULT NULL,
43 p_mode IN VARCHAR2 DEFAULT 'INTERNAL_VALUE'
44 ) RETURN VARCHAR2 ;
45
46 ------------------------------------------------------------------------------------------
47 -- Function: This function is used to get the sepecific UDA address row attribute values
48 -- depending on the parameters passed
49 --
50 -- Parameters:
51 -- IN
52 -- p_template_id : Template ID (Should be passed if p_entity_code is null)
53 -- p_entity_code : Entity Code (Should be passed if p_template_id is null)
54 -- Valid values for entity code are (PO_HEADER_EXT_ATTRS, PO_LINE_EXT_ATTRS, PO_SHIPMENTS_EXT_ATTRS,
55 -- PO_DISTRIBUTIONS_EXT_ATTRS, PO_REQ_HEADER_EXT_ATTRS, PO_REQ_LINE_EXT_ATTRS
56 -- PO_REQ_DIST_EXT_ATTRS, PON_AUC_HDRS_EXT_ATTRS, PON_AUC_PRICES_EXT_ATTRS,
57 -- PON_BID_HDRS_EXT_ATTRS, PON_BID_PRICES_EXT_ATTRS)
58 -- pk1_value : PK1 Value (Primary Key of the row to be identified)
59 -- pk2_value : PK2 Value (Primary Key of the row to be identified)
60 -- pk3_value : PK3 Value (Primary Key of the row to be identified)
61 -- pk4_value : PK4 Value (Primary Key of the row to be identified)
62 -- pk5_value : PK5 Value (Primary Key of the row to be identified)
63 -- p_attr_grp_id : Attribute Group ID (Should be passed if p_attr_grp_int_name is null)
64 -- p_attr_grp_int_name : Attribute Group Internal name (Should be passed if p_attr_grp_id is null)
65 -- p_attr_id : Attribute ID (Should be passed if p_attr_int_name is null)
66 -- p_attr_int_name : Attribute Internal name (Should be passed if p_attr_id is null)
67 -- p_address_type : Address Type (Ex: COTR_OFFICE)
68 -- Valid value can be lookup_code from any lookup types in
69 -- (SOL_UDA_ADDRESS_TYPES, PO_MOD_UDA_ADDRESS_TYPES, PR_UDA_ADDRESS_TYPES, PO_UDA_ADDRESS_TYPES, PR_AMD_UDA_ADDRESS_TYPES, SOL_AMD_UDA_ADDRESS_TYPES)
70 --
71 -- RETURN
72 -- VARCHAR2 : The values of the UDA address attribute specified witthe the address type
73 ------------------------------------------------------------------------------------------
74 FUNCTION GET_ADDRESS_ATTR_VALUE (
75 p_template_id IN NUMBER default null,
76 p_entity_code IN VARCHAR2 default null,
77 pk1_value IN NUMBER default null,
78 pk2_value IN NUMBER default null,
79 pk3_value IN NUMBER default null,
80 pk4_value IN NUMBER default null,
81 pk5_value IN NUMBER default null,
82 p_attr_grp_id IN NUMBER default null,
83 p_attr_grp_int_name IN VARCHAR2 default 'addresses',
84 p_attr_id IN NUMBER default null,
85 p_attr_int_name IN VARCHAR2 default null,
86 p_address_type IN VARCHAR2,
87 p_mode IN VARCHAR2 DEFAULT 'DISPLAY_VALUE'
88 ) RETURN VARCHAR2;
89
90 ------------------------------------------------------------------------------------------
91 -- Function: This function is used to get the sepecific UDA multi row attribute values
92 -- depending on the parameters passed
93 --
94 -- Parameters:
95 -- IN
96 -- p_template_id : Template ID (Should be passed if p_entity_code is null)
97 -- p_entity_code : Entity Code (Should be passed if p_template_id is null)
98 -- Valid values for entity code are (PO_HEADER_EXT_ATTRS, PO_LINE_EXT_ATTRS, PO_SHIPMENTS_EXT_ATTRS,
99 -- PO_DISTRIBUTIONS_EXT_ATTRS, PO_REQ_HEADER_EXT_ATTRS, PO_REQ_LINE_EXT_ATTRS
100 -- PO_REQ_DIST_EXT_ATTRS, PON_AUC_HDRS_EXT_ATTRS, PON_AUC_PRICES_EXT_ATTRS,
101 -- PON_BID_HDRS_EXT_ATTRS, PON_BID_PRICES_EXT_ATTRS)
102 -- pk1_value : PK1 Value (Primary Key of the row to be identified)
103 -- pk2_value : PK2 Value (Primary Key of the row to be identified)
104 -- pk3_value : PK3 Value (Primary Key of the row to be identified)
105 -- pk4_value : PK4 Value (Primary Key of the row to be identified)
106 -- pk5_value : PK5 Value (Primary Key of the row to be identified)
107 -- p_attr_grp_id : Attribute Group ID (Should be passed if p_attr_grp_int_name is null)
108 -- p_attr_grp_int_name : Attribute Group Internal name (Should be passed if p_attr_grp_id is null)
109 -- p_attr_id : Attribute ID (Should be passed if p_attr_int_name is null)
110 -- p_attr_int_name : Attribute Internal name (Should be passed if p_attr_id is null)
111 -- p_attr_grp_pk_tbl : IF Null would get all the appropriate values else would get one appropriate value in the table
112 -- p_attr_grp_pk_tbl refers to the attribute group unique keys for a multi row AG, in the sequence of the corresponding attributes
113 --
114 -- RETURN
115 -- PO_TBL_VARCHAR4000 : The values of the UDA attributes specified in a table
116 ------------------------------------------------------------------------------------------
117
118 function get_multi_attr_value (
119 p_template_id IN NUMBER,
120 p_entity_code IN VARCHAR2,
121 pk1_value IN NUMBER,
122 pk2_value IN NUMBER,
123 pk3_value IN NUMBER,
124 pk4_value IN NUMBER,
125 pk5_value IN NUMBER,
126 p_attr_grp_id IN NUMBER,
127 p_attr_grp_int_name IN VARCHAR2,
128 p_attr_id IN NUMBER,
129 p_attr_int_name IN VARCHAR2,
130 p_attr_grp_pk_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
131 p_mode IN VARCHAR2 DEFAULT 'INTERNAL_VALUE'
132 ) RETURN PO_TBL_VARCHAR4000;
133
134
135 ------------------------------------------------------------------------------------------
136 -- Procedure: This procedure is used to get the sepecific UDA address attribute values
137 -- depending on the parameters passed
138 --
139 -- Parameters:
140 -- IN
141 -- p_template_id : Template ID (Should be passed if p_entity_code is null)
142 -- p_entity_code : Entity Code (Should be passed if p_template_id is null)
143 -- Valid values for entity code are (PO_HEADER_EXT_ATTRS, PO_LINE_EXT_ATTRS, PO_SHIPMENTS_EXT_ATTRS,
144 -- PO_DISTRIBUTIONS_EXT_ATTRS, PO_REQ_HEADER_EXT_ATTRS, PO_REQ_LINE_EXT_ATTRS
145 -- PO_REQ_DIST_EXT_ATTRS, PON_AUC_HDRS_EXT_ATTRS, PON_AUC_PRICES_EXT_ATTRS,
146 -- PON_BID_HDRS_EXT_ATTRS, PON_BID_PRICES_EXT_ATTRS)
147 -- pk1_value : PK1 Value (Primary Key of the row to be identified)
148 -- pk2_value : PK2 Value (Primary Key of the row to be identified)
149 -- pk3_value : PK3 Value (Primary Key of the row to be identified)
150 -- pk4_value : PK4 Value (Primary Key of the row to be identified)
151 -- pk5_value : PK5 Value (Primary Key of the row to be identified)
152 -- p_attr_grp_id : Attribute Group ID (Should be passed if p_attr_grp_int_name is null)
153 -- p_attr_grp_int_name : Attribute Group Internal name (Should be passed if p_attr_grp_id is null)
154 -- p_attr_id : Attribute ID (Should be passed if p_attr_int_name is null)
155 -- p_attr_int_name : Attribute Internal name (Should be passed if p_attr_id is null)
156 -- p_mode : 'INTERNAL_VALUE' OR 'DISPLAY_VALUE'
157 -- p_mode specifies whether to return the internal code of the attribute value or the display value from the associated value set
158 -- p_attr_grp_pk_tbl : PK values can be specified to get the appropriate value (If null would get the total set)
159 -- p_attr_grp_pk_tbl refers to the attribute group unique keys for a multi row AG, in the sequence of the corresponding attributes
160 --
161 -- OUT
162 -- x_multi_row_code : Y or N (Y for multi row and N for single row)
163 -- x_single_attr_value : If x_multi_row_code is N then the value is populated in this variable
164 -- x_multi_attr_value : The table will have all the appropriate values populated in this variable
165 -- x_return_status : The return status
166 -- Valid vaules are FND_API.G_RET_STS_SUCCESS => That the procedure returned successfully
167 -- FND_API.G_RET_STS_ERROR => That the procedure returned with expected error
168 -- FND_API.G_RET_STS_UNEXP_ERROR => That the procedure returned with unexpected error
169 -- x_msg_data : Appropriate Error Msg data
170 --
171 ------------------------------------------------------------------------------------------
172
173 PROCEDURE GET_ATTR_VALUE (
174 p_template_id IN NUMBER DEFAULT NULL,
178 pk3_value IN VARCHAR2 DEFAULT NULL,
175 p_entity_code IN VARCHAR2 DEFAULT null,
176 pk1_value IN VARCHAR2 DEFAULT NULL,
177 pk2_value IN VARCHAR2 DEFAULT NULL,
179 pk4_value IN VARCHAR2 DEFAULT NULL,
180 pk5_value IN VARCHAR2 DEFAULT NULL,
181 p_attr_grp_id IN NUMBER DEFAULT NULL,
182 p_attr_grp_int_name IN VARCHAR2 DEFAULT NULL,
183 p_attr_id IN NUMBER DEFAULT NULL,
184 p_attr_int_name IN VARCHAR2 DEFAULT NULL,
185 p_mode IN VARCHAR2 DEFAULT 'INTERNAL_VALUE',
186 p_attr_grp_pk_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
187 x_multi_row_code OUT NOCOPY VARCHAR2,
188 x_single_attr_value OUT NOCOPY VARCHAR2,
189 x_multi_attr_value OUT NOCOPY PO_TBL_VARCHAR4000,
190 x_return_status OUT NOCOPY VARCHAR2,
194
191 x_msg_data OUT NOCOPY VARCHAR2
192 );
193
195 END PO_UDA_PUB;