PACKAGE BODY: APPS.INV_LOT_AG

Source

1 PACKAGE BODY INV_LOT_AG AS
2 /* $Header: INVLOTGB.pls 120.1 2005/06/11 08:31:34 appldev  $ */
3 
4 /* Global constant holding package name */
5 G_PKG_NAME CONSTANT VARCHAR2(20) := 'INV_LOT_AG' ;
6 
7 
8 PROCEDURE update_lot_age( x_retcode        OUT NOCOPY /* file.sql.39 change */ VARCHAR2
9                           ,x_errbuf        OUT NOCOPY /* file.sql.39 change */ VARCHAR2
10                           ,p_age_for_expired_lots IN VARCHAR2
11 			) IS
12     var_age_for_expired_lots varchar2(10);
13     var_enabled number;
14     var_age     number;
15 
16     cursor cur_lots_not_expired is
17            select  ROWID,
18                    INVENTORY_ITEM_ID,
19                    ORGANIZATION_ID ,
20                    CREATION_DATE,
21                    EXPIRATION_DATE,
22                    ORIGINATION_DATE
23              from  mtl_lot_numbers
24             where  trunc(expiration_date) <= nvl(origination_date,creation_date);
25 
26     cursor cur_lots_all is
27            select  ROWID,
28                    INVENTORY_ITEM_ID,
29                    ORGANIZATION_ID ,
30                    CREATION_DATE,
31                    EXPIRATION_DATE,
32                    ORIGINATION_DATE
33              from  mtl_lot_numbers;
34 
35     lot_rec cur_lots_all%rowtype;
36 
37 BEGIN
38 
39 
40   -- Initialize the Message Stack
41   FND_MSG_PUB.Initialize;
42 
43     var_age_for_expired_lots := p_age_for_expired_lots;
44     --dbms_output.enable(1000000);
45 
46     if var_age_for_expired_lots = 'N'
47     then
48     -- Processing for All Lots
49        open cur_lots_not_expired;
50        loop
51           fetch cur_lots_not_expired into lot_rec;
52           exit when cur_lots_not_expired%notfound;
53 
54           -- Processing for context value AGE
55 
56           var_enabled := INV_LOT_SEL_ATTR.is_enabled_segment('Lot Attributes','AGE',
57                                       lot_rec.organization_id,
58                                       lot_rec.inventory_item_id
59                                      );
60 
61           --dbms_output.put_line('con ' || var_enabled);
62 
63           if var_enabled > 0
64           then
65 
66           -- Updater the AGE column.
67              --dbms_output.put_line('before update');
68              update mtl_lot_numbers
69                set age= trunc(sysdate) - trunc(nvl(lot_rec.origination_date,lot_rec.creation_date))
70               where rowid = lot_rec.rowid;
71           end if;
72        end loop;
73        close cur_lots_not_expired;
74     else
75     -- Processing for  Lots not expired.
76        open cur_lots_all;
77        loop
78           fetch cur_lots_all into lot_rec;
79           exit when cur_lots_all%notfound;
80 
81           -- Processing for context value AGE
82 
83           var_enabled := INV_LOT_SEL_ATTR.is_enabled_segment('Lot Attributes','AGE',
84                                       lot_rec.organization_id,
85                                       lot_rec.inventory_item_id
86                                      );
87 
88           --dbms_output.put_line('con ' || var_enabled);
89 
90           if var_enabled > 0
91           then
92           -- Updater the AGE column.
93              --dbms_output.put_line('before update');
94              update mtl_lot_numbers
95                set age= trunc(sysdate) - trunc(nvl(lot_rec.origination_date,lot_rec.creation_date))
96               where rowid = lot_rec.rowid;
97           end if;
98        end loop;
99        close cur_lots_all;
100     end if;
101 
102 COMMIT;
103 x_errbuf  := NULL;
104 x_retcode := 0;
105 
106 Exception
107 When others then
108    IF
109     FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
110    THEN
111     FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
112                           , 'Update Age for Lot Attributes '
113                           );
114    END IF;
115 
116   x_errbuf  := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
117   x_retcode := 2;
118   RAISE;
119 
120 END update_lot_age;
121 
122 END INV_LOT_AG;