1 PACKAGE BODY PO_PALT_MS_COMPLETION AS
2 /* $Header: POPTMCOB.pls 120.5 2012/01/18 05:09:09 mzhussai noship $ */
3 Function Get_Solicitation_Creation_Dt(pAssignment_Number IN VARCHAR2)
4 RETURN DATE AS
5 L_Solicit_Creation_Dt Date;
6
7 BEGIN
8
9 SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Solicit_Creation_Dt
10 FROM ( SELECT MAX(NVL(PAIP.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
11 FROM PO_REQUISITION_LINES_ALL PRL,
12 PON_BACKING_REQUISITIONS PBR,
13 PON_AUCTION_ITEM_PRICES_ALL PAIP
14 WHERE PRL.REQUISITION_LINE_ID = PBR.REQUISITION_LINE_ID (+)
15 AND NVL(PRL.CLM_INFO_FLAG,'N') <> 'Y'
16 AND PBR.AUCTION_HEADER_ID = PAIP.AUCTION_HEADER_ID (+)
17 AND PBR.LINE_NUMBER = PAIP.LINE_NUMBER (+)
18 AND PRL.ASSIGNMENT_NUMBER = pAssignment_Number
19 );
20
21 Return L_Solicit_Creation_Dt;
22
23
24 EXCEPTION
25
26 WHEN OTHERS THEN
27 RETURN NULL;
28 END Get_Solicitation_Creation_Dt;
29
30 Function Get_Solicitation_Publish_Dt(pAssignment_Number IN VARCHAR2)
31 RETURN DATE AS
32 L_Solicit_Publish_Dt Date;
33 BEGIN
34
35 SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Solicit_Publish_Dt
36 FROM ( SELECT MAX(NVL(PUBLISH_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
37 FROM PO_REQUISITION_LINES_ALL PRLA,
38 PON_BACKING_REQUISITIONS PBR ,
39 PON_AUCTION_HEADERS_ALL PAHA
40 WHERE PRLA.REQUISITION_LINE_ID = PBR.REQUISITION_LINE_ID(+)
41 AND NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
42 AND PBR.AUCTION_HEADER_ID = PAHA.AUCTION_HEADER_ID (+)
43 AND PAHA.AUCTION_STATUS (+) = 'ACTIVE'
44 AND PRLA.ASSIGNMENT_NUMBER = pAssignment_Number
45 );
46
47 Return L_Solicit_Publish_Dt;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 RETURN NULL;
52 END Get_Solicitation_Publish_Dt;
53
54
55 Function Get_Award_Creation_Dt(pAssignment_Number IN VARCHAR2)
56 RETURN DATE AS
57 L_Award_Creation_Dt Date;
58
59 BEGIN
60
61 SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Award_Creation_Dt
62 FROM (
63 SELECT MAX(NVL(PLLA.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
64 FROM PO_REQUISITION_LINES_ALL PRLA,
65 PO_REQ_DISTRIBUTIONS_ALL PRD,
66 PO_DISTRIBUTIONS_ALL PD,
67 PO_LINE_LOCATIONS_ALL PLLA
68 WHERE PRD.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
69 AND PRD.DISTRIBUTION_ID=PD.REQ_DISTRIBUTION_ID(+)
70 AND PD.LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID(+)
71 AND NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
72 AND PRLA.ASSIGNMENT_NUMBER = pAssignment_Number
73 UNION
74 SELECT MAX(NVL(PLL.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
75 FROM PO_REQUISITION_LINES_ALL PRLA,
76 PO_LINES_ALL PLL
77 WHERE PRLA.PO_LINE_ID = PLL.PO_LINE_ID(+)
78 AND NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
79 AND ((nvl(PRLA.CLM_OPTION_INDICATOR,'N')='O' and nvl(PRLA.CLM_OPTION_EXERCISED,'N')='N') OR nvl(PRLA.FUND_SOURCE_NOT_KNOWN,'N')='Y')
80 AND PRLA.ASSIGNMENT_NUMBER = pAssignment_Number
81 UNION
82 SELECT MAX(NVL(PLLDA.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
83 FROM PO_REQUISITION_LINES_ALL PRLA,
84 PO_REQ_DISTRIBUTIONS_ALL PRD,
85 PO_DISTRIBUTIONS_DRAFT_ALL PDD,
86 PO_LINE_LOCATIONS_DRAFT_ALL PLLDA
87 WHERE PRD.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
88 AND PRD.DISTRIBUTION_ID=PDD.REQ_DISTRIBUTION_ID
89 AND PDD.LINE_LOCATION_ID=PLLDA.LINE_LOCATION_ID
90 AND PDD.CHANGE_STATUS='NEW'
91 AND NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
92 AND PRLA.ASSIGNMENT_NUMBER = pAssignment_Number
93 );
94
95 Return L_Award_Creation_Dt;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 RETURN NULL;
100 END Get_Award_Creation_Dt;
101
102 Function Get_Award_Approval_Dt(pAssignment_Number IN VARCHAR2)
103 RETURN DATE AS
104 L_Award_Approval_Dt Date;
105
106 BEGIN
107
108 SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Award_Approval_Dt
109 FROM ( SELECT MAX(NVL(PLLA.APPROVED_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
110 FROM PO_REQUISITION_LINES_ALL PRLA,
111 PO_REQ_DISTRIBUTIONS_ALL PRD,
112 PO_DISTRIBUTIONS_ALL PD,
113 PO_LINE_LOCATIONS_ALL PLLA
114 WHERE PRD.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
115 AND PRD.DISTRIBUTION_ID=PD.REQ_DISTRIBUTION_ID
116 AND PD.LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID
117 AND NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
118 AND PLLA.APPROVED_FLAG(+) = 'Y'
119 AND PRLA.ASSIGNMENT_NUMBER = pAssignment_Number);
120
121 Return L_Award_Approval_Dt;
122
123 EXCEPTION
124
125 WHEN OTHERS THEN
126 RETURN NULL;
127 END Get_Award_Approval_Dt;
128
129
130 Procedure Get_MS_Completion_Date(errbuf OUT NOCOPY VARCHAR2,
131 retcode OUT NOCOPY VARCHAR2,
132 Use_Custom_Hook IN VARCHAR2) IS
133
134 l_Assignment_list PO_PALT_MS_COMPLETION.Assignment_Details;
135 l_Assignment_Number VARCHAR2(30);
136 l_Assignment_Id NUMBER;
137 l_Assign_MS_Dtls PO_PALT_MS_COMPLETION.Assign_MS_Details;
138 l_MS_Code VARCHAR2(30);
139 L_Solicit_Creation_Dt Date;
140 L_Solicit_Publish_Dt Date;
141 L_Award_Creation_Dt Date;
142 L_Award_Approval_Dt Date;
143 l_incomplete_MS_Count Number;
144 l_Use_Custom_Hook BOOLEAN;
145 l_incomp_MS_count NUMBER;
146
147 CURSOR Get_Uncompleted_Assignments IS
148 SELECT ASSIGNMENT_NUMBER,ASSIGNMENT_ID FROM PO_CLM_ASSIGNMENTS
149 WHERE ASSIGNMENT_STATUS IN (1,2,7);
150
151 CURSOR Get_Uncompleted_System_MS IS
152 SELECT MILESTONE_CODE FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
153 WHERE MILESTONE_TYPE = 'S'
154 AND ACTUAL_COMPLETION_DATE IS NULL
155 AND ASSIGNMENT_ID = l_Assignment_Id;
156
157 BEGIN
158 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Beginning the Calculation of Milestone Completion Date');
159
160 IF (Use_Custom_Hook = 'Y') THEN
161 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter to Use Custom Code is ON');
162 l_Use_Custom_Hook := TRUE;
163 ELSE
164 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter to Use Custom Code is OFF');
165 l_Use_Custom_Hook := FALSE;
166
167 END IF;
168
169 FOR l_Assignment_list IN Get_Uncompleted_Assignments LOOP
170 l_Assignment_Number := l_Assignment_list.ASSIGNMENT_NUMBER;
171 l_Assignment_Id := l_Assignment_list.ASSIGNMENT_ID;
172 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Of Completion Date Calculation for Assignment '||l_Assignment_Number);
173 FOR l_Assign_MS_Dtls IN Get_Uncompleted_System_MS LOOP
174 l_MS_Code := l_Assign_MS_Dtls.MILESTONE_CODE;
175
176 IF (l_MS_Code = 'SOCREATE') THEN
177
178 IF (l_Use_Custom_Hook) THEN
179 L_Solicit_Creation_Dt:= PO_PALT_MILESTONE_CMPL_HOOK.Get_Solicitation_Creation_Dt(l_Assignment_Number);
180 ELSE
181 L_Solicit_Creation_Dt := Get_Solicitation_Creation_Dt(l_Assignment_Number);
182 END IF;
183 IF (L_Solicit_Creation_Dt IS NOT NULL) THEN
184 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Solicitation Creation for Assignment'||l_Assignment_Number||' Completed ON '|| to_char(L_Solicit_Creation_Dt,'DD-MON-YYYY HH24:MI:SS'));
185 UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
186 SET ACTUAL_COMPLETION_DATE = L_Solicit_Creation_Dt,
187 last_updated_by = fnd_global.user_id ,
188 last_update_date = SYSDATE ,
189 last_update_login = fnd_global.login_id
190 WHERE ASSIGNMENT_ID = l_Assignment_Id
191 and MILESTONE_CODE = 'SOCREATE' ;
192 ELSE
193 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Solicitation Creation for Assignment'||l_Assignment_Number||' is not Completed');
194 END IF;
195 END IF;
196
197 IF (l_MS_Code = 'SOPUBLIS') THEN
198 IF (l_Use_Custom_Hook) THEN
199 L_Solicit_Publish_Dt :=PO_PALT_MILESTONE_CMPL_HOOK.Get_Solicitation_Publish_Dt(l_Assignment_Number);
200 ELSE
201 L_Solicit_Publish_Dt := Get_Solicitation_Publish_Dt(l_Assignment_Number);
202 END IF;
203 IF (L_Solicit_Publish_Dt IS NOT NULL) THEN
204 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Publish Solicitation for Assignment'||l_Assignment_Number||' Completed ON '|| to_char(L_Solicit_Publish_Dt,'DD-MON-YYYY HH24:MI:SS'));
205 UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
206 SET ACTUAL_COMPLETION_DATE = L_Solicit_Publish_Dt,
207 last_updated_by = fnd_global.user_id ,
208 last_update_date = SYSDATE ,
209 last_update_login = fnd_global.login_id
210 WHERE ASSIGNMENT_ID = l_Assignment_Id
211 and MILESTONE_CODE = 'SOPUBLIS' ;
212 ELSE
213 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Publish Solicitation for Assignment'||l_Assignment_Number||' is not Completed');
214 END IF;
215 END IF;
216
217 IF (l_MS_Code = 'AWCREATE') THEN
218 IF (l_Use_Custom_Hook) THEN
219 L_Award_Creation_Dt := PO_PALT_MILESTONE_CMPL_HOOK.Get_Award_Creation_Dt(l_Assignment_Number);
220 ELSE
221 L_Award_Creation_Dt := Get_Award_Creation_Dt(l_Assignment_Number);
222 END IF;
223 IF (L_Award_Creation_Dt IS NOT NULL) THEN
224 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Award Creation for Assignment'||l_Assignment_Number||' Completed ON '|| to_char(L_Award_Creation_Dt,'DD-MON-YYYY HH24:MI:SS'));
225 UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
226 SET ACTUAL_COMPLETION_DATE = L_Award_Creation_Dt,
227 last_updated_by = fnd_global.user_id ,
228 last_update_date = SYSDATE ,
229 last_update_login = fnd_global.login_id
230 WHERE ASSIGNMENT_ID = l_Assignment_Id
231 and MILESTONE_CODE = 'AWCREATE' ;
232
233 ELSE
234 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Award Creation for Assignment'||l_Assignment_Number||' is not Completed');
235 END IF;
236 END IF;
237
238 IF (l_MS_Code = 'AWAPPROV') THEN
239 IF (l_Use_Custom_Hook) THEN
240 L_Award_Approval_Dt := PO_PALT_MILESTONE_CMPL_HOOK.Get_Award_Approval_Dt(l_Assignment_Number);
241 ELSE
242 L_Award_Approval_Dt := Get_Award_Approval_Dt(l_Assignment_Number);
243 END IF;
244
245 IF (L_Award_Approval_Dt IS NOT NULL) THEN
246 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Award Approval for Assignment'||l_Assignment_Number||' Completed ON '|| to_char(L_Award_Approval_Dt,'DD-MON-YYYY HH24:MI:SS'));
247 UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
248 SET ACTUAL_COMPLETION_DATE = L_Award_Approval_Dt,
249 last_updated_by = fnd_global.user_id ,
250 last_update_date = SYSDATE ,
251 last_update_login = fnd_global.login_id
252 WHERE ASSIGNMENT_ID = l_Assignment_Id
253 and MILESTONE_CODE = 'AWAPPROV' ;
254 ELSE
255 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Milestone Award Approval for Assignment'||l_Assignment_Number||' is not Completed');
256 END IF;
257 END IF;
258
259 END LOOP;
260
261 /* Added code to update completion dates for all milestones other than the 4 seeded milestones.*/
262 SELECT Count(*)
263 INTO l_incomp_MS_count
264 FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
265 WHERE ((MILESTONE_TYPE = 'S' AND MILESTONE_CODE NOT IN ('SOCREATE','SOPUBLIS','AWCREATE','AWAPPROV'))
266 OR MILESTONE_TYPE = 'U')
267 AND ACTUAL_COMPLETION_DATE IS NULL
268 AND ASSIGNMENT_ID = l_Assignment_Id;
269
270 IF (l_incomp_MS_count > 0) THEN
271 IF (l_Use_Custom_Hook) THEN
272 PO_PALT_MILESTONE_CMPL_HOOK.Custom_Update_MS_Completion_Dt(l_Assignment_Number);
273 END IF;
274 END IF;
275
276
277 l_incomplete_MS_Count := 0;
278
279 SELECT Count(*)
280 INTO l_incomplete_MS_Count
281 FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
282 WHERE ASSIGNMENT_ID = l_Assignment_Id
283 and ACTUAL_COMPLETION_DATE is NULL ;
284
285 IF (l_incomplete_MS_Count = 0) THEN
286 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment '||l_Assignment_Number||' is Completed');
287 UPDATE PO_CLM_ASSIGNMENTS
288 SET ACTUAL_COMPLETION_DATE = (SELECT Max(ACTUAL_COMPLETION_DATE)
289 FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
290 WHERE ASSIGNMENT_ID = l_Assignment_Id) ,
291 ASSIGNMENT_STATUS = 4,
292 last_updated_by = fnd_global.user_id ,
293 last_update_date = SYSDATE ,
294 last_update_login = fnd_global.login_id
295 WHERE ASSIGNMENT_ID = l_Assignment_Id;
296
297 END IF;
298 END LOOP;
299 COMMIT;
300 EXCEPTION
301 WHEN OTHERS THEN
302 NULL;
303 END Get_MS_Completion_Date;
304
305 END PO_PALT_MS_COMPLETION;
[Home] [Help]