PACKAGE BODY: APPS.MTL_STATUS_ATTRIB_VAL_PKG

Source

1 PACKAGE BODY MTL_STATUS_ATTRIB_VAL_PKG as
2 /* $Header: INVSDOSB.pls 120.1 2005/07/01 12:56:28 appldev ship $ */
3 
4 
5   PROCEDURE Lock_Row(X_Rowid                            VARCHAR2,
6 
7                      X_Inventory_Item_Status_Code       VARCHAR2,
8                      X_Attribute_Name                   VARCHAR2,
9                      X_Attribute_Value                  VARCHAR2
10   ) IS
11     CURSOR C IS
12         SELECT *
13         FROM   MTL_STATUS_ATTRIBUTE_VALUES
14         WHERE  rowid = X_Rowid
15         FOR UPDATE of Inventory_Item_Status_Code NOWAIT;
16     Recinfo C%ROWTYPE;
17 
18 
19   BEGIN
20     OPEN C;
21     FETCH C INTO Recinfo;
22     if (C%NOTFOUND) then
23       CLOSE C;
24       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
25       APP_EXCEPTION.Raise_Exception;
26     end if;
27     CLOSE C;
28     if ( (Recinfo.inventory_item_status_code =  X_Inventory_Item_Status_Code)
29            AND (Recinfo.attribute_name =  X_Attribute_Name)
30            AND (Recinfo.attribute_value =  X_Attribute_Value)) then
31       return;
32     else
33       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
34       APP_EXCEPTION.Raise_Exception;
35     end if;
36   END Lock_Row;
37 
38 
39   PROCEDURE Update_Row(X_Rowid                          VARCHAR2,
40                        X_Inventory_Item_Status_Code     VARCHAR2,
41                        X_Status_Code_Ndb                NUMBER,
42                        X_Attribute_Name                 VARCHAR2,
43                        X_Attribute_Value                VARCHAR2,
44                        X_Old_Attribute_Value            VARCHAR2,
45                        X_Last_Update_Date               DATE,
46                        X_Last_Updated_By                NUMBER,
47                        X_Last_Update_Login              NUMBER
48 
49   ) IS
50   BEGIN
51 
52     IF ((X_Attribute_Value <> X_Old_Attribute_Value) AND
53         (X_STATUS_CODE_NDB = 1))  THEN
54         INVUPDAT.UPDATE_ATTRIBUTES( X_Attribute_Name,
55                                     X_Attribute_Value,
56                                     X_Inventory_Item_Status_Code);
57     END IF;
58 
59     UPDATE MTL_STATUS_ATTRIBUTE_VALUES
60     SET
61        inventory_item_status_code      =     X_Inventory_Item_Status_Code,
62        attribute_name                  =     X_Attribute_Name,
63        attribute_value                 =     X_Attribute_Value,
64        last_update_date                =     X_Last_Update_Date,
65        last_updated_by                 =     X_Last_Updated_By,
66        last_update_login               =     X_Last_Update_Login
67     WHERE rowid = X_Rowid;
68 
69     if (SQL%NOTFOUND) then
70       Raise NO_DATA_FOUND;
71     end if;
72   END Update_Row;
73 
74 
75   PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
76   BEGIN
77     DELETE FROM MTL_STATUS_ATTRIBUTE_VALUES
78     WHERE rowid = X_Rowid;
79 
80     if (SQL%NOTFOUND) then
81       Raise NO_DATA_FOUND;
82     end if;
83   END Delete_Row;
84 
85 
86 ------ BUG #867987 : Changes made in the following procedure.
87 ------ Adding variable 'userid' to populate the last_updated_by and created_by
88 ------        fields of MTL_STATUS_ATTRIBUTE_VALUES. Earlier 'logid' was used instead.
89 
90  PROCEDURE     Populate_Tab ( status_code IN VARCHAR2 )
91  IS
92    logid  NUMBER;
93    userid NUMBER;
94  BEGIN
95    logid := FND_GLOBAL.LOGIN_ID;
96    userid := FND_GLOBAL.USER_ID;
97 
98    insert into mtl_status_attribute_values
99   ( inventory_item_status_code,
100     attribute_name,
101     attribute_value,
102     last_update_date,
103     last_updated_by,
104     creation_date,
105     created_by,
106     last_update_login)
107   select status_code,
108         attr.attribute_name,
109         'N',
110         sysdate,
111         userid,
112         sysdate,
113         userid,
114         logid
115  from   mtl_item_attributes attr
116  where  attr.STATUS_CONTROL_CODE is not NULL;
117 
118   exception
119     when others then
120          raise_application_error(-20001, sqlerrm);
121 
122  END Populate_Tab;
123 
124 END MTL_STATUS_ATTRIB_VAL_PKG;