[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_UTIL_PVT
Source
1 PACKAGE BODY PO_DOCUMENT_UTIL_PVT as
2 -- $Header: PO_DOCUMENT_UTIL_PVT.plb 120.4.12020000.3 2013/02/10 22:31:53 vegajula ship $
3
4 --------------------------------------------------------------------------
5 -- Modules for debugging.
6 --------------------------------------------------------------------------
7
8 -- The module base for this package.
9 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
10 PO_LOG.get_package_base('PO_DOCUMENT_UTIL_PVT');
11
12 -- The module base for the subprogram.
13 D_synchronize_gt_tables CONSTANT VARCHAR2(100) :=
14 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'synchronize_gt_tables');
15 D_initialize_gt_table CONSTANT VARCHAR2(100) :=
16 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'initialize_gt_table');
17
18 --for bug 12534184
19 D_get_plc_status CONSTANT VARCHAR2(100) :=
20 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_plc_status');
21
22
23
24
25 PROCEDURE synchronize_gt_tables (
26 p_key IN NUMBER
27 , p_index_num1_vals IN PO_TBL_NUMBER
28 , p_index_num2_vals IN PO_TBL_NUMBER
29 )
30 IS
31 d_mod CONSTANT VARCHAR2(100) := D_synchronize_gt_tables;
32
33 BEGIN
34
35 IF PO_LOG.d_proc THEN
36 PO_LOG.proc_begin(d_mod);
37 END IF;
38
39 -- Remove any values for this gt key
40 DELETE FROM PO_SESSION_GT
41 WHERE key = p_key;
42
43 -- Bulk insert new values
44 FORALL i IN 1 .. p_index_num1_vals.COUNT
45 INSERT INTO PO_SESSION_GT
46 ( key
47 , index_num1
48 , index_num2
49 )
50 VALUES
51 (
52 p_key
53 , p_index_num1_vals(i)
54 , p_index_num2_vals(i)
55 );
56
57 IF PO_LOG.d_proc THEN
58 PO_LOG.proc_end(d_mod);
59 END IF;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 IF PO_LOG.d_exc THEN
64 PO_LOG.exc(d_mod,0,NULL);
65 END IF;
66 RAISE;
67
68 END synchronize_gt_tables;
69
70 /*==================================================================
71 FUNCTION NAME: initialize_gt_table
72
73 DESCRIPTION: Inserts the values into PO_SESSION_GT at
74 a new key, returning the key.
75
76 PARAMETERS: p_index_num1_vals - the values to be inserted into
77 the GT Table's index_num1 column.
78 p_index_num2_vals - the values to be inserted into
79 the GT Table's index_num2 column.
80 ====================================================================*/
81 FUNCTION initialize_gt_table (
82 p_index_num1_vals IN PO_TBL_NUMBER
83 , p_index_num2_vals IN PO_TBL_NUMBER)
84 RETURN NUMBER
85
86 IS
87 d_mod CONSTANT VARCHAR2(100) := D_initialize_gt_table;
88 l_key NUMBER;
89
90 BEGIN
91
92 IF PO_LOG.d_proc THEN
93 PO_LOG.proc_begin(d_mod, 'l_key', l_key);
94 END IF;
95
96 -- Get the next key for the session
97 l_key := PO_CORE_S.get_session_gt_nextval;
98
99 -- Insert the values into the GT table
100 synchronize_gt_tables(
101 p_key => l_key,
102 p_index_num1_vals => p_index_num1_vals,
103 p_index_num2_vals => p_index_num2_vals);
104
105 IF PO_LOG.d_proc THEN
106 PO_LOG.proc_end(d_mod);
107 END IF;
108
109 RETURN(l_key);
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF PO_LOG.d_exc THEN
114 PO_LOG.exc(d_mod,0,NULL);
115 END IF;
116 RAISE;
117
118
119 END initialize_gt_table;
120
121
122 --For Bug 12534184
123
124 /*==================================================================
125 FUNCTION NAME: get_plc_status
126
127 DESCRIPTION: Returns the concatenated status of document.
128
129 PARAMETERS: p_header_id:Document header id.
130 ====================================================================*/
131
132 FUNCTION get_plc_status (
133 p_header_id IN NUMBER)
134 RETURN VARCHAR2
135
136 IS
137 disp_field_sta VARCHAR2(1000);
138 l_progress NUMBER := 0;
139 d_mod CONSTANT VARCHAR2(100) := D_get_plc_status;
140 l_approved_flag VARCHAR2(1);
141 l_authorization_status VARCHAR2(40);
142 l_cancel_flag VARCHAR2(1);
143 l_closed_code VARCHAR2(40);
144 l_frozen_flag VARCHAR2(1);
145 l_user_hold_flag VARCHAR2(1);
146 l_temp VARCHAR2(100);
147
148 BEGIN
149
150 l_progress := 1;
151
152 SELECT approved_flag,authorization_status,cancel_flag,closed_code,frozen_flag,user_hold_flag
153 INTO l_approved_flag,l_authorization_status,l_cancel_flag,l_closed_code,l_frozen_flag,l_user_hold_flag
154 FROM po_headers_all
155 WHERE po_header_id = p_header_id;
156
157 l_progress := 2;
158 SELECT displayed_field
159 INTO disp_field_sta
160 FROM po_lookup_codes
161 WHERE lookup_code = DECODE(l_approved_flag,
162 'R', l_approved_flag,
163 NVL(l_authorization_status, 'INCOMPLETE'))
164 AND lookup_type IN ( 'PO APPROVAL', 'DOCUMENT STATE' );
165
166 l_progress := 3;
167
168 IF l_cancel_flag = 'Y'
169 THEN
170 SELECT displayed_field
171 INTO l_temp
172 FROM po_lookup_codes
173 WHERE lookup_code = 'CANCELLED'
174 AND lookup_type = 'DOCUMENT STATE';
175 ELSE
176 l_temp := NULL;
177 END IF;
178
179 disp_field_sta := disp_field_sta || ' ' || l_temp;
180
181 l_temp := NULL;
182
183 l_progress := 4;
184
185 IF NVL(l_closed_code, 'OPEN') <> 'OPEN'
186 THEN
187 SELECT displayed_field
188 INTO l_temp
189 FROM po_lookup_codes
190 WHERE lookup_code = NVL(l_closed_code, 'OPEN')
191 AND lookup_type = 'DOCUMENT STATE';
192 ELSE
193 l_temp := NULL;
194 END IF;
195
196 disp_field_sta := disp_field_sta || ' ' || l_temp;
197
198 l_temp := NULL;
199
200 l_progress := 5;
201
202 IF l_frozen_flag= 'Y'
203 THEN
204 SELECT displayed_field
205 INTO l_temp
206 FROM po_lookup_codes
207 WHERE lookup_type = 'DOCUMENT STATE'
208 AND lookup_code = 'FROZEN';
209 ELSE
210 l_temp := NULL;
211 END IF;
212
213 disp_field_sta := disp_field_sta || ' ' || l_temp;
214
215 l_temp := NULL;
216
217 l_progress := 6;
218
219 IF l_user_hold_flag = 'Y'
220 THEN
221 SELECT displayed_field
222 INTO l_temp
223 FROM po_lookup_codes
224 WHERE lookup_type = 'DOCUMENT STATE'
225 AND lookup_code = 'ON HOLD';
226 ELSE
227 l_temp := NULL;
228 END IF;
229
230 disp_field_sta := disp_field_sta || ' ' || l_temp;
231
232
233 RETURN(disp_field_sta) ;
234
235 EXCEPTION WHEN OTHERS THEN
236 IF PO_LOG.d_exc THEN
237 PO_LOG.exc(d_mod,l_progress,NULL);
238 END IF;
239 RETURN NULL;
240 END get_plc_status;
241 /*bug 12932650*/
242 /*==================================================================
243 * FUNCTION NAME: get_prorated_tax
244 *
245 * DESCRIPTION: Returns tax only for uncancelled quantity
246 *
247 * PARAMETERS: p_header_id,po_line_id,line_location_id
248 *
249 * Only for line_location if line id is null
250 * For line if location_id is null
251 *====================================================================*/
252 FUNCTION get_prorated_tax(x_header_id IN NUMBER, x_line_id IN NUMBER,
253 x_line_location_id IN NUMBER) return NUMBER
254
255
256 is
257
258
259 l_prorated_tax NUMBER;
260
261 BEGIN
262
263 IF(x_line_id is NULL and x_line_location_id is NULL) THEN
264 select SUM (( quantity_ordered - quantity_cancelled ) /
265 decode(quantity_ordered,0,1,quantity_ordered ) *
266 nonrecoverable_tax)
267 into l_prorated_tax
268 from po_distributions_all
269 where po_header_id = x_header_id;
270 END IF;
271
272 IF(x_line_id is NOT NULL and x_line_location_id is NULL) THEN
273 select SUM (( quantity_ordered - quantity_cancelled ) /
274 decode(quantity_ordered,0,1,quantity_ordered ) *
275 nonrecoverable_tax)
276 into l_prorated_tax
277 from po_distributions_all
278 where po_header_id = x_header_id
279 and po_line_id = x_line_id;
280
281 END IF;
282
283 IF(x_line_id is NULL and x_line_location_id is NOT NULL) THEN
284 select SUM (( quantity_ordered - quantity_cancelled ) /
285 decode(quantity_ordered,0,1,quantity_ordered ) *
286 nonrecoverable_tax)
287 into l_prorated_tax
288 from po_distributions_all
289 where po_header_id = x_header_id
290 and line_location_id = x_line_location_id;
291 END IF;
292
293 return l_prorated_tax;
294
295 END get_prorated_tax;
296
297 FUNCTION get_amount_billed(x_header_id IN NUMBER, x_line_id IN NUMBER) return NUMBER is
298
299 l_amount_billed NUMBER;
300
301 BEGIN
302
303 IF(x_line_id is NULL) THEN
304
305 select sum(amount_billed)
306 into l_amount_billed
307 from po_distributions_all
308 where po_header_id = x_header_id;
309
310 END IF;
311
312 IF(x_line_id is NOT NULL) THEN
313
314 select sum(amount_billed)
315 into l_amount_billed
316 from po_distributions_all
317 where po_header_id = x_header_id
318 and po_line_id = x_line_id;
319
320 END IF;
321
322 return l_amount_billed;
323
324 end get_amount_billed;
325
326
327 END PO_DOCUMENT_UTIL_PVT;