1 PACKAGE BODY PA_CLIENT_EXTN_CHECK_CMT AS
2 /* $Header: PACECMTB.pls 120.2.12020000.2 2012/07/19 09:27:50 admarath ship $*/
3
4
5 FUNCTION COMMITMENTS_CHANGED ( p_ProjectID IN NUMBER )
6 RETURN VARCHAR2
7 IS
8
9 v_tmp VARCHAR2(1) := 'N';
10
11 BEGIN
12
13 /** The below code lists all standard sqls to identify whether commitments
14 have changed or not since the last summarization run. Customers can
15 uncomment the below code to check whether commitments have changed or not.
16 One benefit of uncommenting the below code is that customers can add hints
17 (which are suitable for the data distribution that exists in their system)
18 to the below sqls which will make the Summarization process much faster.
19 The default code which is written below inside comments is simlar to
20 what has been coded inside the standard Oracle Projects function
21 PA_CHECK_COMMITMENTS.COMMITMENTS_CHANGED. If customer uncomments the below
22 code then the same code in PA_CHECK_COMMITMENTS.COMMITMENTS_CHANGED
23 will not get executed again ensuring that performance is not hit.
24
25 If customers want they can modify a part of or entire code below to account
26 for the way they want to consider whether commitments have changed or not.
27
28 This function should be able to determine whether the user defined
29 commitments have changed from the last summarization run or not.
30 If the commitments have changed, then the function should return
31 Y, else it should return S indicating STOP. If customers return 'N'
32 from this procedure then the code in standard Oracle Projects function
33 PA_CHECK_COMMITMENTS.COMMITMENTS_CHANGED will also get executed.
34 So for users who are going to uncomment the below code it is not
35 recommended to return 'N' from this function.
36
37 If Y is returned, then the summarization process would rebuild the
38 commitments, if S is returned then it will not rebuild the
39 commitments and if N is returned then it may or may not rebuild the commitments
40 depending on the output of the sqls coded inside standard Oracle Projects function
41 PA_CHECK_COMMITMENTS.COMMITMENTS_CHANGED. **/
42
43 v_tmp := 'N';
44
45 -- Code addition for bug 3258046 starts
46 /*
47
48 --Second Block: NEW REQUISITIONS
49 --Checks the PO Req Distributions' tables against PA_Commitment_Txns
50 --for new Purchase Requisitions
51
52 Begin -- Second Block
53
54 SELECT 'Y'
55 INTO v_tmp
56 FROM DUAL
57 WHERE EXISTS (
58 SELECT '1'
59 FROM
60 PO_REQ_DISTRIBUTIONS_ALL RD
61 , PO_REQUISITION_LINES_ALL RL
62 , PER_ALL_PEOPLE_F REQ
63 , PO_REQUISITION_HEADERS_ALL RH
64 , PO_DOCUMENT_TYPES PDT
65 , PO_LINE_TYPES LT
66 , PA_TASKS T
67 , HR_ALL_ORGANIZATION_UNITS O
68 , PA_EXPENDITURE_TYPES ET
69 , PA_PROJECTS P
70 WHERE
71 RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
72 AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
73 AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
74 AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
75 AND RL.LINE_LOCATION_ID IS NULL
76 AND NVL(RL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
77 AND NVL(RL.CANCEL_FLAG,'N') = 'N'
78 AND NVL(RL.MODIFIED_BY_AGENT_FLAG,'N') = 'N'
79 AND RL.SOURCE_TYPE_CODE = 'VENDOR'
80 AND NVL(RL.CLM_INFO_FLAG,'N') = 'N' --Bug 9462109: CLM enhancement
81 AND ( NVL(RL.CLM_OPTION_INDICATOR,'B') <> 'O' OR NVL(RL.CLM_OPTION_EXERCISED,'N') = 'Y' ) --Bug 9462109: CLM enhancement
82 AND REQ.PERSON_ID = RL.TO_PERSON_ID
83 AND TRUNC(SYSDATE)
84 BETWEEN NVL(REQ.EFFECTIVE_START_DATE,TRUNC(SYSDATE))
85 AND NVL(REQ.EFFECTIVE_END_DATE,TRUNC(SYSDATE))
86 AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
87 AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
88 AND RD.PROJECT_ID = P.PROJECT_ID
89 AND RD.TASK_ID = T.TASK_ID
90 AND RD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
91 AND RD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
92 AND RD.Project_ID = p_ProjectID
93 AND NOT EXISTS (
94 SELECT '2'
95 FROM PA_COMMITMENT_TXNS CMT
96 WHERE CMT.Line_Type = 'R'
97 AND CMT.Project_ID = p_ProjectID
98 AND CMT.Burden_Sum_Dest_Run_ID is NULL
99 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
100 AND CMT.CMT_Header_ID = RH.Requisition_Header_ID
101 AND CMT.CMT_Line_Number = RL.Line_Num
102 AND CMT.CMT_Distribution_ID = RD.Distribution_ID )
103 );
104
105 Return v_tmp;
106
107 Exception
108 WHEN NO_DATA_FOUND THEN
109 v_tmp := 'S';
110 End; -- Second Block, for new PO Reqs
111
112
113 -- Third Block: NEW AP INVOICES
114 -- Checks the AP Inv Distributions' tables against PA_Commitment_Txns
115 -- for new Invoices
116
117
118 v_tmp := 'S';
119
120 Begin -- Third Block
121
122 SELECT 'Y'
123 INTO v_tmp
124 FROM DUAL
125 WHERE EXISTS (
126 SELECT '1' -- may use push_subq hint here
127 FROM
128 AP_INVOICES_ALL I
129 , PO_DISTRIBUTIONS POD
130 , AP_INVOICE_DISTRIBUTIONS_ALL D
131 WHERE
132 I.Invoice_ID = D.Invoice_ID
133 AND D.PO_Distribution_ID = POD.PO_Distribution_ID(+)
134 AND POD.Distribution_type <> 'PREPAYMENT'
135 AND NVL(POD.Destination_Type_Code, 'EXPENSE') = 'EXPENSE'
136 AND decode(D.Pa_Addition_Flag,'Z','Y','T','Y','E','Y',null,'N',D.Pa_Addition_Flag) <> 'Y'
137 AND nvl(I.source, 'xxx') not in ('Oracle Project Accounting','PA_IC_INVOICES')
138 AND D.Project_ID = p_ProjectID
139 AND NOT EXISTS (
140 SELECT '2'
141 FROM PA_COMMITMENT_TXNS CMT
142 WHERE CMT.Line_Type = 'I'
143 AND CMT.Project_ID = p_ProjectID
144 AND CMT.Burden_Sum_Dest_Run_ID is NULL
145 AND CMT.Transaction_Source = 'ORACLE_PAYABLES'
146 AND CMT.CMT_Header_ID = D.Invoice_ID
147 -- AND CMT.CMT_Line_Number = D.Distribution_Line_Number ) -- R12 change
148 AND CMT.CMT_Distribution_ID = D.Invoice_Distribution_ID) -- R12 change
149 );
150
151 Return v_tmp;
152
153 Exception
154 WHEN NO_DATA_FOUND THEN
155 v_tmp := 'S';
156 End; -- Third Block, for new AP Invoices
157
158
159 -- Fourth Block: NEW POs
160 -- Checks the PO distributions' tables against PA_Commitment_Txns
161 -- for new POs
162
163
164 v_tmp := 'S';
165
166 Begin -- Fourth Block
167
168 SELECT 'Y'
169 INTO v_tmp
170 FROM DUAL
171 WHERE EXISTS
172 (
173 SELECT '1' -- may use ordered and push_subq hints here
174 FROM
175 PO_DISTRIBUTIONS_ALL POD
176 , PO_HEADERS_ALL POH
177 , PO_LINES_ALL POL
178 , PO_RELEASES_ALL POR
179 , PO_DOCUMENT_TYPES PDT
180 , PO_LINE_LOCATIONS_ALL PLL
181 , PER_ALL_PEOPLE_F BUY
182 , PER_ALL_PEOPLE_F REQ
183 WHERE
184 POH.AGENT_ID = BUY.PERSON_ID
185 AND TRUNC(SYSDATE)
186 BETWEEN BUY.EFFECTIVE_START_DATE
187 AND BUY.EFFECTIVE_END_DATE
188 AND POD.DELIVER_TO_PERSON_ID = REQ.PERSON_ID(+)
189 AND POD.Distribution_type <> 'PREPAYMENT'
190 AND NVL(POD.Destination_Type_Code, 'EXPENSE') = 'EXPENSE'
191 AND TRUNC(SYSDATE)
192 BETWEEN NVL(REQ.EFFECTIVE_START_DATE,TRUNC(SYSDATE))
193 AND NVL(REQ.EFFECTIVE_END_DATE,TRUNC(SYSDATE))
194 AND NVL(POH.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
195 AND NVL(PLL.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
196 AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
197 AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED')
198 AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
199 AND PDT.DOCUMENT_TYPE_CODE IN ('PO','PA')
200 AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
201 AND NVL(POH.CANCEL_FLAG,'N') = 'N'
202 AND DECODE(POR.RELEASE_NUM,NULL,'OPEN',NVL(POR.CLOSED_CODE,'OPEN'))
203 NOT IN ('CLOSED','FINALLY CLOSED')
204 AND DECODE(POR.RELEASE_NUM,NULL,'N',NVL(POR.CANCEL_FLAG,'N')) = 'N'
205 AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
206 AND POH.PO_Header_ID = POD.PO_Header_ID
207 AND POL.PO_Line_ID = POD.PO_Line_ID
208 AND NVL(POL.CLM_INFO_FLAG,'N') = 'N' --Bug 9462109: CLM enhancement
209 AND ( NVL(POL.CLM_OPTION_INDICATOR,'B') <> 'O' OR NVL(POL.CLM_EXERCISED_FLAG,'N') = 'Y' ) --Bug 9462109: CLM enhancement
210 AND POD.Project_ID = p_ProjectID
211 AND NOT EXISTS (
212 SELECT '2'
213 FROM PA_COMMITMENT_TXNS CMT
214 WHERE CMT.Line_Type = 'P'
215 AND CMT.Project_ID = p_ProjectID
216 AND CMT.Burden_Sum_Dest_Run_ID is NULL
217 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
218 AND CMT.CMT_Header_ID = POD.PO_Header_ID
219 AND CMT.CMT_Line_Number = POL.Line_Num
220 AND CMT.CMT_Distribution_ID = POD.PO_Distribution_ID )
221 );
222 Return v_tmp;
223
224 Exception
225 WHEN NO_DATA_FOUND THEN
226 v_tmp := 'S';
227
228 End; -- Fourth Block, for new POs
229
230
231 -- Fifth Block: UPDATED POs
232 -- Checks the PO distributions' tables against PA_Commitment_Txns
233 -- for updated POs
234 -- Note: For POs, all amounts are captured in Oracle Purchasing as denom amounts.
235
236
237 v_tmp := 'S';
238
239 Begin -- Fifth Block
240
241 SELECT 'Y'
242 INTO v_tmp
243 FROM DUAL
244 WHERE EXISTS
245 (
246 SELECT '1'
247 FROM PA_COMMITMENT_TXNS CMT
248 WHERE CMT.Project_ID = p_ProjectID
249 AND CMT.Burden_Sum_Dest_Run_ID is NULL
250 AND CMT.Line_Type||'' = 'P'
251 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
252 AND NOT EXISTS
253 (
254 SELECT '2'
255 FROM
256 PO_HEADERS_ALL POH
257 , PO_RELEASES_ALL POR
258 , PO_DOCUMENT_TYPES PDT
259 , PO_LINES_ALL POL
260 , PO_LINE_TYPES LT
261 , PO_LINE_LOCATIONS_ALL PLL
262 , PER_ALL_PEOPLE_F BUY
263 , PER_ALL_PEOPLE_F REQ
264 , HR_ALL_ORGANIZATION_UNITS O
265 , PA_EXPENDITURE_TYPES ET
266 , PA_TASKS T
267 , PO_DISTRIBUTIONS_ALL POD
268 , PA_PROJECTS P
269 WHERE
270 POH.AGENT_ID = BUY.PERSON_ID
271 AND TRUNC(SYSDATE)
272 BETWEEN BUY.EFFECTIVE_START_DATE
273 AND BUY.EFFECTIVE_END_DATE
274 AND POD.DELIVER_TO_PERSON_ID = REQ.PERSON_ID(+)
275 AND POD.Distribution_type <> 'PREPAYMENT'
276 AND NVL(POD.Destination_Type_Code, 'EXPENSE') = 'EXPENSE'
277 AND TRUNC(SYSDATE)
278 BETWEEN NVL(REQ.EFFECTIVE_START_DATE,TRUNC(SYSDATE))
279 AND NVL(REQ.EFFECTIVE_END_DATE,TRUNC(SYSDATE))
280 AND NVL(POH.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
281 AND NVL(PLL.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
282 AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
283 AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED')
284 AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
285 AND PDT.DOCUMENT_TYPE_CODE IN ('PO','PA')
286 AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
287 AND NVL(POH.CANCEL_FLAG,'N') = 'N'
288 AND DECODE(POR.RELEASE_NUM,NULL,'OPEN',NVL(POR.CLOSED_CODE,'OPEN'))
289 NOT IN ('CLOSED','FINALLY CLOSED')
290 AND DECODE(POR.RELEASE_NUM,NULL,'N',NVL(POR.CANCEL_FLAG,'N')) = 'N'
291 AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
292 AND POL.LINE_TYPE_ID = LT.LINE_TYPE_ID
293 AND POL.PO_LINE_ID = PLL.PO_LINE_ID
294 AND NVL(POL.CLM_INFO_FLAG,'N') = 'N' --Bug 9462109: CLM enhancement
295 AND ( NVL(POL.CLM_OPTION_INDICATOR,'B') <> 'O' OR NVL(POL.CLM_EXERCISED_FLAG,'N') = 'Y' ) --Bug 9462109: CLM enhancement
296 AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
297 AND POD.PROJECT_ID = P.PROJECT_ID
298 AND POD.TASK_ID = T.TASK_ID
299 AND POD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
300 AND POD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
301 AND POD.PROJECT_ID = p_ProjectID
302 AND POH.PO_Header_ID = CMT.CMT_Header_ID
303 AND POD.PO_Distribution_ID = CMT.CMT_Distribution_ID
304 and CMT.task_id = nvl(pod.task_id,0)
305 and NVL(CMT.description,'<prm>') = NVL(POL.item_description,'<prm>')
306 and NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(POD.expenditure_item_date,sysdate-15000)
307 and NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(decode(POR.release_num,NULL,POH.creation_date,POR.creation_date),sysdate-15000)
308 and NVL(CMT.cmt_approved_date,sysdate-15000) = NVL(decode(POR.release_num,NULL,POH.approved_date,POR.approved_date),sysdate-15000)
309 and NVL(CMT.cmt_requestor_name,'<prm>') = NVL(REQ.full_name,'<prm>')
310 and NVL(CMT.cmt_buyer_name,'<prm>') = NVL(BUY.full_name,'<prm>')
311 and NVL(CMT.cmt_approved_flag,'<prm>') = NVL(decode(POR.release_num,NULL,decode(POH.authorization_status,'APPROVED','Y','N'),decode(POR.authorization_status,'APPROVED','Y','N')),'<prm>')
312 and NVL(CMT.vendor_id,-1) = NVL(POH.vendor_id,-1)
313 and NVL(CMT.expenditure_type,'<prm>') = NVL(POD.expenditure_type,'<prm>')
314 and NVL(CMT.organization_id,0) = NVL(O.organization_id,0)
315 and CMT.expenditure_category = ET.expenditure_category
316 and CMT.revenue_category = ET.revenue_category_code
317 and NVL(CMT.unit_of_measure,'<prm>') = NVL(decode(PLL.VALUE_BASIS,'AMOUNT',NULL,POL.unit_meas_lookup_code),'<prm>')
321 and NVL(CMT.quantity_delivered,0) = NVL(POD.quantity_delivered,0)
318 and NVL(CMT.unit_price,0) = NVL(TO_NUMBER(decode(PLL.VALUE_BASIS,'AMOUNT',NULL,( PLL.price_override * NVL(POD.rate,1)))),0)
319 and CMT.original_quantity_ordered = POD.quantity_ordered
320 and NVL(CMT.quantity_cancelled,0) = NVL(POD.quantity_cancelled,0)
322 and CMT.quantity_invoiced = NVL(POD.quantity_billed,0)
323 and nvl(CMT.denom_raw_cost,0) = GREATEST(0,(POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0)
324 -NVL(POD.QUANTITY_BILLED,0))) * ((PLL.PRICE_OVERRIDE) +
325 (NVL(POD.NONRECOVERABLE_TAX,0) / POD.QUANTITY_ORDERED))
326 and NVL(CMT.denom_burdened_cost,0) =
327 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
328 NULL
329 , 'CMT'
330 , T.task_id
331 , POD.expenditure_item_date
332 , POD.expenditure_type
333 , O.organization_id
334 , 'C'
335 , GREATEST(0,(POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0)
336 -NVL(POD.QUANTITY_BILLED,0))) * ((PLL.PRICE_OVERRIDE) +
337 (NVL(POD.NONRECOVERABLE_TAX,0) / POD.QUANTITY_ORDERED)) ),0)
338 )
339 );
340
341 Return v_tmp;
342
343 Exception
344 WHEN NO_DATA_FOUND THEN
345 v_tmp := 'S';
346
347 End; -- Fifth Block, for updated POs
348
349
350 -- Sixth Block: UPDATED INVOICES
351 -- Checks the AP Inv Distributions' tables against PA_Commitment_Txns
352 -- for updated Invoices
353 -- Note: For AP Invoices, all amounts are captured in Oracle Payables as denom amounts.
354
355
356 v_tmp := 'S';
357
358 Begin -- Sixth Block
359
360 SELECT 'Y'
361 INTO v_tmp
362 FROM DUAL
363 WHERE EXISTS
364 (
365 SELECT '1'
366 FROM PA_COMMITMENT_TXNS CMT
367 WHERE CMT.Project_ID = p_ProjectID
368 AND CMT.Burden_Sum_Dest_Run_ID is NULL
369 AND CMT.Line_Type||'' = 'I'
370 AND CMT.Transaction_Source = 'ORACLE_PAYABLES'
371 AND NOT EXISTS
372 (
373 SELECT '2'
374 FROM
375 AP_INVOICE_DISTRIBUTIONS_ALL D
376 , AP_INVOICES_ALL I
377 , PO_VENDORS V
378 , HR_ALL_ORGANIZATION_UNITS O
379 , PA_EXPEND_TYP_SYS_LINKS ES
380 , PA_EXPENDITURE_TYPES ET
381 , PA_TASKS T
382 , PO_DISTRIBUTIONS PO
383 , PA_PROJECTS P
384 WHERE
385 I.vendor_id = V.vendor_id
386 AND I.invoice_id = D.invoice_id
387 AND decode(D.pa_addition_flag,'Z','Y','T','Y','E','Y',null,'N',D.pa_addition_flag) <> 'Y'
388 AND ( ES.system_linkage_function = 'VI' OR
389 ( ES.system_linkage_function = 'ER' AND
390 V.employee_id IS NOT NULL ))
391 AND D.po_distribution_id = PO.po_distribution_id (+)
392 AND PO.Distribution_type <> 'PREPAYMENT'
393 AND nvl(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
394 AND D.project_id = P.project_id
395 AND D.task_id = T.task_id
396 AND D.expenditure_organization_id = O.organization_id
397 AND D.expenditure_type = ES.expenditure_type
398 AND ET.expenditure_type = ES.expenditure_type
399 AND nvl(I.source, 'xxx') not in ('Oracle Project Accounting','PA_IC_INVOICES')
400 AND D.project_id = p_ProjectID
401 and CMT.task_id = nvl(d.task_id,0)
402 AND I.Invoice_ID = CMT.CMT_Header_ID
403 AND D.Distribution_Line_Number = CMT.CMT_Line_Number
404 and NVL(CMT.description,'<prm>') = NVL(D.description,'<prm>')
405 and NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(D.expenditure_item_date,sysdate-15000)
406 and NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(I.invoice_date,sysdate-15000)
407 and CMT.cmt_approved_flag = decode(AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.invoice_id,I.invoice_amount,I.payment_status_flag,I.invoice_type_lookup_code),'APPROVED','Y','N')
408 and CMT.vendor_id = I.vendor_id
409 and NVL(CMT.expenditure_type,'<prm>') = NVL(D.expenditure_type,'<prm>')
410 and NVL(CMT.organization_id,0) = NVL(O.organization_id,0)
411 and CMT.expenditure_category = ET.expenditure_category
412 and CMT.revenue_category = ET.revenue_category_code
413 and NVL(CMT.denom_raw_cost,0) = NVL(D.amount,0)
414 and NVL(CMT.tot_cmt_quantity,0) = NVL(D.pa_quantity,0)
415 and NVL(CMT.denom_burdened_cost,0) =
416 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
417 NULL
418 , 'CMT'
419 , T.task_id
420 , D.expenditure_item_date
421 , D.expenditure_type
422 , O.organization_id
423 , 'C'
424 , D.amount),0)
425 )
426 );
427
428 Return v_tmp;
429
430 Exception
431 WHEN NO_DATA_FOUND THEN
432 v_tmp := 'S';
433
434 End; -- Sixth Block, for updated AP Invoices
435
436
437 -- Seventh Block: UPADTED REQUISTIONS
438 -- Checks the PO Req Distributions' tables against PA_Commitment_Txns
439 -- for updated Purchase Requisitions
440
441 -- Note: For Requisitions, unit price is always captured in accounting currency! Therefore,
442 -- the raw cost and burdened cost comparative joins in this block use ACCT columns.
443
444 -- Please note that this is different than Updated POs and AP Invoices.
445
446
447 v_tmp := 'S';
448
449 Begin -- Seventh Block
450
451 SELECT 'Y'
452 INTO v_tmp
453 FROM DUAL
454 WHERE EXISTS
455 (
459 AND CMT.Burden_Sum_Dest_Run_ID is NULL
456 SELECT '1'
457 FROM PA_COMMITMENT_TXNS CMT
458 WHERE CMT.Project_ID = p_ProjectID
460 AND CMT.Line_Type||'' = 'R'
461 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
462 AND NOT EXISTS
463 (
464 SELECT '2'
465 FROM
466 PO_REQ_DISTRIBUTIONS_ALL RD
467 , PO_REQUISITION_LINES_ALL RL
468 , PO_REQUISITION_HEADERS_ALL RH
469 , PO_DOCUMENT_TYPES PDT
470 , PO_LINE_TYPES LT
471 , PER_ALL_PEOPLE_F REQ
472 , PA_TASKS T
473 , HR_ALL_ORGANIZATION_UNITS O
474 , PA_EXPENDITURE_TYPES ET
475 , PA_PROJECTS P
476 WHERE
477 RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
478 AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
479 AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
480 AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
481 AND RL.LINE_LOCATION_ID IS NULL
482 AND NVL(RL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
483 AND NVL(RL.CANCEL_FLAG,'N') = 'N'
484 AND NVL(RL.MODIFIED_BY_AGENT_FLAG,'N') = 'N'
485 AND RL.SOURCE_TYPE_CODE = 'VENDOR'
486 AND REQ.PERSON_ID = RL.TO_PERSON_ID
487 AND TRUNC(SYSDATE)
488 BETWEEN NVL(REQ.EFFECTIVE_START_DATE,TRUNC(SYSDATE))
489 AND NVL(REQ.EFFECTIVE_END_DATE,TRUNC(SYSDATE))
490 AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
491 AND NVL(RL.CLM_INFO_FLAG,'N') = 'N' --Bug 9462109: CLM enhancement
492 AND ( NVL(RL.CLM_OPTION_INDICATOR,'B') <> 'O' OR NVL(RL.CLM_OPTION_EXERCISED,'N') = 'Y' ) --Bug 9462109: CLM enhancement
493 AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
494 AND RD.PROJECT_ID = P.PROJECT_ID
495 AND RD.TASK_ID = T.TASK_ID
496 AND RD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
497 AND RD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
498 AND RD.PROJECT_ID = p_ProjectID
499 and CMT.task_id = nvl(rd.task_id,0)
500 and CMT.cmt_distribution_id = RD.Distribution_ID
501 and CMT.description = RL.item_description
502 and NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(RD.expenditure_item_date,sysdate-15000)
503 and NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(RL.creation_date,sysdate-15000)
504 and CMT.cmt_approved_flag = decode(NVL(RH.authorization_status,'NOT APPROVED'),'APPROVED','Y','N')
505 and NVL(CMT.cmt_need_by_date,sysdate-15000) = NVL(RL.need_by_date,sysdate-15000)
506 and NVL(CMT.vendor_id,0) = NVL(RL.vendor_id,0)
507 and NVL(CMT.expenditure_type,'<prm>') = NVL(RD.expenditure_type,'<prm>')
508 and NVL(CMT.organization_id,0) = NVL(O.organization_id,0)
509 and CMT.expenditure_category = ET.expenditure_category
510 and CMT.revenue_category = ET.revenue_category_code
511 and CMT.acct_raw_cost = (RD.REQ_LINE_QUANTITY * RL.UNIT_PRICE) + NVL(RD.NONRECOVERABLE_TAX,0)
512 and CMT.tot_cmt_quantity = RD.req_line_quantity
513 and NVL(CMT.acct_burdened_cost,0) =
514 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
515 NULL
516 , 'CMT'
517 , T.task_id
518 , RD.expenditure_item_date
519 , RD.expenditure_type
520 , O.organization_id
521 , 'C'
522 , (RD.REQ_LINE_QUANTITY * RL.UNIT_PRICE) + NVL(RD.NONRECOVERABLE_TAX,0)
523 ),0)
524 )
525 );
526
527 Return v_tmp;
528
529 Exception
530 WHEN NO_DATA_FOUND THEN
531 v_tmp := 'S';
532 End; -- Seventh Block, for updated PO Reqs
533
534
535 -- Eighth Block: UPDATED MFG COMMITMENTS
536 -- Checks the CST_PROJMFG_CMT_VIEW against PA_Commitment_Txns
537 -- for updated commitments
538
539 -- Note: For MFG commitments, the view returns both acct and denom amounts for most amount
540 -- columns, So, where possible, joins are performed for denom amounts.
541
542
543 v_tmp := 'S';
544
545 Begin -- Eighth Block
546
547 SELECT 'Y'
548 INTO v_tmp
549 FROM DUAL
550 WHERE EXISTS
551 (
552 SELECT '1'
553 FROM PA_COMMITMENT_TXNS CMT
554 WHERE CMT.Project_ID = p_ProjectID
555 AND CMT.Burden_Sum_Dest_Run_ID is NULL
556 AND CMT.Transaction_Source = 'ORACLE_MANUFACTURING'
557 AND NOT EXISTS
558 (
559 SELECT '2'
560 FROM CST_PROJMFG_CMT_VIEW CST
561 WHERE
562 CST.Project_ID = p_ProjectID
563 AND CMT.task_id = nvl(CST.task_id,0)
564 AND nvl(CMT.CMT_Header_ID,0) = nvl(CST.CMT_Header_ID,0)
565 AND nvl(CMT.CMT_Line_Number,0) = nvl(CST.CMT_Line_Number,0)
566 AND nvl(CMT.CMT_Distribution_ID,0) = nvl(CST.CMT_Distribution_ID,0)
567 and nvl(cmt.DESCRIPTION,'<prm>') = nvl(cst.DESCRIPTION,'<prm>')
568 and nvl(cmt.EXPENDITURE_ITEM_DATE,sysdate-15000) = nvl(cst.EXPENDITURE_ITEM_DATE,sysdate-15000)
569 and nvl(cmt.CMT_LINE_NUMBER,0) = nvl(cst.CMT_LINE_NUMBER,0)
570 and nvl(cmt.CMT_CREATION_DATE,sysdate-15000) = nvl(cst.CMT_CREATION_DATE,sysdate-15000)
571 and nvl(cmt.CMT_APPROVED_DATE,sysdate-15000) = nvl(cst.CMT_APPROVED_DATE,sysdate-15000)
572 and nvl(cmt.CMT_REQUESTOR_NAME,'<prm>') = nvl(cst.CMT_REQUESTOR_NAME,'<prm>')
573 and nvl(cmt.CMT_BUYER_NAME,'<prm>') = nvl(cst.CMT_BUYER_NAME,'<prm>')
574 and nvl(cmt.CMT_APPROVED_FLAG,'<prm>') = nvl(cst.CMT_APPROVED_FLAG,'<prm>')
575 and nvl(cmt.CMT_PROMISED_DATE,sysdate-15000) = nvl(cst.CMT_PROMISED_DATE,sysdate-15000)
576 and nvl(cmt.CMT_NEED_BY_DATE,sysdate-15000) = nvl(cst.CMT_NEED_BY_DATE,sysdate-15000)
580 and nvl(cmt.EXPENDITURE_CATEGORY,'<prm>') = nvl(cst.EXPENDITURE_CATEGORY,'<prm>')
577 and nvl(cmt.ORGANIZATION_ID,0) = nvl(cst.ORGANIZATION_ID,0)
578 and nvl(cmt.VENDOR_ID,0) = nvl(cst.VENDOR_ID,0)
579 and nvl(cmt.EXPENDITURE_TYPE,'<prm>') = nvl(cst.EXPENDITURE_TYPE,'<prm>')
581 and nvl(cmt.REVENUE_CATEGORY,'<prm>') = nvl(cst.REVENUE_CATEGORY,'<prm>')
582 and nvl(cmt.UNIT_OF_MEASURE,'<prm>') = nvl(cst.UNIT_OF_MEASURE,'<prm>')
583 and nvl(cmt.UNIT_PRICE,0) = nvl(cst.UNIT_PRICE,0)
584 and nvl(cmt.denom_RAW_COST,0) = nvl(cst.denom_RAW_COST,0)
585 and nvl(cmt.denom_BURDENED_COST,0) = nvl(cst.denom_BURDENED_COST,0)
586 and nvl(cmt.TOT_CMT_QUANTITY,0) = nvl(cst.TOT_CMT_QUANTITY,0)
587 and nvl(cmt.QUANTITY_ORDERED,0) = nvl(cst.QUANTITY_ORDERED,0)
588 and nvl(cmt.AMOUNT_ORDERED,0) = nvl(cst.AMOUNT_ORDERED,0)
589 and nvl(cmt.ORIGINAL_QUANTITY_ORDERED,0) = nvl(cst.ORIGINAL_QUANTITY_ORDERED,0)
590 and nvl(cmt.ORIGINAL_AMOUNT_ORDERED,0) = nvl(cst.ORIGINAL_AMOUNT_ORDERED,0)
591 and nvl(cmt.QUANTITY_CANCELLED,0) = nvl(cst.QUANTITY_CANCELLED,0)
592 and nvl(cmt.AMOUNT_CANCELLED,0) = nvl(cst.AMOUNT_CANCELLED,0)
593 and nvl(cmt.QUANTITY_DELIVERED,0) = nvl(cst.QUANTITY_DELIVERED,0)
594 and nvl(cmt.AMOUNT_DELIVERED,0) = nvl(cst.AMOUNT_DELIVERED,0)
595 and nvl(cmt.QUANTITY_INVOICED,0) = nvl(cst.QUANTITY_INVOICED,0)
596 and nvl(cmt.AMOUNT_INVOICED,0) = nvl(cst.AMOUNT_INVOICED,0)
597 and nvl(cmt.QUANTITY_OUTSTANDING_DELIVERY,0) = nvl(cst.QUANTITY_OUTSTANDING_DELIVERY,0)
598 and nvl(cmt.AMOUNT_OUTSTANDING_DELIVERY,0) = nvl(cst.AMOUNT_OUTSTANDING_DELIVERY,0)
599 and nvl(cmt.QUANTITY_OUTSTANDING_INVOICE,0) = nvl(cst.QUANTITY_OUTSTANDING_INVOICE,0)
600 and nvl(cmt.AMOUNT_OUTSTANDING_INVOICE,0) = nvl(cst.AMOUNT_OUTSTANDING_INVOICE,0)
601 and nvl(cmt.QUANTITY_OVERBILLED,0) = nvl(cst.QUANTITY_OVERBILLED,0)
602 and nvl(cmt.AMOUNT_OVERBILLED,0) = nvl(cst.AMOUNT_OVERBILLED,0)
603 and nvl(cmt.ORIGINAL_TXN_REFERENCE1,'<prm>') = nvl(cst.ORIGINAL_TXN_REFERENCE1,'<prm>')
604 and nvl(cmt.ORIGINAL_TXN_REFERENCE2,'<prm>') = nvl(cst.ORIGINAL_TXN_REFERENCE2,'<prm>')
605 and nvl(cmt.ORIGINAL_TXN_REFERENCE3,'<prm>') = nvl(cst.ORIGINAL_TXN_REFERENCE3,'<prm>')
606 )
607 );
608
609 Return v_tmp;
610
611 Exception
612 WHEN NO_DATA_FOUND THEN
613 v_tmp := 'S';
614 End; -- Eighth Block, for updated commitments from CST_PROJMFG_CMT_VIEW
615
616
617 -- Ninth Block: NEW MFG COMMITMENTS
618 -- Checks the CST_PROJMFG_CMT_VIEW against PA_Commitment_Txns
619 -- for new commitments
620
621
622 v_tmp := 'S';
623
624 Begin -- Ninth Block
625
626 SELECT 'Y'
627 INTO v_tmp
628 FROM DUAL
629 WHERE EXISTS (
630 SELECT '1'
631 FROM
632 CST_PROJMFG_CMT_VIEW CST
633 WHERE
634 CST.PROJECT_ID = p_ProjectID
635 AND NOT EXISTS (
636 SELECT '2'
637 FROM PA_COMMITMENT_TXNS CMT
638 WHERE CMT.Transaction_Source = 'ORACLE_MANUFACTURING'
639 AND CMT.Project_ID = p_ProjectID
640 AND CMT.Burden_Sum_Dest_Run_ID is NULL
641 AND CMT.CMT_Header_ID = CST.CMT_Header_ID
642 AND CMT.CMT_Line_Number = CST.CMT_Line_Number
643 AND CMT.CMT_Distribution_ID = CST.CMT_Distribution_ID )
644 );
645
646 Return v_tmp;
647
648 Exception
649 WHEN NO_DATA_FOUND THEN
650 v_tmp := 'S';
651 End; -- Ninth Block, for new commitments from CST_PROJMFG_CMT_VIEW
652
653 */
654 -- Code addition for bug 3258046 ends
655 Return v_tmp;
656
657 END Commitments_Changed;
658
659 END PA_CLIENT_EXTN_CHECK_CMT;
[Home] [Help]