[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 );
76:
77: /* Bug 3891382. Added the cursor to select the delete_duplicate_flag */
78: CURSOR cursor_2 IS SELECT DUPLICATE_ID,DELETE_DUPLICATE_FLAG,CUSTOMER_ID /* for bug 6732730*/
275: /* Modified to var_cust_id for bug 7341412 */
276: SELECT count(*) INTO var_count1 FROM pa_project_customers
277: WHERE customer_id = var_dup_id
278: AND project_id NOT IN (
279: SELECT project_id FROM pa_projects);
280:
281: SELECT count(*) INTO var_count2 FROM pa_implementations_all
282: WHERE customer_id = var_dup_id
283: AND org_id NOT IN (
280:
281: SELECT count(*) INTO var_count2 FROM pa_implementations_all
282: WHERE customer_id = var_dup_id
283: AND org_id NOT IN (
284: SELECT DISTINCT org_id FROM pa_projects);
285:
286: var_count1 := var_count1 + var_count2;
287:
288: SELECT count(*) INTO var_count2 FROM pa_agreements_all
287:
288: SELECT count(*) INTO var_count2 FROM pa_agreements_all
289: WHERE customer_id = var_dup_id
290: AND org_id NOT IN (
291: SELECT DISTINCT org_id FROM pa_projects);
292:
293: var_count1 := var_count1 + var_count2;
294:
295: SELECT count(*) INTO var_count2 FROM pa_project_contacts
294:
295: SELECT count(*) INTO var_count2 FROM pa_project_contacts
296: WHERE customer_id = var_dup_id
297: AND project_id NOT IN (
298: SELECT project_id FROM pa_projects);
299:
300: var_count1 := var_count1 + var_count2;
301:
302: IF var_count1 > 0 THEN
1428: AND SET_NUMBER = set_no
1429: AND CUSTOMER_ID<>DUPLICATE_ID) RACM
1430: WHERE RACM.DUPLICATE_ID = PR.CUSTOMER_ID
1431: /* bug 3891382 */
1432: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1433: WHERE PROJECT_ID = PR.PROJECT_ID ));
1434: /* end 3891382 */
1435:
1436:
1449: WHERE RACM.DUPLICATE_ID = PR.CUSTOMER_ID
1450: AND RACM.PROCESS_FLAG = 'N'
1451: AND RACM.SET_NUMBER = set_no
1452: /* bug 3891382 */
1453: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1454: WHERE PROJECT_ID = PR.PROJECT_ID ));
1455: /* end 3891382 */
1456:
1457: --
1506: AND SET_NUMBER = set_no
1507: AND CUSTOMER_ID<>DUPLICATE_ID) RACM
1508: WHERE RACM.DUPLICATE_ID = PB.CUSTOMER_ID
1509: /* bug 3891382 */
1510: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1511: WHERE PROJECT_ID = PB.PROJECT_ID ));
1512: /* end 3891382 */
1513:
1514:
1527: WHERE RACM.DUPLICATE_ID = PB.CUSTOMER_ID
1528: AND RACM.PROCESS_FLAG = 'N'
1529: AND RACM.SET_NUMBER = set_no
1530: /* bug 3891382 */
1531: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1532: WHERE PROJECT_ID = PB.PROJECT_ID ));
1533: /* end 3891382 */
1534:
1535: --
1588: AND SET_NUMBER = set_no
1589: AND DUPLICATE_ID<>CUSTOMER_ID)RACM
1590: WHERE RACM.DUPLICATE_ID = PS.CUSTOMER_ID
1591: /* bug 3891382 */
1592: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1593: WHERE PROJECT_ID = PS.PROJECT_ID ));
1594: /* end 3891382 */
1595:
1596:
1608: WHERE RACM.DUPLICATE_ID = PS.CUSTOMER_ID
1609: AND RACM.PROCESS_FLAG = 'N'
1610: AND RACM.SET_NUMBER = set_no
1611: /* bug 3891382 */
1612: AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1613: WHERE PROJECT_ID = PS.PROJECT_ID ));
1614: /* end 3891382 */
1615:
1616: --