1 PACKAGE BODY jl_globe_util_pkg AS
2 /* $Header: jlgutilb.pls 120.4 2010/11/02 10:02:57 mbarrett noship $ */
3
4 l_line VARCHAR2 (1999);
5 pg_debug_level NUMBER;
6
7 -- procedure to retrieve transaction reason code for PO
8 PROCEDURE populate_po_trx_reason_code (
9 p_level_id IN NUMBER,
10 p_org_id NUMBER
11 )
12 IS
13 l_inv_org_id NUMBER;
14 l_item_id NUMBER;
15 l_trx_reason_code VARCHAR2 (80);
16 BEGIN
17 DEBUG ('Begin populate_trx_reason_code');
18
19 BEGIN
20 SELECT pol.item_id
21 INTO l_item_id
22 FROM po_lines_all pol
23 WHERE pol.po_line_id = p_level_id;
24 EXCEPTION
25 WHEN NO_DATA_FOUND
26 THEN
27 NULL;
28 END;
29
30 DEBUG ('Item Id: ' || '**' || l_item_id || '**');
31
32 IF l_item_id IS NOT NULL
33 THEN
34 BEGIN
35 SELECT global_attribute2
36 INTO l_trx_reason_code
37 FROM mtl_system_items mtl
38 WHERE mtl.organization_id = (SELECT inventory_organization_id
39 FROM financials_system_parameters
40 WHERE org_id = p_org_id)
41 AND mtl.inventory_item_id = l_item_id
42 AND ROWNUM = 1;
43 EXCEPTION
44 WHEN NO_DATA_FOUND
45 THEN
46 NULL;
47 END;
48 ELSE
49 BEGIN
50 SELECT global_attribute1
51 INTO l_trx_reason_code
52 FROM po_system_parameters
53 WHERE ROWNUM = 1;
54 EXCEPTION
55 WHEN NO_DATA_FOUND
56 THEN
57 NULL;
58 END;
59 END IF;
60
61 DEBUG ('Transaction Reason Code: ' || '**' || l_trx_reason_code || '**');
62
63 BEGIN
64 UPDATE po_lines_all
65 SET transaction_reason_code = l_trx_reason_code
66 WHERE po_line_id = p_level_id;
67 END;
68
69 DEBUG ('End populate_trx_reason_code');
70 END populate_po_trx_reason_code;
71
72 -- function to retrieve transaction reason code for Requisitions
73 FUNCTION populate_icx_trx_reason_code (
74 p_org_id IN NUMBER,
75 p_item_id IN NUMBER,
76 p_deliver_to_org_id IN NUMBER
77 )
78 RETURN VARCHAR2
79 IS
80 -- Bug 10226487 Start
81 l_txn_reason_code VARCHAR2 (80);
82 -- Bug 10226487 End;
83 l_country_code VARCHAR2 (10);
84 l_org_id NUMBER;
85 l_trx_reason_def_rule VARCHAR2 (80);
86 l_def_from_org_id NUMBER;
87 BEGIN
88 DEBUG ('Entered populate_icx_trx_reason_code');
89 DEBUG (p_org_id);
90 DEBUG (p_item_id);
91 DEBUG (p_deliver_to_org_id);
92 DEBUG (l_txn_reason_code);
93
94 --
95 -- get transaction reason code from mtl system items if
96 -- item is known.
97 -- Determine which organization to use from po system parameters
98 -- and then fetch transaction reason accordingly.
99 --
100 IF p_item_id IS NOT NULL
101 THEN
102 SELECT global_attribute3
103 INTO l_trx_reason_def_rule
104 FROM po_system_parameters_all
105 WHERE org_id = p_org_id;
106
107 IF NVL (l_trx_reason_def_rule, 'MASTER INVENTORY ORGANIZATION') =
108 'INVENTORY ORGANIZATION'
109 THEN
110 l_def_from_org_id := p_deliver_to_org_id;
111
112 SELECT global_attribute2
113 INTO l_txn_reason_code
114 FROM mtl_system_items mtl
115 WHERE mtl.organization_id = l_def_from_org_id
116 AND mtl.inventory_item_id = p_item_id
117 AND ROWNUM = 1;
118
119 --
120 -- try to get trx reason code based on
121 -- validation organization id
122 --
123 IF l_txn_reason_code IS NULL
124 THEN
125 SELECT inventory_organization_id
126 INTO l_def_from_org_id
127 FROM financials_system_parameters
128 WHERE org_id = p_org_id;
129
130 SELECT global_attribute2
131 INTO l_txn_reason_code
132 FROM mtl_system_items mtl
133 WHERE mtl.organization_id = l_def_from_org_id
134 AND mtl.inventory_item_id = p_item_id
135 AND ROWNUM = 1;
136
137 IF l_txn_reason_code IS NOT NULL
138 THEN
139 --
140 -- return if trx reason code is found at validation organization id
141 --
142 RETURN l_txn_reason_code;
143 END IF;
144 END IF;
145 ELSIF NVL (l_trx_reason_def_rule, 'MASTER INVENTORY ORGANIZATION') =
146 'MASTER INVENTORY ORGANIZATION'
147 THEN
148 SELECT inventory_organization_id
149 INTO l_def_from_org_id
150 FROM financials_system_parameters
151 WHERE org_id = p_org_id;
152
153 SELECT global_attribute2
154 INTO l_txn_reason_code
155 FROM mtl_system_items mtl
156 WHERE mtl.organization_id = l_def_from_org_id
157 AND mtl.inventory_item_id = p_item_id
158 AND ROWNUM = 1;
159
160 IF l_txn_reason_code IS NOT NULL
161 THEN
162 --
163 -- return if trx reason code is found at Master org
164 --
165 RETURN l_txn_reason_code;
166 END IF;
167 END IF;
168 END IF;
169
170 -- get here then 1 of the following is true
171 -- p_item_id is NULL or
172 -- p_item_id is not NULL but trx reason code is
173 -- not available for Local/Master org
174 -- need to get trx reason code from
175 -- PO system parameters
176 --
177 SELECT global_attribute1
178 INTO l_txn_reason_code
179 FROM po_system_parameters_all
180 WHERE org_id = p_org_id;
181
182 RETURN l_txn_reason_code;
183 DEBUG ('End populate_icx_trx_reason_code');
184 EXCEPTION
185 WHEN OTHERS
186 THEN
187 DEBUG ('Error Code' || SQLCODE);
188 DEBUG ('Error Message' || SQLERRM);
189 -- Bug 10226487 Start
190 RETURN NULL;
191 -- Bug 10226487 End
192 END populate_icx_trx_reason_code;
193
194 /* ---------------------------------------------------------------------*
195 |Public Procedure |
196 | debug Write the text message in log file |
197 | if the debug is set "Yes". |
198 | Description This procedure will generate the standard debug |
199 | information in to the log file.User can open the |
200 | log file <user name.log> at specified location. |
201 | |
202 | Requires |
203 | p_line The line of debug messages that will be writen |
204 | in the log file. |
205 | Exception Raised |
206 | |
207 | Known Bugs |
208 | |
209 | Notes |
210 | |
211 | History |
212 | |
213 *-----------------------------------------------------------------------*/
214 PROCEDURE DEBUG (p_line IN VARCHAR2)
215 IS
216 p_module_name VARCHAR2 (50);
217 g_log_statement_level NUMBER;
218 g_current_runtime_level NUMBER;
219 g_level_event CONSTANT NUMBER := fnd_log.level_event;
220 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
221 g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
222 BEGIN
223 p_module_name := 'JG: Globe Util';
224 g_log_statement_level := fnd_log.level_statement;
225 pg_debug_level := fnd_log.level_procedure;
226 g_current_runtime_level := fnd_log.g_current_runtime_level;
227
228 IF (g_log_statement_level >= g_current_runtime_level)
229 THEN
230 IF LENGTHB (p_line) > 1999
231 THEN
232 l_line := SUBSTRB (p_line, 1, 1999);
233 ELSE
234 l_line := p_line;
235 END IF;
236
237 fnd_log.STRING (log_level => g_log_statement_level,
238 module => p_module_name,
239 MESSAGE => l_line
240 );
241 END IF;
242 EXCEPTION
243 WHEN OTHERS
244 THEN
245 IF (g_level_unexpected >= g_current_runtime_level)
246 THEN
247 fnd_log.STRING
248 (log_level => fnd_log.level_unexpected,
249 module => p_module_name,
250 MESSAGE => 'Unexpected Error When Logging Debug Messages.'
251 );
252 END IF;
253 END DEBUG;
254 END;
255
[Home] [Help]