1 PACKAGE BODY FUN_IC_AME_WF_PKG AS
2 /* $Header: funicameab.pls 120.0 2004/10/13 12:58:17 bsilveir noship $ */
3
4
5 /* ---------------------------------------------------------------------------
6 Name : get_attribute_value
7 Pre-reqs : None.
8 Modifies : None.
9 function : This function returns the values for the intercompany transaction
10 attributes that are used within AME. These values are used when
11 evaluating AME rules and conditions
12 Parameters:
13 IN : p_transaction_id - fun_trx_headers.trx_id
14 p_dist_id - fun_trx_dist_lines.dist_id
15 p_attribute_name - Name of the attribute whose value is required.
16 OUT : Value of the attribute.
17 Notes : None.
18 Testing : This function can be tested using the 'Test' tab provided within AME
19 setup pages
20 ------------------------------------------------------------------------------*/
21 FUNCTION get_attribute_value
22 (p_transaction_id IN NUMBER,
23 p_dist_id IN NUMBER DEFAULT NULL,
24 p_attribute_name IN VARCHAR2)
25 RETURN VARCHAR2 IS
26
27 l_return_value VARCHAR2(2000):= NULL;
28
29 BEGIN
30
31 IF p_attribute_name = 'BATCH_INITIATOR_NAME'
32 THEN
33 SELECT DISTINCT hz.party_name
34 INTO l_return_value
35 FROM hz_parties hz,
36 fun_trx_batches btch,
37 fun_trx_headers head
38 WHERE hz.party_id = btch.initiator_id
39 AND btch.batch_id = head.batch_id
40 AND head.trx_id = p_transaction_id;
41
42 ELSIF p_attribute_name = 'BATCH_FROM_LE_NAME'
43 THEN
44 SELECT DISTINCT xla.name
45 INTO l_return_value
46 FROM xle_firstparty_information_v xla,
47 fun_trx_batches btch,
48 fun_trx_headers head
49 WHERE xla.legal_entity_id = btch.from_le_id
50 AND btch.batch_id = head.batch_id
51 AND head.trx_id = p_transaction_id;
52
53 ELSIF p_attribute_name = 'BATCH_FROM_LEDGER_NAME'
54 THEN
55 SELECT DISTINCT gl.name
56 INTO l_return_value
57 FROM gl_ledgers gl,
58 fun_trx_batches btch,
59 fun_trx_headers head
60 WHERE gl.ledger_id = btch.from_ledger_id
61 AND btch.batch_id = head.batch_id
62 AND head.trx_id = p_transaction_id;
63
64 ELSIF p_attribute_name = 'BATCH_TYPE_NAME'
65 THEN
66 SELECT DISTINCT tt.trx_type_name
67 INTO l_return_value
68 FROM fun_trx_types_tl tt,
69 fun_trx_batches btch,
70 fun_trx_headers head
71 WHERE tt.trx_type_id = btch.trx_type_id
72 AND btch.batch_id = head.batch_id
73 AND head.trx_id = p_transaction_id;
74
75 ELSIF p_attribute_name = 'HEADER_INITIATOR_NAME'
76 THEN
77 SELECT DISTINCT hz.party_name
78 INTO l_return_value
79 FROM hz_parties hz,
80 fun_trx_headers head
81 WHERE hz.party_id = head.initiator_id
82 AND head.trx_id = p_transaction_id;
83
84 ELSIF p_attribute_name = 'HEADER_RECIPIENT_NAME'
85 THEN
86 SELECT DISTINCT hz.party_name
87 INTO l_return_value
88 FROM hz_parties hz,
89 fun_trx_headers head
90 WHERE hz.party_id = head.recipient_id
91 AND head.trx_id = p_transaction_id;
92
93 ELSIF p_attribute_name = 'HEADER_TO_LE_NAME'
94 THEN
95 SELECT DISTINCT xla.name
96 INTO l_return_value
97 FROM xle_firstparty_information_v xla,
98 fun_trx_headers head
99 WHERE xla.legal_entity_id = head.to_le_id
100 AND head.trx_id = p_transaction_id;
101
102 ELSIF p_attribute_name = 'HEADER_TO_LEDGER_NAME'
103 THEN
104 SELECT DISTINCT gl.name
105 INTO l_return_value
106 FROM gl_ledgers gl,
107 fun_trx_headers head
108 WHERE gl.ledger_id = head.to_ledger_id
109 AND head.trx_id = p_transaction_id;
110
111 ELSIF p_attribute_name = 'DIST_PARTY_NAME'
112 THEN
113 SELECT DISTINCT hz.party_name
114 INTO l_return_value
115 FROM hz_parties hz,
116 fun_dist_lines dist
117 WHERE hz.party_id = dist.party_id
118 AND dist.dist_id = p_dist_id;
119
120 END IF;
121
122 RETURN l_return_value;
123
124 EXCEPTION
125 -- Since these functions will be called from AME, just log the exception
126 -- and return NULL.
127
128 WHEN NO_DATA_FOUND
129 THEN
130 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
131 THEN
132 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
133 'fun.plsql.fun_ic_ame_wf_pkg.get_attribute_values',
134 'No data found error occurred when getting '||
135 'attribute value '||p_attribute_name||
136 ' for transaction '||p_transaction_id);
137 END IF;
138
139 RETURN NULL;
140
141 WHEN OTHERS
142 THEN
143 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
144 THEN
145 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
146 'fun.plsql.fun_ic_ame_wf_pkg.get_attribute_values',
147 SQLERRM || ' Error occurred when getting '||
148 'attribute value '||p_attribute_name||
149 ' for transaction '||p_transaction_id);
150 END IF;
151 RETURN NULL;
152 END get_attribute_value;
153
154 /* ---------------------------------------------------------------------------
155 Name : get_fun_dist_acct_flex
156 Pre-reqs : None.
157 Modifies : None.
158 Function : This function will be called from within AME to get the value of
159 the accounting flexfields qualifying segments enabling users to
160 build rules based on them.
161 Parameters:
162 IN : p_seg_name - Name of the Segment
163 Eg. GL_ACCOUNT, GL_BALANCING, FA_COST_CTR
164 p_ccid - Code Combination Id
165 p_dist_id - fun_trx_dist_lines.dist_id
166 p_transaction_id - fun_trx_headers.trx_id
167 OUT : Value of the attribute.
168 Notes : None.
169 Testing : This function can be tested using the 'Test' tab provided within AME
170 setup pages
171 ------------------------------------------------------------------------------*/
172 FUNCTION get_fun_dist_acct_flex(p_seg_name IN VARCHAR2,
173 P_ccid IN NUMBER,
174 p_dist_id IN NUMBER,
175 p_transaction_id IN NUMBER)
176 RETURN VARCHAR2
177 IS
178
179 l_segments FND_FLEX_EXT.SEGMENTARRAY;
180 l_result BOOLEAN;
181 l_chart_of_accounts_id NUMBER;
182 l_num_segments NUMBER;
183 l_segment_num NUMBER;
184 l_seg_val VARCHAR2(50);
185
186 BEGIN
187 SELECT chart_of_accounts_id
188 INTO l_chart_of_accounts_id
189 FROM gl_ledgers gl,
190 fun_trx_headers head
191 WHERE gl.ledger_id = head.to_ledger_id
192 AND head.trx_id = p_transaction_id;
193
194 l_result := fnd_flex_ext.get_segments(
195 'SQLGL',
196 'GL#',
197 l_chart_of_accounts_id,
198 p_ccid,
199 l_num_segments,
200 l_segments);
201
202 l_result := fnd_flex_apis.get_qualifier_segnum(
203 101,
204 'GL#',
205 l_chart_of_accounts_id,
206 p_seg_name,
207 l_segment_num);
208
209 l_seg_val := l_segments(l_segment_num);
210
211 RETURN l_seg_val;
212
213 EXCEPTION
214 -- Since these functions will be called from AME, just log the exception
215 -- and return NULL.
216 WHEN OTHERS
217 THEN
218 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
219 THEN
220 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
221 'fun.plsql.fun_ic_ame_wf_pkg.get_fun_dist_acct_flex',
222 SQLERRM || ' Error occurred when getting '||
223 'segment value '||p_seg_name ||
224 ' for transaction ' || p_transaction_id);
225 END IF;
226 RETURN NULL;
227 END get_fun_dist_acct_flex;
228
229 END fun_ic_ame_wf_pkg;
230
[Home] [Help]