[Home] [Help]
PACKAGE BODY: APPS.CSD_GROUP_JOB_PARAM_TEMP_PKG
Source
1 PACKAGE BODY CSD_GROUP_JOB_PARAM_TEMP_PKG as
2 /* $Header: csdtjprb.pls 115.6 2002/12/02 23:42:30 takwong noship $ */
3 -- Start of Comments
4 -- Package name : CSD_GROUP_JOB_PARAM_TEMP_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_GROUP_JOB_PARAM_TEMP_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtjprb.pls';
13 l_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
14
15 PROCEDURE Insert_Row(
16 px_GROUP_JOB_ID IN OUT NOCOPY NUMBER
17 ,p_REPAIR_GROUP_ID NUMBER
18 ,p_INVENTORY_ITEM_ID NUMBER
19 ,p_ORGANIZATION_ID NUMBER
20 ,p_JOB_TYPE NUMBER
21 ,p_ROUTING_REFERENCE_ID NUMBER
22 ,p_ALTERNATE_DESIGNATOR_CODE VARCHAR2
23 ,p_JOB_STATUS_TYPE VARCHAR2
24 ,p_ACCOUNTING_CLASS VARCHAR2
25 ,p_START_DATE DATE
26 ,p_COMPLETION_DATE DATE
27 ,p_QUANTITY_RECEIVED NUMBER
28 ,p_QUANTITY_SUBMITTED NUMBER
29 ,p_ITEM_REVISION VARCHAR2
30 ,p_CREATED_BY NUMBER
31 ,p_CREATION_DATE DATE
32 ,p_LAST_UPDATED_BY NUMBER
33 ,p_LAST_UPDATE_LOGIN NUMBER
34 ,p_OBJECT_VERSION_NUMBER NUMBER
35 ,p_LAST_UPDATE_DATE DATE
36 ,p_INCIDENT_ID NUMBER
37 ,p_PROCESS_ID NUMBER)
38
39 IS
40 CURSOR C2 IS SELECT CSD_GROUP_JOB_PARAM_TEMP_S1.nextval FROM sys.dual;
41 BEGIN
42 If (px_GROUP_JOB_ID IS NULL) OR (px_GROUP_JOB_ID = FND_API.G_MISS_NUM) then
43 OPEN C2;
44 FETCH C2 INTO px_GROUP_JOB_ID;
45 CLOSE C2;
46 End If;
47 INSERT INTO CSD_GROUP_JOB_PARAM_TEMP(
48 GROUP_JOB_ID
49 ,REPAIR_GROUP_ID
50 ,INVENTORY_ITEM_ID
51 ,ORGANIZATION_ID
52 ,JOB_TYPE
53 ,ROUTING_REFERENCE_ID
54 ,ALTERNATE_DESIGNATOR_CODE
55 ,JOB_STATUS_TYPE
56 ,ACCOUNTING_CLASS
57 ,START_DATE
58 ,COMPLETION_DATE
59 ,QUANTITY_RECEIVED
60 ,QUANTITY_SUBMITTED
61 ,ITEM_REVISION
62 ,CREATED_BY
63 ,CREATION_DATE
64 ,LAST_UPDATED_BY
65 ,LAST_UPDATE_LOGIN
66 ,OBJECT_VERSION_NUMBER
67 ,LAST_UPDATE_DATE
68 ,INCIDENT_ID
69 ,PROCESS_ID
70 ) VALUES (
71 px_GROUP_JOB_ID
72 ,decode( p_REPAIR_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_GROUP_ID)
73 ,decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID)
74 ,decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID)
75 ,decode( p_JOB_TYPE, FND_API.G_MISS_NUM, NULL, p_JOB_TYPE)
76 ,decode( p_ROUTING_REFERENCE_ID, FND_API.G_MISS_NUM, NULL, p_ROUTING_REFERENCE_ID)
77 ,decode( p_ALTERNATE_DESIGNATOR_CODE, FND_API.G_MISS_CHAR, NULL, p_ALTERNATE_DESIGNATOR_CODE)
78 ,decode( p_JOB_STATUS_TYPE, FND_API.G_MISS_CHAR, NULL, p_JOB_STATUS_TYPE)
79 ,decode( p_ACCOUNTING_CLASS, FND_API.G_MISS_CHAR, NULL, p_ACCOUNTING_CLASS)
80 ,decode( p_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_START_DATE)
81 ,decode( p_COMPLETION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_COMPLETION_DATE)
82 ,decode( p_QUANTITY_RECEIVED, FND_API.G_MISS_NUM, NULL, p_QUANTITY_RECEIVED)
83 ,decode( p_QUANTITY_SUBMITTED, FND_API.G_MISS_NUM, NULL, p_QUANTITY_SUBMITTED)
84 ,decode( p_ITEM_REVISION, FND_API.G_MISS_CHAR, NULL, p_ITEM_REVISION)
85 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
86 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
87 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
88 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
89 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
90 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
91 ,decode( p_INCIDENT_ID, FND_API.G_MISS_NUM, NULL, p_INCIDENT_ID)
92 ,decode( p_PROCESS_ID, FND_API.G_MISS_NUM, NULL, p_PROCESS_ID));
93 End Insert_Row;
94
95 PROCEDURE Update_Row(
96 p_GROUP_JOB_ID NUMBER
97 ,p_REPAIR_GROUP_ID NUMBER
98 ,p_INVENTORY_ITEM_ID NUMBER
99 ,p_ORGANIZATION_ID NUMBER
100 ,p_JOB_TYPE NUMBER
101 ,p_ROUTING_REFERENCE_ID NUMBER
102 ,p_ALTERNATE_DESIGNATOR_CODE VARCHAR2
103 ,p_JOB_STATUS_TYPE VARCHAR2
104 ,p_ACCOUNTING_CLASS VARCHAR2
105 ,p_START_DATE DATE
106 ,p_COMPLETION_DATE DATE
107 ,p_QUANTITY_RECEIVED NUMBER
108 ,p_QUANTITY_SUBMITTED NUMBER
109 ,p_ITEM_REVISION VARCHAR2
110 ,p_CREATED_BY NUMBER
111 ,p_CREATION_DATE DATE
112 ,p_LAST_UPDATED_BY NUMBER
113 ,p_LAST_UPDATE_LOGIN NUMBER
114 ,p_OBJECT_VERSION_NUMBER NUMBER
115 ,p_LAST_UPDATE_DATE DATE
116 ,p_INCIDENT_ID NUMBER
117 ,p_PROCESS_ID NUMBER)
118
119 IS
120 BEGIN
121 Update CSD_GROUP_JOB_PARAM_TEMP
122 SET
123 REPAIR_GROUP_ID = decode( p_REPAIR_GROUP_ID, FND_API.G_MISS_NUM, REPAIR_GROUP_ID, p_REPAIR_GROUP_ID)
124 ,INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID)
125 ,ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID)
126 ,JOB_TYPE = decode( p_JOB_TYPE, FND_API.G_MISS_NUM, JOB_TYPE, p_JOB_TYPE)
127 ,ROUTING_REFERENCE_ID = decode( p_ROUTING_REFERENCE_ID, FND_API.G_MISS_NUM, ROUTING_REFERENCE_ID, p_ROUTING_REFERENCE_ID)
128 ,ALTERNATE_DESIGNATOR_CODE = decode( p_ALTERNATE_DESIGNATOR_CODE, FND_API.G_MISS_CHAR, ALTERNATE_DESIGNATOR_CODE, p_ALTERNATE_DESIGNATOR_CODE)
129 ,JOB_STATUS_TYPE = decode( p_JOB_STATUS_TYPE, FND_API.G_MISS_CHAR, JOB_STATUS_TYPE, p_JOB_STATUS_TYPE)
130 ,ACCOUNTING_CLASS = decode( p_ACCOUNTING_CLASS, FND_API.G_MISS_CHAR, ACCOUNTING_CLASS, p_ACCOUNTING_CLASS)
131 ,START_DATE = decode( p_START_DATE, FND_API.G_MISS_DATE, START_DATE, p_START_DATE)
132 ,COMPLETION_DATE = decode( p_COMPLETION_DATE, FND_API.G_MISS_DATE, COMPLETION_DATE, p_COMPLETION_DATE)
133 ,QUANTITY_RECEIVED = decode( p_QUANTITY_RECEIVED, FND_API.G_MISS_NUM, QUANTITY_RECEIVED, p_QUANTITY_RECEIVED)
134 ,QUANTITY_SUBMITTED = decode( p_QUANTITY_SUBMITTED, FND_API.G_MISS_NUM, QUANTITY_SUBMITTED, p_QUANTITY_SUBMITTED)
135 ,ITEM_REVISION = decode( p_ITEM_REVISION, FND_API.G_MISS_CHAR, ITEM_REVISION, p_ITEM_REVISION)
136 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
137 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
138 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
139 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
140 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
141 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
142 ,INCIDENT_ID = decode( p_INCIDENT_ID, FND_API.G_MISS_NUM, INCIDENT_ID, p_INCIDENT_ID)
143 ,PROCESS_ID = decode( p_PROCESS_ID, FND_API.G_MISS_NUM, PROCESS_ID, p_PROCESS_ID)
144 where GROUP_JOB_ID = p_GROUP_JOB_ID;
145
146 If (SQL%NOTFOUND) then
147 RAISE NO_DATA_FOUND;
148 End If;
149 END Update_Row;
150
151 PROCEDURE Delete_Row(
152 p_GROUP_JOB_ID NUMBER)
153 IS
154 BEGIN
155 DELETE FROM CSD_GROUP_JOB_PARAM_TEMP
156 WHERE GROUP_JOB_ID = p_GROUP_JOB_ID;
157 If (SQL%NOTFOUND) then
158 RAISE NO_DATA_FOUND;
159 End If;
160 END Delete_Row;
161
162 PROCEDURE Lock_Row(
163 p_GROUP_JOB_ID NUMBER
164 ,p_OBJECT_VERSION_NUMBER NUMBER)
165
166 IS
167 CURSOR C IS
168 SELECT *
169 FROM CSD_GROUP_JOB_PARAM_TEMP
170 WHERE GROUP_JOB_ID = p_GROUP_JOB_ID
171 FOR UPDATE of GROUP_JOB_ID NOWAIT;
172
173 Recinfo C%ROWTYPE;
174 BEGIN
175 OPEN C;
176 FETCH C INTO Recinfo;
177
178 If (C%NOTFOUND) then
179 CLOSE C;
180 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
181 APP_EXCEPTION.RAISE_EXCEPTION;
182 End If;
183 CLOSE C;
184
185 If ( Recinfo.object_version_number = p_object_version_number ) then
186 return;
187 else
188 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
189 APP_EXCEPTION.RAISE_EXCEPTION;
190 End If;
191
192 END Lock_Row;
193
194 End CSD_GROUP_JOB_PARAM_TEMP_PKG;