[Home] [Help]
PACKAGE BODY: APPS.PA_CHECK_COMMITMENTS
Source
1 PACKAGE BODY PA_CHECK_COMMITMENTS AS
2 /* $Header: PAXCMTVB.pls 120.14 2011/04/28 10:33:20 dbudhwar ship $*/
3
4
5 FUNCTION COMMITMENTS_CHANGED ( p_ProjectID IN NUMBER )
6 RETURN VARCHAR2
7 IS
8
9 v_tmp Varchar2(1) := 'N';
10 /* Added for bug 4360855 */
11 tmp NUMBER;
12 CURSOR c IS
13 SELECT organization_id
14 FROM pjm_org_parameters
15 WHERE common_project_id = p_ProjectID ;
16
17 Cursor is_cash_basis_enabled_cur IS /* Bug#4905546 */
18 SELECT 'Y' /* to find whether cash basis accounting is enabled or not */
19 FROM DUAL
20 WHERE EXISTS (
21 SELECT NVL(GLSLA.SLA_LEDGER_CASH_BASIS_FLAG,'N')
22 FROM GL_LEDGERS GLSLA,
23 PA_IMPLEMENTATIONS_ALL IMP,
24 AP_INVOICE_DISTRIBUTIONS_ALL APD
25 WHERE NVL(GLSLA.SLA_LEDGER_CASH_BASIS_FLAG,'N') = 'Y'
26 AND GLSLA.LEDGER_ID = IMP.SET_OF_BOOKS_ID
27 AND APD.PROJECT_ID = p_ProjectID
28 AND DECODE(APD.PA_ADDITION_FLAG,'Z','Y','T','Y','E','Y', null,'N', APD.PA_ADDITION_FLAG) <> 'Y'
29 AND APD.ORG_ID = IMP.ORG_ID );
30
31 is_cash_basis_enabled_flag varchar(1) := 'N' ;
32
33 BEGIN
34
35 OPEN is_cash_basis_enabled_cur;
36 FETCH is_cash_basis_enabled_cur into is_cash_basis_enabled_flag;
37 CLOSE is_cash_basis_enabled_cur;
38
39 /* First Block: NEW COMMITMENTS VIA CLIENT EXTENSION
40 Checks Client Extension for commitments against PA_Commitment_Txns
41 */
42
43
44
45 Begin -- First Block
46
47 v_tmp := PA_Client_Extn_Check_CMT.Commitments_Changed(p_ProjectID);
48 If v_tmp = 'Y' then
49 Return v_tmp;
50 end if;
51
52 End; -- First Block
53
54 -- Grants Management Integrated Commitment Processing ---------------------
55 -- added 30-MAY-2003, jwhite
56
57 -- If GMS enabled, Check manual encumbrance changes here for grants.
58 --
59 -- Since a GMS view is used for inserting GMS commitments if GMS is enabled,
60 -- then there is not any point doing any processing beyond this portion
61 -- of the code. Therefore, this code must ALWAYS return control to the calling object.
62
63
64 Begin
65
66 -- R12 AP Lines uptake: Commitment changed for grants is merged with the commitment
67 -- changed for projects. Additionally check for encumbrance as follows.
68 --IF (PA_PROJ_ACCUM_MAIN.G_GMS_Enabled = 'Y')
69 -- THEN
70 -- v_tmp := GMS_PA_API3.Commitments_Changed(p_ProjectID);
71 -- RETURN v_tmp;
72 --END IF ;
73 --
74 IF (PA_PROJ_ACCUM_MAIN.G_GMS_Enabled = 'Y')
75 THEN
76 BEGIN
77 SELECT 'Y' INTO v_tmp
78 FROM DUAL
79 WHERE EXISTS (
80 SELECT enc1.encumbrance_item_id
81 FROM gms_encumbrance_items_all Enc1,
82 pa_tasks T
83 WHERE enc1.task_id = T.task_id
84 AND T.project_id = p_ProjectID
85 AND enc1.enc_distributed_flag = 'Y'
86 MINUS
87 SELECT CMT.CMT_Header_ID
88 FROM PA_COMMITMENT_TXNS CMT
89 WHERE CMT.Line_Type = 'N'
90 AND CMT.Project_ID = p_ProjectID
91 AND CMT.Transaction_Source = 'OUTSIDE_SYSTEM');
92 -- Bug 3504811 : End
93 Return v_tmp;
94 Exception
95 WHEN NO_DATA_FOUND THEN
96 v_tmp := 'N' ;
97 End; -- End First block , Manual Enc.
98 END IF ;
99
100 End;
101
102 -- -------------------------------------------------------------------------
103
104 /* Code addition for bug 3258046 starts */
105 If v_tmp = 'S' then
106 Return 'N';
107 end if;
108 /* Code addition for bug 3258046 ends */
109
110 /* Second Block: NEW REQUISITIONS
111 Checks the PO Req Distributions' tables against PA_Commitment_Txns
112 for new Purchase Requisitions
113 */
114
115 /* Bug 1517186 made chagnes for performance tuning.
116 1. Altered the table sequence. 2. replaced per_people_f with per_all_people_f
117 3. Replace hr_organization_units with hr_all_organization_units
118 */
119
120 v_tmp := 'N';
121
122 Begin -- Second Block
123
124 SELECT 'Y'
125 INTO v_tmp
126 FROM DUAL
127 WHERE EXISTS (
128 SELECT '1'
129 FROM
130 PO_REQ_DISTRIBUTIONS_ALL RD
131 , PO_REQUISITION_LINES_ALL RL
132 , PER_ALL_PEOPLE_F REQ
133 , PO_REQUISITION_HEADERS_ALL RH
134 , PO_DOCUMENT_TYPES_ALL_TL PDT /* modified for bug 4758887 */
135 , PO_LINE_TYPES_B LT /* modified for bug 6367516 */
136 , PA_TASKS T
137 , HR_ALL_ORGANIZATION_UNITS O
138 , PA_EXPENDITURE_TYPES ET
139 , PA_PROJECTS P
140 WHERE
141 RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
142 AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
143 AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
144 AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
145 AND NVL( PDT.ORG_ID , -99 ) = NVL( RH.ORG_ID , -99 ) /* added for bug 4758887 */
146 AND PDT.LANGUAGE = USERENV('LANG') /* added for bug 4758887 */
147 AND RL.LINE_LOCATION_ID IS NULL
148 AND NVL(RL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
149 AND NVL(RL.CANCEL_FLAG,'N') = 'N'
150 AND NVL(RL.MODIFIED_BY_AGENT_FLAG,'N') = 'N'
151 AND RL.SOURCE_TYPE_CODE = 'VENDOR'
152 AND REQ.PERSON_ID = RL.TO_PERSON_ID
153 AND TRUNC(SYSDATE)
154 BETWEEN REQ.EFFECTIVE_START_DATE /* modified for bug 6367516 */
155 AND REQ.EFFECTIVE_END_DATE
156 AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
157 AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
158 AND RD.PROJECT_ID = P.PROJECT_ID
159 AND RD.TASK_ID = T.TASK_ID
160 AND RD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
161 AND RD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
162 AND RD.Project_ID = p_ProjectID
163 AND NOT EXISTS (
164 SELECT '2'
165 FROM PA_COMMITMENT_TXNS CMT
166 WHERE CMT.Line_Type = 'R'
167 AND CMT.Project_ID = p_ProjectID
168 AND CMT.Burden_Sum_Dest_Run_ID is NULL
169 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
170 AND CMT.CMT_Header_ID = RH.Requisition_Header_ID
171 AND CMT.CMT_Line_Number = RL.Line_Num
172 AND CMT.CMT_Distribution_ID = RD.Distribution_ID )
173 );
174
175 Return v_tmp;
176
177 Exception
178 WHEN NO_DATA_FOUND THEN
179 v_tmp := 'N';
180 End; -- Second Block, for new PO Reqs
181
182
183 /* Third Block: NEW AP INVOICES
184 Checks the AP Inv Distributions' tables against PA_Commitment_Txns
185 for new Invoices
186 */
187
188 v_tmp := 'N';
189
190 Begin -- Third Block
191
192 IF is_cash_basis_enabled_flag = 'Y' Then /* Bug#4905546 */
193
194 SELECT 'Y' /* When cash basis accounting is enabled */
195 INTO v_tmp
196 FROM DUAL
197 WHERE EXISTS (
198 SELECT '1'
199 FROM
200 pa_proj_ap_inv_distributions apd
201 WHERE
202 apd.PROJECT_ID = p_ProjectID
203 AND NOT EXISTS (
204 SELECT '2'
205 FROM PA_COMMITMENT_TXNS CMT
206 WHERE CMT.Line_Type = 'I'
207 AND CMT.Project_ID = p_ProjectID
208 AND CMT.Burden_Sum_Dest_Run_ID is NULL
209 AND CMT.Transaction_Source = 'ORACLE_PAYABLES'
210 AND CMT.CMT_Header_ID = apD.Invoice_ID
211 AND CMT.cmt_distribution_id = apd.invoice_distribution_id
212 AND cmt.acct_raw_cost = apd.amount)
213 );
214 ELSE
215
216 SELECT 'Y' /* When cash basis accounting is not enabled */
217 INTO v_tmp
218 FROM DUAL
219 WHERE EXISTS (
220 SELECT '1'
221 FROM
222 AP_INVOICES_ALL I /* Changed for the bug #1530740 */
223 , PO_DISTRIBUTIONS POD
224 , AP_INVOICE_DISTRIBUTIONS_ALL D /* Changed for the bug #1530740 */
225 WHERE
226 I.Invoice_ID = D.Invoice_ID
227 AND NVL(POD.Distribution_type, 'XX') <> 'PREPAYMENT'
228 AND D.PO_Distribution_ID = POD.PO_Distribution_ID(+)
229 AND NVL(POD.Destination_Type_Code, 'EXPENSE') = 'EXPENSE'
230 AND decode(D.Pa_Addition_Flag,'Z','Y','T','Y','E','Y', null,'N',D.Pa_Addition_Flag) <> 'Y'
231 /*Bug# 2061817:Added PA_IC_INVOICES in the condition of i.source below*/
232 AND nvl(I.source, 'xxx') not in ('Oracle Project Accounting','PA_IC_INVOICES')
233 AND D.Project_ID = p_ProjectID
234 AND NOT EXISTS (
235 SELECT '2'
236 FROM PA_COMMITMENT_TXNS CMT
237 WHERE CMT.Line_Type = 'I'
238 AND CMT.Project_ID = p_ProjectID
239 AND CMT.Burden_Sum_Dest_Run_ID is NULL
240 AND CMT.Transaction_Source = 'ORACLE_PAYABLES'
241 AND CMT.CMT_Header_ID = D.Invoice_ID
242 AND CMT.CMT_DISTRIBUTION_ID = D.invoice_distribution_id)
243 );
244 END IF;
245
246 Return v_tmp;
247
248 Exception
249 WHEN NO_DATA_FOUND THEN
250 v_tmp := 'N';
251 End; -- Third Block, for new AP Invoices
252
253 /* Fourth Block: NEW POs
254 Checks the PO distributions' tables against PA_Commitment_Txns
255 for new POs
256 */
257
258 v_tmp := 'N';
259
260 Begin -- Fourth Block
261
262 SELECT 'Y'
263 INTO v_tmp
264 FROM DUAL
265 WHERE EXISTS
266 (
267 SELECT /*+ leading(pod) */ '1' --bug 6872563 - skkoppul : Added hint
268 FROM
269 PO_DISTRIBUTIONS_ALL POD
270 , PO_HEADERS_ALL POH
271 , PO_LINES_ALL POL
272 , PO_RELEASES_ALL POR
273 , PO_DOCUMENT_TYPES_TL PDT --/* added for bug 6367516 */
274 , PO_LINE_LOCATIONS_ALL PLL
275 /* , PER_ALL_PEOPLE_F BUY
276 , PER_ALL_PEOPLE_F REQ *//* commented for bug 11800494 */
277 WHERE
278 /* POH.AGENT_ID = BUY.PERSON_ID
279 AND */ POD.Distribution_type <> 'PREPAYMENT' /* commented for bug 11800494 */
280 /* AND TRUNC(SYSDATE)
281 BETWEEN BUY.EFFECTIVE_START_DATE
282 AND BUY.EFFECTIVE_END_DATE
283 AND POD.DELIVER_TO_PERSON_ID = REQ.PERSON_ID(+) */ /* commented for bug 11800494 */
284 AND PDT.LANGUAGE = USERENV('LANG') /* added for bug 6367516 */
285 AND NVL( PDT.ORG_ID , -99 ) = NVL( POH.ORG_ID , -99 ) /* added for bug 6367516 */
286 /* AND TRUNC(SYSDATE)
287 BETWEEN REQ.EFFECTIVE_START_DATE
288 AND REQ.EFFECTIVE_END_DATE */ /* commented for bug 11800494 */
289 AND NVL(POH.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
290 AND NVL(PLL.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
291 AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
292 AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED')
293 AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
294 AND PDT.DOCUMENT_TYPE_CODE IN ('PO','PA')
295 AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
296 AND NVL(POH.CANCEL_FLAG,'N') = 'N'
297 AND DECODE(POR.RELEASE_NUM,NULL,'OPEN',NVL(POR.CLOSED_CODE,'OPEN'))
298 NOT IN ('CLOSED','FINALLY CLOSED')
299 AND DECODE(POR.RELEASE_NUM,NULL,'N',NVL(POR.CANCEL_FLAG,'N')) = 'N'
300 AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
301 AND POH.PO_Header_ID = POD.PO_Header_ID
302 AND POL.PO_Line_ID = POD.PO_Line_ID
303 AND POD.Project_ID = p_ProjectID
304 AND NOT EXISTS (
305 SELECT '2'
306 FROM PA_COMMITMENT_TXNS CMT
307 WHERE CMT.Line_Type = 'P'
308 AND CMT.Project_ID = p_ProjectID
309 AND CMT.Burden_Sum_Dest_Run_ID is NULL
310 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
311 AND CMT.CMT_Header_ID = POD.PO_Header_ID
312 AND CMT.CMT_Line_Number = POL.Line_Num
313 AND CMT.CMT_Distribution_ID = POD.PO_Distribution_ID )
314 );
315 Return v_tmp;
316
317 Exception
318 WHEN NO_DATA_FOUND THEN
319 v_tmp := 'N';
320
321 End; -- Fourth Block, for new POs
322
323
324 /* Fifth Block: UPDATED POs
325 Checks the PO distributions' tables against PA_Commitment_Txns
326 for updated POs
327 Note: For POs, all amounts are captured in Oracle Purchasing as denom amounts.
328 */
329
330 v_tmp := 'N';
331
332 Begin -- Fifth Block
333
334 SELECT 'Y'
335 INTO v_tmp
336 FROM DUAL
337 WHERE EXISTS
338 (
339 SELECT '1'
340 FROM PA_COMMITMENT_TXNS CMT
341 WHERE CMT.Project_ID = p_ProjectID
342 AND CMT.Burden_Sum_Dest_Run_ID is NULL
343 AND CMT.Line_Type||'' = 'P'
344 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
345 AND NOT EXISTS
346 (
347 SELECT '2'
348 FROM
349 PO_HEADERS_ALL POH
350 , PO_RELEASES_ALL POR
351 , PO_DOCUMENT_TYPES PDT
352 /* , PO_VENDORS V Removed for bug 1751445 */
353 , PO_LINES_ALL POL
354 , PO_LINE_TYPES_B LT /* modified for bug 6367516 */
355 , PO_LINE_LOCATIONS_ALL PLL
356 , PER_ALL_PEOPLE_F BUY
357 , PER_ALL_PEOPLE_F REQ
358 , HR_ALL_ORGANIZATION_UNITS O
359 , PA_EXPENDITURE_TYPES ET
360 , PA_TASKS T
361 , PO_DISTRIBUTIONS_ALL POD
362 , PA_PROJECTS P
363 , GL_LEDGERS G /* Added for bug 3537697 */
364 WHERE
365 /* POH.VENDOR_ID = V.VENDOR_ID (+) Removed join for bug 1751445 */
366 POH.AGENT_ID = BUY.PERSON_ID
367 AND TRUNC(SYSDATE)
368 BETWEEN BUY.EFFECTIVE_START_DATE
369 AND BUY.EFFECTIVE_END_DATE
370 AND POD.Distribution_type <> 'PREPAYMENT'
371 AND POD.DELIVER_TO_PERSON_ID = REQ.PERSON_ID(+)
372 AND TRUNC(SYSDATE)
373 BETWEEN REQ.EFFECTIVE_START_DATE /* modified for bug 6367516 */
374 AND REQ.EFFECTIVE_END_DATE
375 AND NVL(POH.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
376 AND NVL(PLL.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED')
377 AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
378 AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED')
379 AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
380 AND PDT.DOCUMENT_TYPE_CODE IN ('PO','PA')
381 AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
382 AND NVL(POH.CANCEL_FLAG,'N') = 'N'
383 AND DECODE(POR.RELEASE_NUM,NULL,'OPEN',NVL(POR.CLOSED_CODE,'OPEN'))
384 NOT IN ('CLOSED','FINALLY CLOSED')
385 AND DECODE(POR.RELEASE_NUM,NULL,'N',NVL(POR.CANCEL_FLAG,'N')) = 'N'
386 AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
387 AND POL.LINE_TYPE_ID = LT.LINE_TYPE_ID
388 AND POL.PO_LINE_ID = PLL.PO_LINE_ID
389 AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
390 AND POD.PROJECT_ID = P.PROJECT_ID
391 AND POD.TASK_ID = T.TASK_ID
392 AND POD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
393 AND POD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
394 AND POD.PROJECT_ID = p_ProjectID
395 AND POH.PO_Header_ID = CMT.CMT_Header_ID
396 AND G.LEDGER_ID = POD.SET_OF_BOOKS_ID /* Added for bug 3537697 */
397 AND POD.PO_Distribution_ID = CMT.CMT_Distribution_ID
398 and CMT.task_id = nvl(pod.task_id,0)
399 and NVL(CMT.description,'<prm>') = NVL(POL.item_description,'<prm>')
400 and NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(POD.expenditure_item_date,sysdate-15000)
401 and NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(decode(POR.release_num,NULL,POH.creation_date,POR.creation_date),sysdate-15000)
402 and NVL(CMT.cmt_approved_date,sysdate-15000) = NVL(decode(POR.release_num,NULL,POH.approved_date,POR.approved_date),sysdate-15000)
403 and NVL(CMT.cmt_requestor_name,'<prm>') = NVL(REQ.full_name,'<prm>')
404 and NVL(CMT.cmt_buyer_name,'<prm>') = NVL(BUY.full_name,'<prm>')
405 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>')
406 and NVL(CMT.vendor_id,-1) = NVL(POH.vendor_id,-1) /* Changed for bug 1751445 */
407 and NVL(CMT.expenditure_type,'<prm>') = NVL(POD.expenditure_type,'<prm>')
408 and NVL(CMT.organization_id,0) = NVL(O.organization_id,0)
409 and CMT.expenditure_category = ET.expenditure_category
410 and CMT.revenue_category = ET.revenue_category_code
411 and NVL(CMT.unit_of_measure,'<prm>') = NVL(decode(pll.value_basis,'AMOUNT',NULL,POL.unit_meas_lookup_code),'<prm>')
412 and NVL(CMT.unit_price,0) = NVL(TO_NUMBER(DECODE(pll.value_basis, 'AMOUNT', NULL,
413 pa_multi_currency.convert_amount_sql(POH.CURRENCY_CODE, G.CURRENCY_CODE,
414 POD.RATE_DATE, POH.RATE_TYPE,
415 NVL(POD.RATE, 1), PLL.PRICE_OVERRIDE ) )) , 0)
416 /* Added above code and commented the below for Bug 3537697
417 NVL(TO_NUMBER(decode(pll.value_basis,'AMOUNT',NULL,( PLL.price_override * NVL(POD.rate,1)))),0) */
418 and CMT.original_quantity_ordered = POD.quantity_ordered
419 and NVL(CMT.quantity_cancelled,0) = NVL(POD.quantity_cancelled,0)
420 and NVL(CMT.quantity_delivered,0) = NVL(POD.quantity_delivered,0)
421 and CMT.quantity_invoiced = NVL(POD.quantity_billed,0)
422 and nvl(CMT.denom_raw_cost,0) = GREATEST(0,(PA_CMT_UTILS.get_rcpt_qty(pod.po_distribution_id,
423 POD.QUANTITY_ORDERED,
424 NVL(POD.QUANTITY_CANCELLED,0),
425 NVL(POD.QUANTITY_BILLED,0),'PO',
426 pol.po_line_id,
427 t.project_id,
428 t.task_id,
429 pod.code_combination_id,0, NULL, NULL, NULL, NULL, nvl(g.sla_ledger_cash_basis_flag,'N')))) * /*Bug#4905552*/
430 ((PLL.PRICE_OVERRIDE) +(NVL(POD.NONRECOVERABLE_TAX,0) / POD.QUANTITY_ORDERED))
431 /* Added above condition and commented this for bug 3537697
432 GREATEST(0,(POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0)
433 -NVL(POD.QUANTITY_BILLED,0))) * ((PLL.PRICE_OVERRIDE) +
434 (NVL(POD.NONRECOVERABLE_TAX,0) / POD.QUANTITY_ORDERED)) */
435 and NVL(CMT.denom_burdened_cost,0) =
436 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
437 NULL
438 , 'CMT'
439 , T.task_id
440 , POD.expenditure_item_date
441 , POD.expenditure_type
442 , O.organization_id
443 , 'C'
444 , GREATEST(0,(PA_CMT_UTILS.get_rcpt_qty(pod.po_distribution_id,
445 POD.QUANTITY_ORDERED,
446 NVL(POD.QUANTITY_CANCELLED,0),
447 NVL(POD.QUANTITY_BILLED,0),'PO',
448 pol.po_line_id,
449 t.project_id,
450 t.task_id,
451 pod.code_combination_id,0,NULL,NULL, NULL, NULL, nvl(g.sla_ledger_cash_basis_flag,'N')))) * /*Bug#4905552*/
452 ((PLL.PRICE_OVERRIDE) +(NVL(POD.NONRECOVERABLE_TAX,0) / POD.QUANTITY_ORDERED))
453 /* Added above condition and commented this for bug 3537697
454 , GREATEST(0,(POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0)
455 -NVL(POD.QUANTITY_BILLED,0))) * ((PLL.PRICE_OVERRIDE) +
456 (NVL(POD.NONRECOVERABLE_TAX,0) / POD.QUANTITY_ORDERED)) */
457 ),0)
458 )
459 );
460
461 Return v_tmp;
462
463 Exception
464 WHEN NO_DATA_FOUND THEN
465 v_tmp := 'N';
466
467 End; -- Fifth Block, for updated POs
468
469
470 /* Sixth Block: UPDATED INVOICES
471 Checks the AP Inv Distributions' tables against PA_Commitment_Txns
472 for updated Invoices
473 Note: For AP Invoices, all amounts are captured in Oracle Payables as denom amounts.
474 */
475
476 v_tmp := 'N';
477
478 Begin -- Sixth Block
479
480 SELECT 'Y'
481 INTO v_tmp
482 FROM DUAL
483 WHERE EXISTS
484 (
485 SELECT '1'
486 FROM PA_COMMITMENT_TXNS CMT
487 WHERE CMT.Project_ID = p_ProjectID
488 AND CMT.Burden_Sum_Dest_Run_ID is NULL
489 AND CMT.Line_Type||'' = 'I'
490 AND CMT.Transaction_Source = 'ORACLE_PAYABLES'
491 AND NOT EXISTS
492 (
493 SELECT '2'
494 FROM
495 AP_INVOICE_DISTRIBUTIONS_ALL D
496 , AP_INVOICES_ALL I
497 , PO_VENDORS V
498 , HR_ALL_ORGANIZATION_UNITS O
499 , PA_EXPEND_TYP_SYS_LINKS ES
500 , PA_EXPENDITURE_TYPES ET
501 , PA_TASKS T
502 , PO_DISTRIBUTIONS PO
503 , PA_PROJECTS P
504 WHERE
505 I.vendor_id = V.vendor_id
506 AND I.invoice_id = D.invoice_id
507 AND decode(D.pa_addition_flag,'Z','Y','T','Y','E','Y', null,'N',D.pa_addition_flag) <> 'Y'
508 AND ( ES.system_linkage_function = 'VI' OR
509 ( ES.system_linkage_function = 'ER' AND
510 V.employee_id IS NOT NULL ))
511 AND D.po_distribution_id = PO.po_distribution_id (+)
512 AND NVL(PO.Distribution_type, 'XX') <> 'PREPAYMENT'
513 AND nvl(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
514 AND D.project_id = P.project_id
515 AND D.task_id = T.task_id
516 AND D.expenditure_organization_id = O.organization_id
517 AND D.expenditure_type = ES.expenditure_type
518 AND ET.expenditure_type = ES.expenditure_type
519 /*Bug#2061817:Added PA_IC_INVOICES in the condition for i.source*/
520 AND nvl(I.source, 'xxx') not in ('Oracle Project Accounting','PA_IC_INVOICES')
521 AND D.project_id = p_ProjectID
522 and CMT.task_id = nvl(d.task_id,0)
523 AND I.Invoice_ID = CMT.CMT_Header_ID
524 AND CMT.CMT_DISTRIBUTION_ID = D.invoice_distribution_id
525 and NVL(CMT.description,'<prm>') = NVL(D.description,'<prm>')
526 and NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(D.expenditure_item_date,sysdate-15000)
527 and NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(I.invoice_date,sysdate-15000)
528 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')
529 and CMT.vendor_id = I.vendor_id
530 and NVL(CMT.expenditure_type,'<prm>') = NVL(D.expenditure_type,'<prm>')
531 and NVL(CMT.organization_id,0) = NVL(O.organization_id,0)
532 and CMT.expenditure_category = ET.expenditure_category
533 and CMT.revenue_category = ET.revenue_category_code
534 and NVL(CMT.denom_raw_cost,0) = NVL(D.amount,0)
535 and NVL(CMT.tot_cmt_quantity,0) = NVL(D.pa_quantity,0)
536 and NVL(CMT.denom_burdened_cost,0) =
537 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
538 NULL
539 , 'CMT'
540 , T.task_id
541 , D.expenditure_item_date
542 , D.expenditure_type
543 , O.organization_id
544 , 'C'
545 , D.amount),0)
546 )
547 );
548
549 Return v_tmp;
550
551 Exception
552 WHEN NO_DATA_FOUND THEN
553 v_tmp := 'N';
554
555 End; -- Sixth Block, for updated AP Invoices
556
557
558 /* Seventh Block: UPADTED REQUISTIONS
559 Checks the PO Req Distributions' tables against PA_Commitment_Txns
560 for updated Purchase Requisitions
561
562 Note: For Requisitions, unit price is always captured in accounting currency! Therefore,
563 the raw cost and burdened cost comparative joins in this block use ACCT columns.
564
565 Please note that this is different than Updated POs and AP Invoices.
566 */
567
568 v_tmp := 'N';
569
570 Begin -- Seventh Block
571
572 SELECT 'Y'
573 INTO v_tmp
574 FROM DUAL
575 WHERE EXISTS
576 (
577 SELECT '1'
578 FROM PA_COMMITMENT_TXNS CMT
579 WHERE CMT.Project_ID = p_ProjectID
580 AND CMT.Burden_Sum_Dest_Run_ID is NULL
581 AND CMT.Line_Type||'' = 'R'
582 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
583 AND NOT EXISTS
584 (
585 SELECT '2'
586 FROM
587 PO_REQ_DISTRIBUTIONS_ALL RD
588 , PO_REQUISITION_LINES_ALL RL
589 , PO_REQUISITION_HEADERS_ALL RH
590 , PO_DOCUMENT_TYPES_ALL_TL PDT /* modified for bug 4758887 */
591 , PO_LINE_TYPES_B LT /* modified for bug 6367516 */
592 , PER_ALL_PEOPLE_F REQ
593 , PA_TASKS T
594 , HR_ALL_ORGANIZATION_UNITS O
595 , PA_EXPENDITURE_TYPES ET
596 , PA_PROJECTS P
597 WHERE
598 RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
599 AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
600 AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
601 AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
602 AND NVL( PDT.ORG_ID , -99 ) = NVL( RH.ORG_ID , -99 ) /* added for bug 4758887 */
603 AND PDT.LANGUAGE = USERENV('LANG') /* added for bug 4758887 */
604 AND RL.LINE_LOCATION_ID IS NULL
605 AND NVL(RL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
606 AND NVL(RL.CANCEL_FLAG,'N') = 'N'
607 AND NVL(RL.MODIFIED_BY_AGENT_FLAG,'N') = 'N'
608 AND RL.SOURCE_TYPE_CODE = 'VENDOR'
609 AND REQ.PERSON_ID = RL.TO_PERSON_ID
610 AND TRUNC(SYSDATE)
611 BETWEEN REQ.EFFECTIVE_START_DATE /* modified for bug 6367516 */
612 AND REQ.EFFECTIVE_END_DATE
613 AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
614 AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
615 AND RD.PROJECT_ID = P.PROJECT_ID
616 AND RD.TASK_ID = T.TASK_ID
617 AND RD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
618 AND RD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
619 AND RD.PROJECT_ID = p_ProjectID
620 and CMT.task_id = nvl(rd.task_id,0)
621 and CMT.cmt_distribution_id = RD.Distribution_ID
622 and CMT.description = RL.item_description
623 and NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(RD.expenditure_item_date,sysdate-15000)
624 and NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(RL.creation_date,sysdate-15000)
625 and CMT.cmt_approved_flag = decode(NVL(RH.authorization_status,'NOT APPROVED'),'APPROVED','Y','N')
626 and NVL(CMT.cmt_need_by_date,sysdate-15000) = NVL(RL.need_by_date,sysdate-15000)
627 and NVL(CMT.vendor_id,0) = NVL(RL.vendor_id,0)
628 and NVL(CMT.expenditure_type,'<prm>') = NVL(RD.expenditure_type,'<prm>')
629 and NVL(CMT.organization_id,0) = NVL(O.organization_id,0)
630 and CMT.expenditure_category = ET.expenditure_category
631 and CMT.revenue_category = ET.revenue_category_code
632 and CMT.acct_raw_cost = (RD.REQ_LINE_QUANTITY * RL.UNIT_PRICE) + NVL(RD.NONRECOVERABLE_TAX,0)
633 and CMT.tot_cmt_quantity = RD.req_line_quantity
634 and NVL(CMT.acct_burdened_cost,0) =
635 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
636 NULL
637 , 'CMT'
638 , T.task_id
639 , RD.expenditure_item_date
640 , RD.expenditure_type
641 , O.organization_id
642 , 'C'
643 , (RD.REQ_LINE_QUANTITY * RL.UNIT_PRICE) + NVL(RD.NONRECOVERABLE_TAX,0)
644 ),0)
645 )
646 );
647
648 Return v_tmp;
649
650 Exception
651 WHEN NO_DATA_FOUND THEN
652 v_tmp := 'N';
653 End; -- Seventh Block, for updated PO Reqs
654
655
656 /* Eighth Block: UPDATED MFG COMMITMENTS
657 Checks the CST_PROJMFG_CMT_VIEW against PA_Commitment_Txns
658 for updated commitments
659
660 Note: For MFG commitments, the view returns both acct and denom amounts for most amount
661 columns, So, where possible, joins are performed for denom amounts.
662 */
663
664 v_tmp := 'N';
665
666 Begin -- Eighth Block
667
668 SELECT 'Y'
669 INTO v_tmp
670 FROM DUAL
671 WHERE EXISTS
672 (
673 SELECT '1'
674 FROM PA_COMMITMENT_TXNS CMT
675 WHERE CMT.Project_ID = p_ProjectID
676 AND CMT.Burden_Sum_Dest_Run_ID is NULL
677 AND CMT.Transaction_Source = 'ORACLE_MANUFACTURING'
678 AND NOT EXISTS
679 (
680 SELECT '2'
681 FROM CST_PROJMFG_CMT_VIEW CST
682 WHERE
683 CST.Project_ID = p_ProjectID
684 AND CMT.task_id = nvl(CST.task_id,0)
685 AND nvl(CMT.CMT_Header_ID,0) = nvl(CST.CMT_Header_ID,0)
686 AND nvl(CMT.CMT_Line_Number,0) = nvl(CST.CMT_Line_Number,0)
687 AND nvl(CMT.CMT_Distribution_ID,0) = nvl(CST.CMT_Distribution_ID,0)
688 and nvl(cmt.DESCRIPTION,'<prm>') = nvl(cst.DESCRIPTION,'<prm>')
689 and nvl(cmt.EXPENDITURE_ITEM_DATE,sysdate-15000) = nvl(cst.EXPENDITURE_ITEM_DATE,sysdate-15000)
690 and nvl(cmt.CMT_LINE_NUMBER,0) = nvl(cst.CMT_LINE_NUMBER,0)
691 and nvl(cmt.CMT_CREATION_DATE,sysdate-15000) = nvl(cst.CMT_CREATION_DATE,sysdate-15000)
692 and nvl(cmt.CMT_APPROVED_DATE,sysdate-15000) = nvl(cst.CMT_APPROVED_DATE,sysdate-15000)
693 and nvl(cmt.CMT_REQUESTOR_NAME,'<prm>') = nvl(cst.CMT_REQUESTOR_NAME,'<prm>')
694 and nvl(cmt.CMT_BUYER_NAME,'<prm>') = nvl(cst.CMT_BUYER_NAME,'<prm>')
695 and nvl(cmt.CMT_APPROVED_FLAG,'<prm>') = nvl(cst.CMT_APPROVED_FLAG,'<prm>')
696 and nvl(cmt.CMT_PROMISED_DATE,sysdate-15000) = nvl(cst.CMT_PROMISED_DATE,sysdate-15000)
697 and nvl(cmt.CMT_NEED_BY_DATE,sysdate-15000) = nvl(cst.CMT_NEED_BY_DATE,sysdate-15000)
698 and nvl(cmt.ORGANIZATION_ID,0) = nvl(cst.ORGANIZATION_ID,0)
699 and nvl(cmt.VENDOR_ID,0) = nvl(cst.VENDOR_ID,0)
700 and nvl(cmt.EXPENDITURE_TYPE,'<prm>') = nvl(cst.EXPENDITURE_TYPE,'<prm>')
701 and nvl(cmt.EXPENDITURE_CATEGORY,'<prm>') = nvl(cst.EXPENDITURE_CATEGORY,'<prm>')
702 and nvl(cmt.REVENUE_CATEGORY,'<prm>') = nvl(cst.REVENUE_CATEGORY,'<prm>')
703 and nvl(cmt.UNIT_OF_MEASURE,'<prm>') = nvl(cst.UNIT_OF_MEASURE,'<prm>')
704 and nvl(cmt.UNIT_PRICE,0) = nvl(cst.UNIT_PRICE,0)
705 and nvl(cmt.denom_RAW_COST,0) = nvl(cst.denom_RAW_COST,0)
706 and nvl(cmt.denom_BURDENED_COST,0) = nvl(cst.denom_BURDENED_COST,0)
707 and nvl(cmt.TOT_CMT_QUANTITY,0) = nvl(cst.TOT_CMT_QUANTITY,0)
708 and nvl(cmt.QUANTITY_ORDERED,0) = nvl(cst.QUANTITY_ORDERED,0)
709 and nvl(cmt.AMOUNT_ORDERED,0) = nvl(cst.AMOUNT_ORDERED,0)
710 and nvl(cmt.ORIGINAL_QUANTITY_ORDERED,0) = nvl(cst.ORIGINAL_QUANTITY_ORDERED,0)
711 and nvl(cmt.ORIGINAL_AMOUNT_ORDERED,0) = nvl(cst.ORIGINAL_AMOUNT_ORDERED,0)
712 and nvl(cmt.QUANTITY_CANCELLED,0) = nvl(cst.QUANTITY_CANCELLED,0)
713 and nvl(cmt.AMOUNT_CANCELLED,0) = nvl(cst.AMOUNT_CANCELLED,0)
714 and nvl(cmt.QUANTITY_DELIVERED,0) = nvl(cst.QUANTITY_DELIVERED,0)
715 and nvl(cmt.AMOUNT_DELIVERED,0) = nvl(cst.AMOUNT_DELIVERED,0)
716 and nvl(cmt.QUANTITY_INVOICED,0) = nvl(cst.QUANTITY_INVOICED,0)
717 and nvl(cmt.AMOUNT_INVOICED,0) = nvl(cst.AMOUNT_INVOICED,0)
718 and nvl(cmt.QUANTITY_OUTSTANDING_DELIVERY,0) = nvl(cst.QUANTITY_OUTSTANDING_DELIVERY,0)
719 and nvl(cmt.AMOUNT_OUTSTANDING_DELIVERY,0) = nvl(cst.AMOUNT_OUTSTANDING_DELIVERY,0)
720 and nvl(cmt.QUANTITY_OUTSTANDING_INVOICE,0) = nvl(cst.QUANTITY_OUTSTANDING_INVOICE,0)
721 and nvl(cmt.AMOUNT_OUTSTANDING_INVOICE,0) = nvl(cst.AMOUNT_OUTSTANDING_INVOICE,0)
722 and nvl(cmt.QUANTITY_OVERBILLED,0) = nvl(cst.QUANTITY_OVERBILLED,0)
723 and nvl(cmt.AMOUNT_OVERBILLED,0) = nvl(cst.AMOUNT_OVERBILLED,0)
724 and nvl(cmt.ORIGINAL_TXN_REFERENCE1,'<prm>') = nvl(cst.ORIGINAL_TXN_REFERENCE1,'<prm>')
725 and nvl(cmt.ORIGINAL_TXN_REFERENCE2,'<prm>') = nvl(cst.ORIGINAL_TXN_REFERENCE2,'<prm>')
726 and nvl(cmt.ORIGINAL_TXN_REFERENCE3,'<prm>') = nvl(cst.ORIGINAL_TXN_REFERENCE3,'<prm>')
727 )
728 );
729
730 Return v_tmp;
731
732 Exception
733 WHEN NO_DATA_FOUND THEN
734 v_tmp := 'N';
735 End; -- Eighth Block, for updated commitments from CST_PROJMFG_CMT_VIEW
736
737
738 /* Ninth Block: NEW MFG COMMITMENTS
739 Checks the CST_PROJMFG_CMT_VIEW against PA_Commitment_Txns
740 for new commitments
741 */
742
743 v_tmp := 'N';
744
745 Begin -- Ninth Block
746
747 SELECT 'Y'
748 INTO v_tmp
749 FROM DUAL
750 WHERE EXISTS (
751 SELECT '1'
752 FROM
753 CST_PROJMFG_CMT_VIEW CST
754 WHERE
755 CST.PROJECT_ID = p_ProjectID
756 AND NOT EXISTS (
757 SELECT '2'
758 FROM PA_COMMITMENT_TXNS CMT
759 WHERE CMT.Transaction_Source = 'ORACLE_MANUFACTURING'
760 AND CMT.Project_ID = p_ProjectID
761 AND CMT.Burden_Sum_Dest_Run_ID is NULL
762 AND CMT.CMT_Header_ID = CST.CMT_Header_ID
763 AND CMT.CMT_Line_Number = CST.CMT_Line_Number
764 AND CMT.CMT_Distribution_ID = CST.CMT_Distribution_ID )
765 );
766
767 Return v_tmp;
768
769 Exception
770 WHEN NO_DATA_FOUND THEN
771 v_tmp := 'N';
772 End; -- Ninth Block, for new commitments from CST_PROJMFG_CMT_VIEW
773
774 /* Added the following four blocks for the bug #3631172. */
775
776 /* Tenth Block: NEW Shop Floor/Inventory REQUISITIONS
777 Checks the PJM Req Commitments View against PA_Commitment_Txns
778 for new Shop Floor/Inventory Purchase Requisitions.
779 */
780
781 v_tmp := 'N';
782
783 Begin -- Tenth Block
784 /* Commented and Modified for bug 4360855 as below as suggested by PJM Team.
785 SELECT 'Y'
786 INTO v_tmp
787 FROM DUAL
788 WHERE EXISTS (
789 SELECT '1'
790 FROM
791 PJM_REQ_COMMITMENTS_V PJREQ
792 WHERE
793 PJREQ.Project_ID = p_ProjectID
794 AND NOT EXISTS (
795 SELECT '2'
796 FROM PA_COMMITMENT_TXNS CMT
797 WHERE CMT.Line_Type = 'R'
798 AND CMT.Project_ID = p_ProjectID
799 AND CMT.Burden_Sum_Dest_Run_ID is NULL
800 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
801 AND CMT.CMT_Header_ID = PJREQ.Requisition_Header_ID
802 AND CMT.CMT_Line_Number = PJREQ.Req_Line
803 AND CMT.CMT_Distribution_ID = PJREQ.Req_Distribution_ID )
804 );*/
805 /* Bug 5517898 */
806 SELECT 1 -- common project
807 INTO tmp
808 FROM dual where exists (select 1 from pjm_org_parameters
809 WHERE common_project_id = p_ProjectID);
810 /* Bug 5517898 */
811
812 FOR c_rec in c loop
813 Begin
814 SELECT 'Y'
815 INTO v_tmp
816 FROM DUAL
817 WHERE EXISTS (
818 SELECT REQUISITION_HEADER_ID,
819 LINE_NUM,
820 DISTRIBUTION_ID
821 from (
822 SELECT RL.REQUISITION_HEADER_ID,
823 RL.LINE_NUM,
824 RD.DISTRIBUTION_ID
825 FROM PO_REQ_DISTRIBUTIONS_ALL RD
826 , PO_REQUISITION_LINES_ALL RL
827 , PJM_ORG_PARAMETERS POP
828 , MTL_SYSTEM_ITEMS MSI
829 , MTL_UNITS_OF_MEASURE_VL UOM
830 WHERE RL.DESTINATION_TYPE_CODE = 'INVENTORY'
831 AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
832 AND RD.PROJECT_ID IS NULL
833 AND POP.COMMON_PROJECT_ID = p_ProjectID
834 AND POP.ORGANIZATION_ID = c_rec.organization_id
835 AND POP.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
836 AND MSI.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
837 AND MSI.INVENTORY_ITEM_ID = RL.ITEM_ID
838 AND UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE
839 UNION ALL
840 SELECT RL.REQUISITION_HEADER_ID,
841 RL.LINE_NUM,
842 RD.DISTRIBUTION_ID
843 FROM PO_REQ_DISTRIBUTIONS_ALL RD
844 , PO_REQUISITION_LINES_ALL RL
845 , PJM_ORG_PARAMETERS POP
846 , WIP_DISCRETE_JOBS WDJ
847 , WIP_OPERATIONS WO
848 , BOM_DEPARTMENTS BD
849 , MTL_UNITS_OF_MEASURE_VL UOM
850 WHERE RL.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
851 AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
852 AND RD.PROJECT_ID IS NULL
853 AND POP.COMMON_PROJECT_ID = p_ProjectID
854 AND RL.DESTINATION_ORGANIZATION_ID = c_rec.organization_id
855 AND POP.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
856 AND WDJ.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
857 AND WDJ.WIP_ENTITY_ID = RL.WIP_ENTITY_ID
858 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
859 AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
860 AND WO.OPERATION_SEQ_NUM = RL.WIP_OPERATION_SEQ_NUM
861 AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
862 AND UOM.UNIT_OF_MEASURE = RL.UNIT_MEAS_LOOKUP_CODE)
863 WHERE NOT EXISTS (
864 SELECT '2'
865 FROM PA_COMMITMENT_TXNS CMT
866 WHERE CMT.Line_Type = 'R'
867 AND CMT.Project_ID = p_ProjectID
868 AND CMT.Burden_Sum_Dest_Run_ID is NULL
869 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
870 AND CMT.CMT_Header_ID = REQUISITION_HEADER_ID
871 AND CMT.CMT_Line_Number = LINE_NUM
872 AND CMT.CMT_Distribution_ID = DISTRIBUTION_ID )
873 );
874 Return v_tmp;
875 Exception
876 WHEN NO_DATA_FOUND THEN
877 v_tmp := 'N';
878 End;
879 end loop;
880 EXCEPTION
881 WHEN NO_DATA_FOUND THEN -- Regular Project
882 Begin
883
884 SELECT 'Y'
885 INTO v_tmp
886 FROM DUAL
887 WHERE EXISTS (
888 SELECT REQUISITION_HEADER_ID,
889 LINE_NUM,
890 DISTRIBUTION_ID
891 from (
892 SELECT RL.REQUISITION_HEADER_ID,
893 RL.LINE_NUM,
894 RD.DISTRIBUTION_ID
895 FROM PO_REQ_DISTRIBUTIONS_ALL RD
896 , PO_REQUISITION_LINES_ALL RL
897 , MTL_SYSTEM_ITEMS MSI
898 , MTL_UNITS_OF_MEASURE_VL UOM
899 WHERE RL.DESTINATION_TYPE_CODE = 'INVENTORY'
900 AND RD.PROJECT_ID = p_ProjectID
901 AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
902 AND MSI.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
903 AND MSI.INVENTORY_ITEM_ID = RL.ITEM_ID
904 AND UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE
905 UNION ALL
906 SELECT RL.REQUISITION_HEADER_ID,
907 RL.LINE_NUM,
908 RD.DISTRIBUTION_ID
909 FROM PO_REQ_DISTRIBUTIONS_ALL RD
910 , PO_REQUISITION_LINES_ALL RL
911 , WIP_DISCRETE_JOBS WDJ
912 , WIP_OPERATIONS WO
913 , BOM_DEPARTMENTS BD
914 , MTL_UNITS_OF_MEASURE_VL UOM
915 WHERE RL.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
916 AND RD.PROJECT_ID = p_ProjectID
917 AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
918 AND WDJ.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
919 AND WDJ.WIP_ENTITY_ID = RL.WIP_ENTITY_ID
920 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
921 AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
922 AND WO.OPERATION_SEQ_NUM = RL.WIP_OPERATION_SEQ_NUM
923 AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
924 AND UOM.UNIT_OF_MEASURE = RL.UNIT_MEAS_LOOKUP_CODE)
925 WHERE NOT EXISTS (
926 SELECT '2'
927 FROM PA_COMMITMENT_TXNS CMT
928 WHERE CMT.Line_Type = 'R'
929 AND CMT.Project_ID = p_ProjectID
930 AND CMT.Burden_Sum_Dest_Run_ID is NULL
931 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
932 AND CMT.CMT_Header_ID = REQUISITION_HEADER_ID
933 AND CMT.CMT_Line_Number = LINE_NUM
934 AND CMT.CMT_Distribution_ID = DISTRIBUTION_ID )
935 );
936 Return v_tmp;
937
938 Exception
939 WHEN NO_DATA_FOUND THEN
940 v_tmp := 'N';
941 End;
942 End; -- Tenth Block, for new Shop Floor/Inventory Reqs
943
944 /* Eleventh Block: UPDATED Shop Floor/Inventory REQUISTIONS
945 Checks the PJM Req Commitments View against PA_Commitment_Txns
946 for updated Shop Floor/Inventory Purchase Requisitions.
947
948 */
949
950 v_tmp := 'N';
951
952 Begin -- Eleventh Block
953
954 SELECT 'Y'
955 INTO v_tmp
956 FROM DUAL
957 WHERE EXISTS
958 (
959 SELECT '1'
960 FROM PA_COMMITMENT_TXNS CMT
961 WHERE CMT.Project_ID = p_ProjectID
962 AND CMT.Burden_Sum_Dest_Run_ID is NULL
963 AND CMT.Line_Type||'' = 'R'
964 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
965 AND NOT EXISTS
966 (
967 SELECT '2'
968 FROM
969 PJM_REQ_COMMITMENTS_V PJREQ
970 WHERE PJREQ.PROJECT_ID = p_ProjectID
971 AND CMT.task_id = nvl(PJREQ.task_id,0)
972 AND CMT.cmt_distribution_id = PJREQ.REQ_DISTRIBUTION_ID
973 AND CMT.description = PJREQ.item_description
974 AND NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(PJREQ.expenditure_item_date,sysdate-15000)
975 AND NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(PJREQ.creation_date,sysdate-15000)
976 AND CMT.cmt_approved_flag = PJREQ.APPROVED_FLAG
977 AND NVL(CMT.cmt_need_by_date,sysdate-15000) = NVL(PJREQ.need_by_date,sysdate-15000)
978 AND NVL(CMT.vendor_id,0) = NVL(PJREQ.vendor_id,0)
979 AND NVL(CMT.expenditure_type,'<prm>') = NVL(PJREQ.expenditure_type,'<prm>')
980 AND NVL(CMT.organization_id,0) = NVL(PJREQ.expenditure_organization_id,0)
981 AND CMT.expenditure_category = PJREQ.expenditure_category
982 AND CMT.revenue_category = PJREQ.revenue_category
983 AND CMT.acct_raw_cost = PJREQ.amount
984 AND CMT.tot_cmt_quantity = PJREQ.quantity
985 AND NVL(CMT.acct_burdened_cost,0) =
986 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
987 NULL
988 , 'CMT'
989 , PJREQ.task_id
990 , PJREQ.expenditure_item_date
991 , PJREQ.expenditure_type
992 , PJREQ.expenditure_organization_id
993 , 'C'
994 , PJREQ.amount
995 ),0)
996 )
997 );
998
999 Return v_tmp;
1000
1001 Exception
1002 WHEN NO_DATA_FOUND THEN
1003 v_tmp := 'N';
1004 End; -- Eleventh Block, for updated Shop Floor/Inventory Reqs
1005
1006 /* Twelvth Block: NEW Shop Floor/Inventory POs
1007 Checks the PJM PO Commitments View against PA_Commitment_Txns
1008 for new Shop Floor/Inventory Purchase Orders
1009 */
1010
1011 v_tmp := 'N';
1012
1013 Begin -- Twelvth Block
1014 /* Commented and Modified for bug 4360855 as below as suggested by PJM Team.
1015 SELECT 'Y'
1016 INTO v_tmp
1017 FROM DUAL
1018 WHERE EXISTS
1019 (
1020 SELECT '1'
1021 FROM
1022 PJM_PO_COMMITMENTS_V PJPO
1023 WHERE
1024 PJPO.Project_ID = p_ProjectID
1025 AND NOT EXISTS (
1026 SELECT '2'
1027 FROM PA_COMMITMENT_TXNS CMT
1028 WHERE CMT.Line_Type = 'P'
1029 AND CMT.Project_ID = p_ProjectID
1030 AND CMT.Burden_Sum_Dest_Run_ID is NULL
1031 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
1032 AND CMT.CMT_Header_ID = PJPO.PO_Header_ID
1033 AND CMT.CMT_Line_Number = PJPO.PO_Line
1034 AND CMT.CMT_Distribution_ID = PJPO.PO_Distribution_ID )
1035 );*/
1036 /* Bug 5517898 */
1037 SELECT 1 -- common project
1038 INTO tmp
1039 FROM dual where exists (select 1 from pjm_org_parameters
1040 WHERE common_project_id = p_ProjectID);
1041 /* Bug 5517898 */
1042 FOR c_rec in c loop
1043 Begin
1044 SELECT 'Y'
1045 INTO v_tmp
1046 FROM DUAL
1047 WHERE EXISTS (
1048 SELECT PO_HEADER_ID,
1049 LINE_NUM,
1050 PO_DISTRIBUTION_ID
1051 FROM (
1052 SELECT POL.PO_HEADER_ID,
1053 POL.LINE_NUM,
1054 POD.PO_DISTRIBUTION_ID
1055 FROM PO_DISTRIBUTIONS_ALL POD
1056 , PO_LINES_ALL POL
1057 , PJM_ORG_PARAMETERS POP
1058 , MTL_SYSTEM_ITEMS MSI
1059 , MTL_UNITS_OF_MEASURE_VL UOM
1060 WHERE POD.DESTINATION_TYPE_CODE = 'INVENTORY'
1061 AND POL.PO_LINE_ID = POD.PO_LINE_ID
1062 AND POD.PROJECT_ID IS NULL
1063 AND POP.COMMON_PROJECT_ID = p_ProjectID
1064 AND POP.ORGANIZATION_ID = c_rec.organization_id
1065 AND POP.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
1066 AND MSI.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
1067 AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
1068 AND UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE
1069 UNION ALL
1070 SELECT POL.PO_HEADER_ID,
1071 POL.LINE_NUM,
1072 POD.PO_DISTRIBUTION_ID
1073 FROM PO_DISTRIBUTIONS_ALL POD
1074 , PO_LINES_ALL POL
1075 , PJM_ORG_PARAMETERS POP
1076 , WIP_DISCRETE_JOBS WDJ
1077 , WIP_OPERATIONS WO
1078 , BOM_DEPARTMENTS BD
1079 , MTL_UNITS_OF_MEASURE_VL UOM
1080 WHERE POD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
1081 AND POL.PO_LINE_ID = POD.PO_LINE_ID
1082 AND POL.PROJECT_ID IS NULL
1083 AND POP.COMMON_PROJECT_ID = p_ProjectID
1084 AND POP.ORGANIZATION_ID = c_rec.organization_id
1085 AND POP.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
1086 AND WDJ.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
1087 AND WDJ.WIP_ENTITY_ID = POD.WIP_ENTITY_ID
1088 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
1089 AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
1090 AND WO.OPERATION_SEQ_NUM = POD.WIP_OPERATION_SEQ_NUM
1091 AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
1092 AND UOM.UNIT_OF_MEASURE = POL.UNIT_MEAS_LOOKUP_CODE)
1093 WHERE NOT EXISTS (
1094 SELECT '2'
1095 FROM PA_COMMITMENT_TXNS CMT
1096 WHERE CMT.Line_Type = 'P'
1097 AND CMT.Project_ID = p_ProjectID
1098 AND CMT.Burden_Sum_Dest_Run_ID is NULL
1099 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
1100 AND CMT.CMT_Header_ID = PO_HEADER_ID
1101 AND CMT.CMT_Line_Number = LINE_NUM
1102 AND CMT.CMT_Distribution_ID = PO_DISTRIBUTION_ID )
1103 );
1104 Return v_tmp;
1105 Exception
1106 WHEN NO_DATA_FOUND THEN
1107 v_tmp := 'N';
1108 End;
1109 end loop;
1110 EXCEPTION
1111 WHEN NO_DATA_FOUND THEN -- Regular Project
1112 Begin
1113
1114 SELECT 'Y'
1115 INTO v_tmp
1116 FROM DUAL
1117 WHERE EXISTS (
1118 SELECT PO_HEADER_ID,
1119 LINE_NUM,
1120 PO_DISTRIBUTION_ID
1121 FROM (
1122 SELECT POL.PO_HEADER_ID,
1123 POL.LINE_NUM,
1124 POD.PO_DISTRIBUTION_ID
1125 FROM PO_DISTRIBUTIONS_ALL POD
1126 , PO_LINES_ALL POL
1127 , MTL_SYSTEM_ITEMS MSI
1128 , MTL_UNITS_OF_MEASURE_VL UOM
1129 WHERE POD.DESTINATION_TYPE_CODE = 'INVENTORY'
1130 AND POD.PROJECT_ID = p_ProjectID
1131 AND POL.PO_LINE_ID = POD.PO_LINE_ID
1132 AND MSI.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
1133 AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
1134 AND UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE
1135 UNION ALL
1136 SELECT POL.PO_HEADER_ID,
1137 POL.LINE_NUM,
1138 POD.PO_DISTRIBUTION_ID
1139 FROM PO_DISTRIBUTIONS_ALL POD
1140 , PO_LINES_ALL POL
1141 , WIP_DISCRETE_JOBS WDJ
1142 , WIP_OPERATIONS WO
1143 , BOM_DEPARTMENTS BD
1144 , MTL_UNITS_OF_MEASURE_VL UOM
1145 WHERE POD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
1146 AND POD.PROJECT_ID = p_ProjectID
1147 AND POL.PO_LINE_ID = POD.PO_LINE_ID
1148 AND WDJ.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
1149 AND WDJ.WIP_ENTITY_ID = POD.WIP_ENTITY_ID
1150 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
1151 AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
1152 AND WO.OPERATION_SEQ_NUM = POD.WIP_OPERATION_SEQ_NUM
1153 AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
1154 AND UOM.UNIT_OF_MEASURE = POL.UNIT_MEAS_LOOKUP_CODE)
1155 WHERE NOT EXISTS (
1156 SELECT '2'
1157 FROM PA_COMMITMENT_TXNS CMT
1158 WHERE CMT.Line_Type = 'P'
1159 AND CMT.Project_ID = p_ProjectID
1160 AND CMT.Burden_Sum_Dest_Run_ID is NULL
1161 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
1162 AND CMT.CMT_Header_ID = PO_HEADER_ID
1163 AND CMT.CMT_Line_Number = LINE_NUM
1164 AND CMT.CMT_Distribution_ID = PO_DISTRIBUTION_ID )
1165 );
1166 Return v_tmp;
1167
1168 Exception
1169 WHEN NO_DATA_FOUND THEN
1170 v_tmp := 'N';
1171 End;
1172 End; -- Twelvth Block, for new Shop Floor/Inventory POs
1173
1174 /* Thirteenth Block: Updated Shop Floor/Inventory POs
1175 Checks the PJM PO Commitments View against PA_Commitment_Txns
1176 for new Shop Floor/Inventory Purchase Orders
1177 */
1178
1179 v_tmp := 'N';
1180
1181 Begin -- Thirteenth Block
1182
1183 SELECT 'Y'
1184 INTO v_tmp
1185 FROM DUAL
1186 WHERE EXISTS
1187 (
1188 SELECT '1'
1189 FROM PA_COMMITMENT_TXNS CMT
1190 WHERE CMT.Project_ID = p_ProjectID
1191 AND CMT.Burden_Sum_Dest_Run_ID is NULL
1192 AND CMT.Line_Type||'' = 'P'
1193 AND CMT.Transaction_Source = 'ORACLE_PURCHASING'
1194 AND NOT EXISTS
1195 (
1196 SELECT '2'
1197 FROM
1198 PJM_PO_COMMITMENTS_V PJPO
1199 , PO_HEADERS_ALL POH
1200 , PO_LINE_LOCATIONS_ALL PLL
1201 , PO_DISTRIBUTIONS_ALL POD
1202 , PO_LINES_ALL POL
1203 , GL_LEDGERS G
1204 WHERE PJPO.PROJECT_ID = p_ProjectID
1205 AND PJPO.PO_Header_ID = POH.PO_Header_ID
1206 AND POL.PO_Header_ID = POH.PO_Header_ID
1207 AND POD.Distribution_type <> 'PREPAYMENT'
1208 AND PJPO.PO_Line = POL.Line_Num
1209 AND PJPO.PO_Distribution_ID = POD.PO_Distribution_ID
1210 AND POL.PO_Line_ID = PLL.PO_Line_ID
1211 AND PLL.Line_Location_ID = POD.Line_Location_ID
1212 AND PJPO.PO_Header_ID = CMT.CMT_Header_ID
1213 AND PJPO.PO_Distribution_ID = CMT.CMT_Distribution_ID
1214 AND CMT.task_id = nvl(PJPO.task_id,0)
1215 AND NVL(CMT.description,'<prm>') = NVL(PJPO.item_description,'<prm>')
1216 AND NVL(CMT.expenditure_item_date,sysdate-15000) = NVL(PJPO.expenditure_item_date,sysdate-15000)
1217 AND NVL(CMT.cmt_creation_date,sysdate-15000) = NVL(PJPO.creation_date,sysdate-15000)
1218 AND NVL(CMT.cmt_approved_date,sysdate-15000) = NVL(PJPO.approved_date,sysdate-15000)
1219 AND NVL(CMT.cmt_requestor_name,'<prm>') = NVL(PJPO.requestor_name,'<prm>')
1220 AND NVL(CMT.cmt_buyer_name,'<prm>') = NVL(PJPO.buyer_name,'<prm>')
1221 AND NVL(CMT.cmt_approved_flag,'<prm>') = NVL(PJPO.approved_flag,'<prm>')
1222 AND NVL(CMT.vendor_id,-1) = NVL(PJPO.vendor_id,-1)
1223 AND NVL(CMT.expenditure_type,'<prm>') = NVL(PJPO.expenditure_type,'<prm>')
1224 AND NVL(CMT.organization_id,0) = NVL(PJPO.Expenditure_Organization_ID,0)
1225 AND CMT.expenditure_category = PJPO.expenditure_category
1226 AND CMT.revenue_category = PJPO.revenue_category
1227 AND NVL(CMT.unit_of_measure,'<prm>') = NVL(PJPO.unit,'<prm>')
1228 AND NVL(CMT.unit_price,0) = NVL(PJPO.unit_price , 0)
1229 AND CMT.original_quantity_ordered = PJPO.quantity_ordered
1230 AND NVL(CMT.quantity_cancelled,0) = NVL(PJPO.quantity_cancelled,0)
1231 AND NVL(CMT.quantity_delivered,0) = NVL(PJPO.quantity_delivered,0)
1232 AND CMT.quantity_invoiced = NVL(PJPO.quantity_invoiced,0)
1233 AND G.LEDGER_ID = POD.SET_OF_BOOKS_ID
1234 AND nvl(CMT.denom_raw_cost,0) = GREATEST(0,(PA_CMT_UTILS.get_rcpt_qty(PJPO.po_distribution_id,
1235 PJPO.QUANTITY_ORDERED,
1236 NVL(PJPO.QUANTITY_CANCELLED,0),
1237 NVL(PJPO.QUANTITY_INVOICED,0),'PO',
1238 PJPO.po_line_id,
1239 PJPO.project_id,
1240 PJPO.task_id,
1241 POD.code_combination_id,0,NULL,NULL, NULL, NULL, nvl(g.sla_ledger_cash_basis_flag,'N')))) * /*Bug#4905552*/
1242 ((PLL.PRICE_OVERRIDE) +(NVL(POD.NONRECOVERABLE_TAX,0) / PJPO.QUANTITY_ORDERED))
1243 AND NVL(CMT.denom_burdened_cost,0) =
1244 NVL(PA_BURDEN_CMTS.get_cmt_burdened_cost(
1245 NULL
1246 , 'CMT'
1247 , PJPO.task_id
1248 , PJPO.expenditure_item_date
1249 , PJPO.expenditure_type
1250 , PJPO.expenditure_organization_id
1251 , 'C'
1252 , GREATEST(0,(PA_CMT_UTILS.get_rcpt_qty(PJPO.po_distribution_id,
1253 PJPO.QUANTITY_ORDERED,
1254 NVL(PJPO.QUANTITY_CANCELLED,0),
1255 NVL(PJPO.QUANTITY_INVOICED,0),'PO',
1256 PJPO.po_line_id,
1257 PJPO.project_id,
1258 PJPO.task_id,
1259 pod.code_combination_id,0,NULL,NULL, NULL, NULL, nvl(g.sla_ledger_cash_basis_flag,'N')))) * /*Bug#4905552*/
1260 ((PLL.PRICE_OVERRIDE) +(NVL(POD.NONRECOVERABLE_TAX,0) / PJPO.QUANTITY_ORDERED))
1261 ),0)
1262 )
1263 ) ;
1264
1265 Return v_tmp;
1266
1267 Exception
1268 WHEN NO_DATA_FOUND THEN
1269 v_tmp := 'N';
1270 End; -- Thirteenth Block, for updated Shop Floor/Inventory POs
1271
1272 /* End of code added for the bug #3631172. */
1273
1274 Return v_tmp;
1275
1276 END Commitments_Changed;
1277
1278 END PA_Check_Commitments;
1279