1 PACKAGE BODY GML_AUDIT_REPORTS AS
2 /* $Header: GMLOGMCB.pls 120.1 2005/09/30 13:41:02 pbamb noship $ */
3
4 FUNCTION get_ofi_line_count(p_hid NUMBER)
5 RETURN NUMBER
6 IS
7 l_linecnt NUMBER;
8
9 CURSOR lcnt_cur IS
10 SELECT count(*)
11 FROM po_line_locations_all
12 WHERE po_header_id = p_hid;
13
14 BEGIN
15 OPEN lcnt_cur;
16 FETCH lcnt_cur
17 INTO l_linecnt;
18
19 IF lcnt_cur%NOTFOUND THEN
20 l_linecnt:=0;
21 END IF;
22
23 CLOSE lcnt_cur;
24
25 RETURN l_linecnt;
26
27 END get_ofi_line_count;
28
29 FUNCTION get_ofi_bline_count(p_hid NUMBER)
30 RETURN NUMBER
31 IS
32 l_linecnt NUMBER;
33
34 CURSOR lcnt_cur IS
35 SELECT count(*)
36 FROM po_line_locations_all
37 WHERE po_header_id = p_hid
38 AND nvl(po_release_id, 0) > 0;
39
40 BEGIN
41
42 OPEN lcnt_cur;
43 FETCH lcnt_cur
44 INTO l_linecnt;
45
46 IF lcnt_cur%NOTFOUND THEN
47 l_linecnt:=0;
48 END IF;
49
50 CLOSE lcnt_cur;
51
52 RETURN l_linecnt;
53
54 END get_ofi_bline_count;
55
56 FUNCTION get_ofi_total_cost(p_hid NUMBER)
57 RETURN NUMBER
58 IS
59 l_cost NUMBER;
60
61 CURSOR cost_cur IS
62 SELECT SUM(quantity*unit_price)
63 FROM po_lines_all
64 WHERE po_header_id=p_hid;
65
66 BEGIN
67 OPEN cost_cur;
68 FETCH cost_cur INTO l_cost;
69 IF cost_cur%NOTFOUND
70 THEN
71 l_cost:=0;
72 END IF;
73 CLOSE cost_cur;
74
75 RETURN l_cost;
76
77 END get_ofi_total_cost;
78
79 FUNCTION get_ofi_btotal_cost(p_hid NUMBER) RETURN NUMBER IS
80
81 l_cost NUMBER;
82
83 CURSOR cost_cur IS
84 SELECT SUM(quantity*price_override)
85 FROM po_line_locations_all
86 WHERE po_header_id=p_hid
87 AND nvl(po_release_id,0) > 0;
88
89 BEGIN
90 OPEN cost_cur;
91 FETCH cost_cur INTO l_cost;
92 IF cost_cur%NOTFOUND
93 THEN
94 l_cost:=0;
95 END IF;
96 CLOSE cost_cur;
97
98 RETURN l_cost;
99
100 END get_ofi_btotal_cost;
101
102 FUNCTION get_gemms_line_count(p_id NUMBER) RETURN NUMBER IS
103
104 l_linecnt NUMBER;
105
106 CURSOR lcnt_cur IS
107 SELECT count(*)
108 FROM po_ordr_dtl
109 WHERE po_id=p_id;
110
111 BEGIN
112 OPEN lcnt_cur;
113 FETCH lcnt_cur INTO l_linecnt;
114 IF lcnt_cur%NOTFOUND
115 THEN
116 l_linecnt:=0;
117 END IF;
118 CLOSE lcnt_cur;
119
120 RETURN l_linecnt;
121
122 END get_gemms_line_count;
123
124 FUNCTION get_gemms_bline_count(p_hid NUMBER) RETURN NUMBER IS
125
126 l_linecnt NUMBER;
127
128 CURSOR lcnt_cur IS
129 SELECT count(*)
130 FROM po_ordr_dtl
131 WHERE po_id IN (SELECT po_id
132 FROM cpg_oragems_mapping f
133 WHERE f.po_header_id = p_hid
134 AND nvl(po_release_id,0)>0);
135
136 BEGIN
137 OPEN lcnt_cur;
138 FETCH lcnt_cur INTO l_linecnt;
139 IF lcnt_cur%NOTFOUND
140 THEN
141 l_linecnt:=0;
142 END IF;
143 CLOSE lcnt_cur;
144
145 RETURN l_linecnt;
146
147 END get_gemms_bline_count;
148
149 FUNCTION get_gemms_total_cost(p_id NUMBER) RETURN NUMBER IS
150
151 l_cost NUMBER;
152
153 CURSOR cost_cur IS
154 SELECT SUM(order_qty1*net_price)
155 FROM po_ordr_dtl
156 WHERE po_id=p_id
157 AND cancellation_code is NULL;
158
159 BEGIN
160 OPEN cost_cur;
161 FETCH cost_cur INTO l_cost;
162 IF cost_cur%NOTFOUND
163 THEN
164 l_cost:=0;
165 END IF;
166 CLOSE cost_cur;
167
168 RETURN l_cost;
169
170 END get_gemms_total_cost;
171
172 FUNCTION get_gemms_btotal_cost(p_hid NUMBER) RETURN NUMBER IS
173
174 l_cost NUMBER;
175
176 CURSOR cost_cur IS
177 SELECT SUM(order_qty1*net_price)
178 FROM po_ordr_dtl
179 WHERE po_id IN (SELECT po_id
180 FROM cpg_oragems_mapping f
181 WHERE f.po_header_id = p_hid
182 AND nvl(po_release_id,0)>0);
183
184 BEGIN
185 OPEN cost_cur;
186 FETCH cost_cur INTO l_cost;
187 IF cost_cur%NOTFOUND
188 THEN
189 l_cost:=0;
190 END IF;
191 CLOSE cost_cur;
192
193 RETURN l_cost;
194
195 END get_gemms_btotal_cost;
196
197 FUNCTION match_ofi_gms_po_line(p_ohid NUMBER,
198 p_olid NUMBER,
199 p_ollid NUMBER,
200 p_gid NUMBER,
201 p_glid NUMBER) RETURN VARCHAR2 IS
202 l_ret VARCHAR2(1);
203
204 CURSOR ofi_gms_mch_cur IS
205 SELECT 'Y'
206 FROM cpg_oragems_mapping a
207 WHERE a.po_header_id=p_ohid
208 AND a.po_line_id = p_olid
209 AND a.po_line_location_id=p_ollid
210 AND a.po_id=p_gid
211 AND a.line_id=p_glid;
212
213 BEGIN
214
215 OPEN ofi_gms_mch_cur;
216 FETCH ofi_gms_mch_cur INTO l_ret;
217 IF ofi_gms_mch_cur%NOTFOUND
218 THEN
219 l_ret:='N';
220 END IF;
221 CLOSE ofi_gms_mch_cur;
222
223 RETURN l_ret;
224
225 END match_ofi_gms_po_line;
226
227 FUNCTION chk_ofi_gms_poln_itm( p_olid NUMBER,
228 p_glid NUMBER) RETURN VARCHAR2 IS
229
230 l_gms_itm ic_item_mst.item_no%TYPE;
231 l_ofi_itm mtl_system_items.segment1%TYPE;
232
233 CURSOR gms_itm_cur IS
234 SELECT a.item_no
235 FROM ic_item_mst a,
236 po_ordr_dtl b
237 WHERE a.item_id=b.item_id
238 AND b.line_id=p_glid;
239
240 CURSOR ofi_itm_cur IS
241 SELECT segment1
242 FROM po_lines_all a,
243 mtl_system_items b
244 WHERE a.item_id=b.inventory_item_id
245 AND a.po_line_id=p_olid;
246 BEGIN
247 OPEN gms_itm_cur;
248 FETCH gms_itm_cur INTO l_gms_itm;
249 CLOSE gms_itm_cur;
250
251 OPEN ofi_itm_cur;
252 FETCH ofi_itm_cur INTO l_ofi_itm;
253 CLOSE ofi_itm_cur;
254
255 IF l_gms_itm IS NULL OR
256 l_ofi_itm IS NULL
257 THEN
258 RETURN 'Y';
259 END IF;
260
261 IF l_gms_itm<>l_ofi_itm
262 THEN
263 RETURN 'Y';
264 END IF;
265
266 RETURN 'N';
267
268 END chk_ofi_gms_poln_itm;
269
270 FUNCTION chk_ofi_gms_poln_sts( p_ollid NUMBER,
271 p_glid NUMBER) RETURN VARCHAR2 IS
272 l_gms_stat VARCHAR2(20);
273 l_ofi_stat VARCHAR2(20);
274
275 CURSOR gms_sts_cur IS
276 SELECT DECODE(po_status,0,'OPEN',1,'CANCEL',20,'CLOSE','X')
277 FROM po_ordr_dtl
278 WHERE line_id=p_glid;
279
280 CURSOR ofi_sts_cur IS
281 SELECT DECODE(closed_code,'OPEN','OPEN','CLOSED','CLOSE',
282 'FINALLY CLOSED','CLOSE','CLOSED FOR RECEIVING','CLOSE','OPEN')
283 FROM po_line_locations_all
284 WHERE line_location_id=p_ollid;
285
286 BEGIN
287 OPEN gms_sts_cur;
288 FETCH gms_sts_cur INTO l_gms_stat;
289 CLOSE gms_sts_cur;
290
291 OPEN ofi_sts_cur;
292 FETCH ofi_sts_cur INTO l_ofi_stat;
293 CLOSE ofi_sts_cur;
294
295 IF l_gms_stat IS NULL OR
296 l_ofi_stat IS NULL
297 THEN
298 RETURN 'Y';
299 END IF;
300
301 IF l_gms_stat<>l_ofi_stat
302 THEN
303 RETURN 'Y';
304 END IF;
305
306 RETURN 'N';
307
308 END chk_ofi_gms_poln_sts;
309
310
311
312 FUNCTION get_po_num( p_ponum VARCHAR2)
313 RETURN VARCHAR2
314 IS
315 position VARCHAR2(500) := 1;
316 po_num VARCHAR2(100);
317 BEGIN
318
319 SELECT instr(p_ponum, '-', -1)
320 INTO position
321 FROM dual;
322
323 if position = 0 then
324 return p_ponum;
325 end if;
326
327 po_num := LTRIM(substr(p_ponum, 1, position-1), '0');
328 return po_num;
329
330 END get_po_num;
331
332
333 FUNCTION get_rel_num( p_ponum VARCHAR2) RETURN NUMBER IS
334 position varchar2(500) := 1;
335 rel_num varchar2(500);
336 BEGIN
337
338 select instr(p_ponum, '-', -1) into position
339 from dual;
340
341 if position = 0 then
342 return null;
343 end if;
344
345 rel_num := to_number(substr(p_ponum, position+1));
346 return rel_num;
347
348 END get_rel_num;
349
350 END GML_AUDIT_REPORTS;
[Home] [Help]