[Home] [Help]
PACKAGE BODY: APPS.CSP_STOCK_LISTS_PKG
Source
1 PACKAGE BODY CSP_STOCK_LISTS_PKG as
2 /* $Header: csptpslb.pls 115.3 2002/11/26 06:43:07 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name : CSP_STOCK_LISTS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_STOCK_LISTS_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptpslb.pls';
12 PROCEDURE Insert_Row(
13 p_ORGANIZATION_ID NUMBER
14 ,p_INVENTORY_ITEM_ID NUMBER
15 ,p_SUBINVENTORY_CODE VARCHAR2
16 ,p_MANUAL_AUTO VARCHAR2
17 ,p_REASON_CODE VARCHAR2
18 ,p_CREATED_BY NUMBER
19 ,p_CREATION_DATE DATE
20 ,p_LAST_UPDATED_BY NUMBER
21 ,p_LAST_UPDATE_DATE DATE
22 ,p_LAST_UPDATE_LOGIN NUMBER
23 )
24 IS
25 BEGIN
26 INSERT INTO CSP_STOCK_LISTS(
27 ORGANIZATION_ID
28 ,INVENTORY_ITEM_ID
29 ,SUBINVENTORY_CODE
30 ,MANUAL_AUTO
31 ,REASON_CODE
32 ,CREATED_BY
33 ,CREATION_DATE
34 ,LAST_UPDATED_BY
35 ,LAST_UPDATE_DATE
36 ,LAST_UPDATE_LOGIN
37 ) VALUES (
38 decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID)
39 ,decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID)
40 ,decode( p_SUBINVENTORY_CODE, FND_API.G_MISS_CHAR, NULL, p_SUBINVENTORY_CODE)
41 ,decode( p_MANUAL_AUTO, FND_API.G_MISS_CHAR, NULL, p_MANUAL_AUTO)
42 ,decode( p_REASON_CODE, FND_API.G_MISS_CHAR, NULL, p_REASON_CODE)
43 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
44 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
45 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
46 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
47 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
48 );
49 End Insert_Row;
50 PROCEDURE Update_Row(
51 p_ORGANIZATION_ID NUMBER
52 ,p_INVENTORY_ITEM_ID NUMBER
53 ,p_SUBINVENTORY_CODE VARCHAR2
54 ,p_MANUAL_AUTO VARCHAR2
55 ,p_REASON_CODE VARCHAR2
56 ,p_CREATED_BY NUMBER
57 ,p_CREATION_DATE DATE
58 ,p_LAST_UPDATED_BY NUMBER
59 ,p_LAST_UPDATE_DATE DATE
60 ,p_LAST_UPDATE_LOGIN NUMBER
61 )
62 IS
63 BEGIN
64 Update CSP_STOCK_LISTS
65 SET
66 ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID)
67 ,INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID)
68 ,SUBINVENTORY_CODE = decode( p_SUBINVENTORY_CODE, FND_API.G_MISS_CHAR, SUBINVENTORY_CODE, p_SUBINVENTORY_CODE)
69 ,MANUAL_AUTO = decode( p_MANUAL_AUTO, FND_API.G_MISS_CHAR, MANUAL_AUTO, p_MANUAL_AUTO)
70 ,REASON_CODE = decode( p_REASON_CODE, FND_API.G_MISS_CHAR, REASON_CODE, p_REASON_CODE)
71 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
72 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
73 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
74 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
75 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
76 where INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
77 AND ORGANIZATION_ID = P_ORGANIZATION_ID
78 AND NVL(SUBINVENTORY_CODE,'X') = NVL(P_SUBINVENTORY_CODE,'X');
79
80 If (SQL%NOTFOUND) then
81 RAISE NO_DATA_FOUND;
82 End If;
83 END Update_Row;
84
85 PROCEDURE Delete_Row(
86 p_INVENTORY_ITEM_ID NUMBER
87 ,p_ORGANIZATION_ID NUMBER
88 ,p_SUBINVENTORY_CODE VARCHAR2)
89 IS
90 BEGIN
91 DELETE FROM CSP_STOCK_LISTS
92 WHERE INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
93 AND ORGANIZATION_ID = P_ORGANIZATION_ID
94 AND NVL(SUBINVENTORY_CODE,'X') = NVL(P_SUBINVENTORY_CODE,'X');
95
96 If (SQL%NOTFOUND) then
97 RAISE NO_DATA_FOUND;
98 End If;
99 END Delete_Row;
100 PROCEDURE Lock_Row(
101 p_ORGANIZATION_ID NUMBER
102 ,p_INVENTORY_ITEM_ID NUMBER
103 ,p_SUBINVENTORY_CODE VARCHAR2
104 ,p_MANUAL_AUTO VARCHAR2
105 ,p_REASON_CODE VARCHAR2
106 ,p_CREATED_BY NUMBER
107 ,p_CREATION_DATE DATE
108 ,p_LAST_UPDATED_BY NUMBER
109 ,p_LAST_UPDATE_DATE DATE
110 ,p_LAST_UPDATE_LOGIN NUMBER
111 )
112 IS
113 CURSOR C IS
114 SELECT *
115 FROM CSP_STOCK_LISTS
116 WHERE INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
117 FOR UPDATE of INVENTORY_ITEM_ID NOWAIT;
118 Recinfo C%ROWTYPE;
119 BEGIN
120 OPEN C;
121 FETCH C INTO Recinfo;
122 If (C%NOTFOUND) then
123 CLOSE C;
124 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
125 APP_EXCEPTION.RAISE_EXCEPTION;
126 End If;
127 CLOSE C;
128 if (
129 ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
130 AND ( ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
131 OR ( ( Recinfo.INVENTORY_ITEM_ID IS NULL )
132 AND ( p_INVENTORY_ITEM_ID IS NULL )))
133 AND ( ( Recinfo.SUBINVENTORY_CODE = p_SUBINVENTORY_CODE)
134 OR ( ( Recinfo.SUBINVENTORY_CODE IS NULL )
135 AND ( p_SUBINVENTORY_CODE IS NULL )))
136 AND ( ( Recinfo.MANUAL_AUTO = p_MANUAL_AUTO)
137 OR ( ( Recinfo.MANUAL_AUTO IS NULL )
138 AND ( p_MANUAL_AUTO IS NULL )))
139 AND ( ( Recinfo.REASON_CODE = p_REASON_CODE)
140 OR ( ( Recinfo.REASON_CODE IS NULL )
141 AND ( p_REASON_CODE IS NULL )))
142 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
143 OR ( ( Recinfo.CREATED_BY IS NULL )
144 AND ( p_CREATED_BY IS NULL )))
145 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
146 OR ( ( Recinfo.CREATION_DATE IS NULL )
147 AND ( p_CREATION_DATE IS NULL )))
148 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
149 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
150 AND ( p_LAST_UPDATED_BY IS NULL )))
151 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
152 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
153 AND ( p_LAST_UPDATE_DATE IS NULL )))
154 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
155 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
156 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
157 ) then
158 return;
159 else
160 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 End If;
163 END Lock_Row;
164 End CSP_STOCK_LISTS_PKG;