[Home] [Help]
PACKAGE BODY: APPS.CSD_WIP_TRANSACTION_DTLS_PKG
Source
1 PACKAGE BODY CSD_WIP_TRANSACTION_DTLS_PKG as
2 /* $Header: csdtwtdb.pls 120.7.12010000.2 2008/10/15 20:29:08 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_WIP_TRANSACTION_DTLS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_WIP_TRANSACTION_DTLS_PKG';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtdwdb.pls';
14 l_debug NUMBER ;
15
16 PROCEDURE Insert_Row(
17 px_WIP_TRANSACTION_DETAIL_ID IN OUT NOCOPY NUMBER
18 ,p_CREATED_BY IN NUMBER
19 ,p_CREATION_DATE IN DATE
20 ,p_LAST_UPDATED_BY IN NUMBER
21 ,p_LAST_UPDATE_DATE IN DATE
22 ,p_LAST_UPDATE_LOGIN IN NUMBER
23 ,p_INVENTORY_ITEM_ID IN NUMBER
24 ,p_WIP_ENTITY_ID IN NUMBER
25 ,p_OPERATION_SEQ_NUM IN NUMBER
26 ,p_RESOURCE_SEQ_NUM IN NUMBER
27 ,p_employee_id IN NUMBER
28 ,p_TRANSACTION_QUANTITY IN NUMBER
29 ,p_TRANSACTION_UOM IN VARCHAR2
30 ,p_SERIAL_NUMBER IN VARCHAR2
31 ,p_REVISION IN VARCHAR2 -- swai: bug 7182047 (FP of 6995498)
32 -- swai bug 6841113: added material txn reason code
33 ,p_REASON_ID IN NUMBER
34 -- swai: added code for operations
35 ,p_BACKFLUSH_FLAG IN NUMBER
36 ,p_COUNT_POINT_TYPE IN NUMBER
37 ,p_DEPARTMENT_ID IN NUMBER
38 ,p_DESCRIPTION IN VARCHAR2
39 ,p_FIRST_UNIT_COMPLETION_DATE IN DATE
40 ,p_FIRST_UNIT_START_DATE IN DATE
41 ,p_LAST_UNIT_COMPLETION_DATE IN DATE
42 ,p_LAST_UNIT_START_DATE IN DATE
43 ,p_MINIMUM_TRANSFER_QUANTITY IN NUMBER
44 ,p_STANDARD_OPERATION_ID IN NUMBER
45 )
46
47 IS
48 CURSOR C2 IS SELECT CSD_WIP_TRANSACTION_DETAILS_S1.nextval FROM sys.dual;
49 l_object_version_number NUMBER := 1;
50 BEGIN
51 If (px_WIP_TRANSACTION_DETAIL_ID IS NULL) OR (px_WIP_TRANSACTION_DETAIL_ID = FND_API.G_MISS_NUM) then
52 OPEN C2;
53 FETCH C2 INTO px_WIP_TRANSACTION_DETAIL_ID;
54 CLOSE C2;
55 End If;
56 INSERT INTO CSD_WIP_TRANSACTION_DETAILS(
57 WIP_TRANSACTION_DETAIL_ID
58 ,CREATED_BY
59 ,CREATION_DATE
60 ,LAST_UPDATED_BY
61 ,LAST_UPDATE_DATE
62 ,LAST_UPDATE_LOGIN
63 ,INVENTORY_ITEM_ID
64 ,WIP_ENTITY_ID
65 ,OPERATION_SEQ_NUM
66 ,RESOURCE_SEQ_NUM
67 ,employee_id
68 ,TRANSACTION_QUANTITY
69 ,TRANSACTION_UOM
70 ,SERIAL_NUMBER
71 ,REVISION -- swai: bug 7182047 (FP of 6995498)
72 , REASON_ID -- swai bug 6841113: added material txn reason code
73 -- swai: added code for operations
74 ,BACKFLUSH_FLAG
75 ,COUNT_POINT_TYPE
76 ,DEPARTMENT_ID
77 ,DESCRIPTION
78 ,FIRST_UNIT_COMPLETION_DATE
79 ,FIRST_UNIT_START_DATE
80 ,LAST_UNIT_COMPLETION_DATE
81 ,LAST_UNIT_START_DATE
82 ,MINIMUM_TRANSFER_QUANTITY
83 ,STANDARD_OPERATION_ID
84 -- end swai: added code for operations
85 ,OBJECT_VERSION_NUMBER
86 ) VALUES (
87 px_WIP_TRANSACTION_DETAIL_ID
88 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
89 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
90 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
91 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
92 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
93 ,decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID)
94 ,decode( p_WIP_ENTITY_ID, FND_API.G_MISS_NUM, NULL, p_WIP_ENTITY_ID)
95 ,decode( p_OPERATION_SEQ_NUM, FND_API.G_MISS_NUM, NULL, p_OPERATION_SEQ_NUM)
96 ,decode( p_RESOURCE_SEQ_NUM, FND_API.G_MISS_NUM, NULL, p_RESOURCE_SEQ_NUM)
97 ,decode( p_employee_id, FND_API.G_MISS_NUM, NULL, p_employee_id)
98 ,decode( p_TRANSACTION_QUANTITY, FND_API.G_MISS_NUM, NULL, p_TRANSACTION_QUANTITY)
99 ,decode( p_TRANSACTION_UOM, FND_API.G_MISS_CHAR, NULL, p_TRANSACTION_UOM)
100 ,decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, p_SERIAL_NUMBER)
101 -- swai: bug 7182047 (FP of 6995498) added item revision
102 ,decode( p_REVISION, FND_API.G_MISS_CHAR, NULL, p_REVISION)
103 -- end swai: bug 7182047 (FP of 6995498) added item revision
104 -- swai bug 6841113: added material txn reason code
105 ,decode( p_REASON_ID, FND_API.G_MISS_NUM, NULL, p_REASON_ID)
106 -- end swai bug 6841113: added material txn reason code
107 -- swai: added code for operations
108 ,decode( p_BACKFLUSH_FLAG, FND_API.G_MISS_NUM, NULL, p_BACKFLUSH_FLAG)
109 ,decode( p_COUNT_POINT_TYPE, FND_API.G_MISS_NUM, NULL, p_COUNT_POINT_TYPE)
110 ,decode( p_DEPARTMENT_ID, FND_API.G_MISS_NUM, NULL, p_DEPARTMENT_ID)
111 ,decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION)
112 ,decode( p_FIRST_UNIT_COMPLETION_DATE, FND_API.G_MISS_DATE, NULL, p_FIRST_UNIT_COMPLETION_DATE)
113 ,decode( p_FIRST_UNIT_START_DATE, FND_API.G_MISS_DATE, NULL, p_FIRST_UNIT_START_DATE)
114 ,decode( p_LAST_UNIT_COMPLETION_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UNIT_COMPLETION_DATE)
115 ,decode( p_LAST_UNIT_START_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UNIT_START_DATE)
116 ,decode( p_MINIMUM_TRANSFER_QUANTITY, FND_API.G_MISS_NUM, NULL, p_MINIMUM_TRANSFER_QUANTITY)
117 ,decode( p_STANDARD_OPERATION_ID, FND_API.G_MISS_NUM, NULL, p_STANDARD_OPERATION_ID)
118 -- end swai: added code for operations
119 ,l_object_version_number
120 );
121 End Insert_Row;
122
123 PROCEDURE Update_Row(
124 p_WIP_TRANSACTION_DETAIL_ID IN NUMBER
125 ,p_CREATED_BY IN NUMBER
126 ,p_CREATION_DATE IN DATE
127 ,p_LAST_UPDATED_BY IN NUMBER
128 ,p_LAST_UPDATE_DATE IN DATE
129 ,p_LAST_UPDATE_LOGIN IN NUMBER
130 ,p_INVENTORY_ITEM_ID IN NUMBER
131 ,p_WIP_ENTITY_ID IN NUMBER
132 ,p_OPERATION_SEQ_NUM IN NUMBER
133 ,p_RESOURCE_SEQ_NUM IN NUMBER
134 ,p_employee_id IN NUMBER
135 ,p_TRANSACTION_QUANTITY IN NUMBER
136 ,p_TRANSACTION_UOM IN VARCHAR2
137 ,p_SERIAL_NUMBER IN VARCHAR2
138 ,p_REVISION IN VARCHAR2 -- swai: bug 7182047 (FP of 6995498)
139 -- swai bug 6841113: added material txn reason code
140 ,p_REASON_ID IN NUMBER
141 -- swai: added code for operations
142 ,p_BACKFLUSH_FLAG IN NUMBER
143 ,p_COUNT_POINT_TYPE IN NUMBER
144 ,p_DEPARTMENT_ID IN NUMBER
145 ,p_DESCRIPTION IN VARCHAR2
146 ,p_FIRST_UNIT_COMPLETION_DATE IN DATE
147 ,p_FIRST_UNIT_START_DATE IN DATE
148 ,p_LAST_UNIT_COMPLETION_DATE IN DATE
149 ,p_LAST_UNIT_START_DATE IN DATE
150 ,p_MINIMUM_TRANSFER_QUANTITY IN NUMBER
151 ,p_STANDARD_OPERATION_ID IN NUMBER
152 ,p_OBJECT_VERSION_NUMBER IN NUMBER
153 )
154
155 IS
156 BEGIN
157 Update CSD_WIP_TRANSACTION_DETAILS
158 SET
159 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, p_CREATED_BY)
160 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, p_CREATION_DATE)
161 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
162 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
163 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
164 ,INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, NULL, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID)
165 ,WIP_ENTITY_ID = decode( p_WIP_ENTITY_ID, FND_API.G_MISS_NUM, NULL, NULL, WIP_ENTITY_ID, p_WIP_ENTITY_ID)
166 ,OPERATION_SEQ_NUM = decode( p_OPERATION_SEQ_NUM, FND_API.G_MISS_NUM, NULL, NULL, OPERATION_SEQ_NUM, p_OPERATION_SEQ_NUM)
167 ,RESOURCE_SEQ_NUM = decode( p_RESOURCE_SEQ_NUM, FND_API.G_MISS_NUM, NULL, NULL, RESOURCE_SEQ_NUM, p_RESOURCE_SEQ_NUM)
168 ,employee_id = decode( p_employee_id, FND_API.G_MISS_NUM, NULL, NULL, employee_id , p_employee_id)
169 ,TRANSACTION_QUANTITY = decode( p_TRANSACTION_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, TRANSACTION_QUANTITY, p_TRANSACTION_QUANTITY)
170 ,TRANSACTION_UOM = decode( p_TRANSACTION_UOM, FND_API.G_MISS_CHAR, NULL, NULL, TRANSACTION_UOM, p_TRANSACTION_UOM)
171 ,SERIAL_NUMBER = decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, NULL, SERIAL_NUMBER, p_SERIAL_NUMBER)
172 -- swai: bug 7182047 (FP of 6995498) added item revision
173 ,REVISION = decode( p_REVISION, FND_API.G_MISS_CHAR, NULL, NULL, REVISION, p_REVISION)
174 -- end swai: bug 7182047 (FP of 6995498) added item revision
175 -- swai bug 6841113: added material txn reason code
176 ,REASON_ID = decode( p_REASON_ID, FND_API.G_MISS_NUM, NULL, NULL, REASON_ID, p_REASON_ID)
177 -- end swai bug 6841113: added material txn reason code
178 -- swai: added code for operations
179 ,BACKFLUSH_FLAG = decode( p_BACKFLUSH_FLAG, FND_API.G_MISS_NUM, NULL, NULL, BACKFLUSH_FLAG, p_BACKFLUSH_FLAG)
180 ,COUNT_POINT_TYPE = decode( p_COUNT_POINT_TYPE, FND_API.G_MISS_NUM, NULL, NULL, COUNT_POINT_TYPE, p_COUNT_POINT_TYPE)
181 ,DEPARTMENT_ID = decode( p_DEPARTMENT_ID, FND_API.G_MISS_NUM, NULL, NULL, DEPARTMENT_ID, p_DEPARTMENT_ID)
182 ,DESCRIPTION = decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, NULL, DESCRIPTION, p_DESCRIPTION)
183 ,FIRST_UNIT_COMPLETION_DATE = decode( p_FIRST_UNIT_COMPLETION_DATE, FND_API.G_MISS_DATE, NULL, NULL, FIRST_UNIT_COMPLETION_DATE, p_FIRST_UNIT_COMPLETION_DATE)
184 ,FIRST_UNIT_START_DATE = decode( p_FIRST_UNIT_START_DATE, FND_API.G_MISS_DATE, NULL, NULL, FIRST_UNIT_START_DATE, p_FIRST_UNIT_START_DATE)
185 ,LAST_UNIT_COMPLETION_DATE = decode( p_LAST_UNIT_COMPLETION_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UNIT_COMPLETION_DATE, p_LAST_UNIT_COMPLETION_DATE)
186 ,LAST_UNIT_START_DATE = decode( p_LAST_UNIT_START_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UNIT_START_DATE, p_LAST_UNIT_START_DATE)
187 ,MINIMUM_TRANSFER_QUANTITY = decode( p_MINIMUM_TRANSFER_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, MINIMUM_TRANSFER_QUANTITY, p_MINIMUM_TRANSFER_QUANTITY)
188 ,STANDARD_OPERATION_ID = decode( p_STANDARD_OPERATION_ID, FND_API.G_MISS_NUM, NULL, NULL, STANDARD_OPERATION_ID, p_STANDARD_OPERATION_ID)
189 -- end swai: added code for operations
190 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, NULL, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
191 where WIP_TRANSACTION_DETAIL_ID = p_WIP_TRANSACTION_DETAIL_ID;
192
193 If (SQL%NOTFOUND) then
194 RAISE NO_DATA_FOUND;
195 End If;
196 END Update_Row;
197
198 PROCEDURE Delete_Row(
199 p_WIP_TRANSACTION_DETAIL_ID NUMBER)
200 IS
201 BEGIN
202 DELETE FROM CSD_WIP_TRANSACTION_DETAILS
203 WHERE WIP_TRANSACTION_DETAIL_ID = p_WIP_TRANSACTION_DETAIL_ID;
204 If (SQL%NOTFOUND) then
205 RAISE NO_DATA_FOUND;
206 End If;
207 END Delete_Row;
208
209
210
211 End CSD_WIP_TRANSACTION_DTLS_PKG;