[Home] [Help]
55: OR PA.BILL_TO_CUSTOMER_ID=RACM.DUPLICATE_ID
56: OR PA.SHIP_TO_CUSTOMER_ID=RACM.DUPLICATE_ID )
57: Bug 3891382. Added the condition so that the cursor picks projects specific to the
58: org_id where customer merge has taken place.
59: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
60: WHERE PROJECT_ID = PA.PROJECT_ID ); commented for 3938428*/
61:
62: /* Modified cursor for bug 3938428*/
63: /*CURSOR cursor_1 IS SELECT PA.PROJECT_ID, PA.CUSTOMER_ID, PA.BILL_TO_ADDRESS_ID,
70: AND RACM.request_id = req_id
71: AND (PA.CUSTOMER_ID=RACM.DUPLICATE_ID
72: OR PA.BILL_TO_CUSTOMER_ID=RACM.DUPLICATE_ID
73: OR PA.SHIP_TO_CUSTOMER_ID=RACM.DUPLICATE_ID ))
74: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
75: WHERE PROJECT_ID = PA.PROJECT_ID ); Commented for performance bug 13787566 */
76:
77: /* Bug 3891382. Added the cursor to select the delete_duplicate_flag */
78: /* Added hint for bug 13787566 */
102: AND EXISTS
103: (SELECT
104: /*+ no_unnest */
105: 'X'
106: FROM PA_PROJECTS PP
107: WHERE PP.PROJECT_ID = PA.PROJECT_ID
108: );
109: /* Added hint for bug 13787566 */
110: CURSOR cursor_2 IS SELECT DUPLICATE_ID,DELETE_DUPLICATE_FLAG,CUSTOMER_ID /* for bug 6732730*/
307: /* Modified to var_cust_id for bug 7341412 */
308: SELECT count(*) INTO var_count1 FROM pa_project_customers
309: WHERE customer_id = var_dup_id
310: AND project_id NOT IN (
311: SELECT project_id FROM pa_projects);
312:
313: SELECT count(*) INTO var_count2 FROM pa_implementations_all
314: WHERE customer_id = var_dup_id
315: AND org_id NOT IN (
312:
313: SELECT count(*) INTO var_count2 FROM pa_implementations_all
314: WHERE customer_id = var_dup_id
315: AND org_id NOT IN (
316: SELECT DISTINCT org_id FROM pa_projects);
317:
318: var_count1 := var_count1 + var_count2;
319:
320: SELECT count(*) INTO var_count2 FROM pa_agreements_all
319:
320: SELECT count(*) INTO var_count2 FROM pa_agreements_all
321: WHERE customer_id = var_dup_id
322: AND org_id NOT IN (
323: SELECT DISTINCT org_id FROM pa_projects);
324:
325: var_count1 := var_count1 + var_count2;
326:
327: SELECT count(*) INTO var_count2 FROM pa_project_contacts
326:
327: SELECT count(*) INTO var_count2 FROM pa_project_contacts
328: WHERE customer_id = var_dup_id
329: AND project_id NOT IN (
330: SELECT project_id FROM pa_projects);
331:
332: var_count1 := var_count1 + var_count2;
333:
334: IF var_count1 > 0 THEN
1466: AND SET_NUMBER = set_no
1467: AND CUSTOMER_ID<>DUPLICATE_ID) RACM
1468: WHERE RACM.DUPLICATE_ID = PR.CUSTOMER_ID
1469: /* bug 3891382 */
1470: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1471: WHERE PROJECT_ID = PR.PROJECT_ID ));
1472: /* end 3891382 */
1473:
1474:
1487: WHERE RACM.DUPLICATE_ID = PR.CUSTOMER_ID
1488: AND RACM.PROCESS_FLAG = 'N'
1489: AND RACM.SET_NUMBER = set_no
1490: /* bug 3891382 */
1491: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1492: WHERE PROJECT_ID = PR.PROJECT_ID ));
1493: /* end 3891382 */
1494:
1495: --
1544: AND SET_NUMBER = set_no
1545: AND CUSTOMER_ID<>DUPLICATE_ID) RACM
1546: WHERE RACM.DUPLICATE_ID = PB.CUSTOMER_ID
1547: /* bug 3891382 */
1548: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1549: WHERE PROJECT_ID = PB.PROJECT_ID ));
1550: /* end 3891382 */
1551:
1552:
1565: WHERE RACM.DUPLICATE_ID = PB.CUSTOMER_ID
1566: AND RACM.PROCESS_FLAG = 'N'
1567: AND RACM.SET_NUMBER = set_no
1568: /* bug 3891382 */
1569: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1570: WHERE PROJECT_ID = PB.PROJECT_ID ));
1571: /* end 3891382 */
1572:
1573: --
1626: AND SET_NUMBER = set_no
1627: AND DUPLICATE_ID<>CUSTOMER_ID)RACM
1628: WHERE RACM.DUPLICATE_ID = PS.CUSTOMER_ID
1629: /* bug 3891382 */
1630: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1631: WHERE PROJECT_ID = PS.PROJECT_ID ));
1632: /* end 3891382 */
1633:
1634:
1646: WHERE RACM.DUPLICATE_ID = PS.CUSTOMER_ID
1647: AND RACM.PROCESS_FLAG = 'N'
1648: AND RACM.SET_NUMBER = set_no
1649: /* bug 3891382 */
1650: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1651: WHERE PROJECT_ID = PS.PROJECT_ID ));
1652: /* end 3891382 */
1653:
1654: --