1 PACKAGE BODY GMF_RA_GET_FOB_CODES AS
2 /* $Header: gmffobcb.pls 115.0 99/07/16 04:17:36 porting shi $ */
3 /* CURSOR fob_codes( startdate varchar2,
4 enddate varchar2, */
5 CURSOR fob_codes( startdate date,
6 enddate date,
7 lookupcode varchar2) IS
8 SELECT lookup_code,
9 description,
10 creation_date,
11 created_by,
12 last_update_date,
13 last_updated_by,
14 inactive_date
15 FROM po_lookup_codes
16 WHERE lookup_type = 'FOB' AND
17 lookup_code like lookupcode AND
18 last_update_date BETWEEN
19 nvl(startdate,last_update_date) AND
20 nvl(enddate,last_update_date);
21
22 function get_name(usr_id number) return varchar2 is
23 usr_name varchar2(100);
24 begin
25 select user_name into usr_name from fnd_user where
26 user_id=usr_id;
27 return(usr_name);
28 end;
29 /* SIERRA COMMENTED datatype changed to date */
30 /* PROCEDURE ra_get_fob_codes( startdate in varchar2,
31 enddate in varchar2, */
32 PROCEDURE ra_get_fob_codes( startdate in date,
33 enddate in date,
34 lookupcode in out varchar2,
35 description out varchar2,
36 creation_date out varchar2,
37 created_by out number,
38 last_update_date out varchar2,
39 last_updated_by out number,
40 row_to_fetch in out number,
41 statuscode out number,
42 inactive_status out number) is
43 /* ad_by number;*/
44 /* mod_by number;*/
45 inactive_date date;
46 BEGIN
47 inactive_status := 0;
48 IF NOT fob_codes%ISOPEN THEN
49 OPEN fob_codes(startdate,enddate,lookupcode);
50 END IF;
51 FETCH fob_codes
52 INTO lookupcode ,
53 description ,
54 creation_date,
55 created_by,
56 last_update_date,
57 last_updated_by,
58 inactive_date;
59 IF fob_codes%NOTFOUND THEN
60 CLOSE fob_codes;
61 statuscode := 100;
62 END IF;
63 IF row_to_fetch = 1 and fob_codes%ISOPEN THEN
64 CLOSE fob_codes;
65 END IF;
66 /* added_by := get_name( ad_by);*/
67 /* modified_by := get_name( mod_by);*/
68 IF inactive_date <= SYSDATE THEN
69 inactive_status := 1;
70 END IF;
71 EXCEPTION
72 WHEN OTHERS THEN
73 statuscode := SQLCODE;
74 END ra_get_fob_codes;
75 END GMF_RA_GET_FOB_CODES;
[Home] [Help]