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