[Home] [Help]
422: p_realtime_sync_flag IN VARCHAR2 DEFAULT 'Y',
423: p_error_data IN VARCHAR2
424: ) IS
425: BEGIN
426: INSERT INTO hz_dqm_sync_interface (
427: PARTY_ID,
428: RECORD_ID,
429: PARTY_SITE_ID,
430: ORG_CONTACT_ID,
453: hz_utility_pub.creation_date,
454: hz_utility_pub.last_update_login,
455: hz_utility_pub.last_update_date,
456: hz_utility_pub.user_id,
457: HZ_DQM_SYNC_INTERFACE_S.nextval
458: );
459: END insert_dqm_sync_error_rec;
460:
461:
1113: EXIT WHEN c_contact%NOTFOUND;
1114: BEGIN
1115: sync_single_contact_online(l_org_contact_id, p_operation);
1116: EXCEPTION WHEN OTHERS THEN
1117: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1118: -- FOR ONLINE FLOWS
1119: l_sql_err_message := SQLERRM;
1120: insert_dqm_sync_error_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U','E','Y', l_sql_err_message);
1121: END ;
1389: END LOOP;
1390:
1391: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1392: -- insert a record into hz_dqm_sh_sync_interface table for each record
1393: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1394: BEGIN
1395: HZ_DQM_SYNC.insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_operation);
1396: EXCEPTION WHEN OTHERS THEN
1397: NULL;
1391: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1392: -- insert a record into hz_dqm_sh_sync_interface table for each record
1393: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1394: BEGIN
1395: HZ_DQM_SYNC.insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_operation);
1396: EXCEPTION WHEN OTHERS THEN
1397: NULL;
1398: END;
1399: END IF;
1398: END;
1399: END IF;
1400:
1401: EXCEPTION WHEN OTHERS THEN
1402: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1403: -- FOR ONLINE FLOWS
1404: l_sql_err_message := SQLERRM;
1405: insert_dqm_sync_error_rec(p_party_id, NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'Y', l_sql_err_message);
1406: END;
1542: INTO H_ORG_CONTACT_ID(1), H_PARTY_ID(1), H_STATUS(1)
1543: , H_TX2(1)
1544: , H_TX11(1)
1545: , H_TX22(1)
1546: FROM HZ_ORG_CONTACTS oc, HZ_DQM_SYNC_INTERFACE d,
1547: HZ_RELATIONSHIPS r, HZ_PERSON_PROFILES pp
1548: WHERE d.ENTITY = 'CONTACTS'
1549: AND oc.org_contact_id = p_org_contact_id
1550: AND oc.org_contact_id = d.RECORD_ID
1786: WHERE PARTY_ID = H_PARTY_ID(1);
1787:
1788: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1789: -- insert a record into hz_dqm_sh_sync_interface table for each record
1790: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1791: BEGIN
1792: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_operation);
1793: EXCEPTION WHEN OTHERS THEN
1794: NULL;
1788: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1789: -- insert a record into hz_dqm_sh_sync_interface table for each record
1790: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1791: BEGIN
1792: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_operation);
1793: EXCEPTION WHEN OTHERS THEN
1794: NULL;
1795: END;
1796: END IF;
1795: END;
1796: END IF;
1797:
1798: EXCEPTION WHEN OTHERS THEN
1799: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1800: -- FOR ONLINE FLOWS
1801: l_sql_err_message := SQLERRM;
1802: insert_dqm_sync_error_rec(l_party_id, p_org_contact_id, NULL, NULL, 'CONTACTS', p_operation, 'E', 'Y', l_sql_err_message);
1803: END;
1986: , H_TX10(1)
1987: , H_TX11(1)
1988: , H_TX12(1)
1989: , H_TX13(1)
1990: FROM HZ_CONTACT_POINTS cp, HZ_DQM_SYNC_INTERFACE d
1991: WHERE d.ENTITY = 'CONTACT_POINTS'
1992: AND cp.contact_point_id = p_contact_point_id
1993: AND cp.contact_point_id = d.RECORD_ID
1994: AND (cp.status is null OR cp.status = 'A' or cp.status = 'I') and rownum = 1 ;
2340: WHERE PARTY_ID = H_PARTY_ID(1);
2341:
2342: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2343: -- insert a record into hz_dqm_sh_sync_interface table for each record
2344: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2345: BEGIN
2346: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_operation);
2347: EXCEPTION WHEN OTHERS THEN
2348: NULL;
2342: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2343: -- insert a record into hz_dqm_sh_sync_interface table for each record
2344: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2345: BEGIN
2346: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_operation);
2347: EXCEPTION WHEN OTHERS THEN
2348: NULL;
2349: END;
2350: END IF;
2349: END;
2350: END IF;
2351:
2352: EXCEPTION WHEN OTHERS THEN
2353: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
2354: -- FOR ONLINE FLOWS
2355: l_sql_err_message := SQLERRM;
2356: insert_dqm_sync_error_rec(l_party_id, p_contact_point_id, l_party_site_id, l_org_contact_id, 'CONTACT_POINTS', p_operation, 'E', 'Y', l_sql_err_message);
2357: END;
2573: , H_TX22(1)
2574: , H_TX24(1)
2575: , H_TX25(1)
2576: , H_TX28(1)
2577: FROM HZ_PARTY_SITES ps, HZ_DQM_SYNC_INTERFACE d, HZ_LOCATIONS l
2578: WHERE d.ENTITY='PARTY_SITES'
2579: AND ps.party_site_id = p_party_site_id
2580: AND d.record_id = ps.party_site_id
2581: AND ps.location_id = l.location_id
2958: WHERE PARTY_ID = H_PARTY_ID(1);
2959:
2960: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2961: -- insert a record into hz_dqm_sh_sync_interface table for each record
2962: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2963: BEGIN
2964: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_operation);
2965: EXCEPTION WHEN OTHERS THEN
2966: NULL;
2960: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2961: -- insert a record into hz_dqm_sh_sync_interface table for each record
2962: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2963: BEGIN
2964: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_operation);
2965: EXCEPTION WHEN OTHERS THEN
2966: NULL;
2967: END;
2968: END IF;
2967: END;
2968: END IF;
2969:
2970: EXCEPTION WHEN OTHERS THEN
2971: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
2972: -- FOR ONLINE FLOWS
2973: l_sql_err_message := SQLERRM;
2974: insert_dqm_sync_error_rec(l_party_id, p_party_site_id, NULL, l_org_contact_id, 'PARTY_SITES', p_operation, 'E', 'Y', l_sql_err_message);
2975: END;
2978: p_operation IN VARCHAR2,
2979: p_party_type IN VARCHAR2,
2980: p_from_rec IN VARCHAR2,
2981: p_to_rec IN VARCHAR2,
2982: x_sync_party_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
2983:
2984: BEGIN
2985:
2986: IF p_party_type = 'ORGANIZATION' THEN
2998: ,op.SIC_CODE_TYPE
2999: ,p.CATEGORY_CODE
3000: ,p.REFERENCE_USE_FLAG
3001: ,op.CORPORATION_CLASS
3002: FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op, HZ_DQM_SYNC_INTERFACE dsi
3003: WHERE p.party_id = op.party_id
3004: AND p.party_id = dsi.party_id
3005: AND p.PARTY_TYPE = 'ORGANIZATION'
3006: AND dsi.entity = 'PARTY'
3024: ,NULL
3025: ,p.CATEGORY_CODE
3026: ,p.REFERENCE_USE_FLAG
3027: ,NULL
3028: FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe, HZ_DQM_SYNC_INTERFACE dsi
3029: WHERE p.party_id = pe.party_id
3030: AND p.party_id = dsi.party_id
3031: AND p.PARTY_TYPE = 'PERSON'
3032: AND dsi.entity = 'PARTY'
3050: ,NULL
3051: ,p.CATEGORY_CODE
3052: ,p.REFERENCE_USE_FLAG
3053: ,NULL
3054: FROM HZ_PARTIES p, HZ_DQM_SYNC_INTERFACE dsi
3055: WHERE p.party_id = dsi.party_id
3056: AND dsi.entity = 'PARTY'
3057: AND dsi.staged_flag = 'N'
3058: AND dsi.operation = p_operation
3067:
3068: PROCEDURE sync_all_parties (
3069: p_operation IN VARCHAR2,
3070: p_bulk_sync_type IN VARCHAR2,
3071: p_sync_all_party_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3072:
3073: l_limit NUMBER := 200;
3074: l_last_fetch BOOLEAN := FALSE;
3075: l_sql_errm VARCHAR2(2000);
3232: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3233: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3234: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3235: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3236: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='C' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3237: ELSE
3238: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3239: UPDATE hz_dqm_sync_interface
3240: SET error_data = l_sql_errm
3235: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3236: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='C' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3237: ELSE
3238: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3239: UPDATE hz_dqm_sync_interface
3240: SET error_data = l_sql_errm
3241: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3242: WHERE rowid = H_ROWID(l_err_index);
3243: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3240: SET error_data = l_sql_errm
3241: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3242: WHERE rowid = H_ROWID(l_err_index);
3243: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3244: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3245: insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
3246: END IF;
3247: END IF;
3248: END LOOP;
3289: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3290: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3291: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3292: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3293: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='U' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3294: ELSE
3295: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3296: UPDATE hz_dqm_sync_interface
3297: SET error_data = l_sql_errm
3292: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3293: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='U' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3294: ELSE
3295: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3296: UPDATE hz_dqm_sync_interface
3297: SET error_data = l_sql_errm
3298: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3299: WHERE rowid = H_ROWID(l_err_index);
3300: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3297: SET error_data = l_sql_errm
3298: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3299: WHERE rowid = H_ROWID(l_err_index);
3300: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3301: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3302: insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
3303: END IF;
3304: END IF;
3305: END LOOP;
3309: -- REPURI. Bug 4884742.
3310: -- Bulk Insert the Import Parties into Shadow Sync Interface table
3311: -- if Shadow Staging has already run and completed successfully
3312: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3313: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3314: BEGIN
3315: -- REPURI. Bug 4968126.
3316: -- Using the Merge instead of Insert statement
3317: -- so that duplicate records dont get inserted.
3378: PROCEDURE open_sync_party_site_cursor (
3379: p_operation IN VARCHAR2,
3380: p_from_rec IN VARCHAR2,
3381: p_to_rec IN VARCHAR2,
3382: x_sync_party_site_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3383: BEGIN
3384: OPEN x_sync_party_site_cur FOR
3385: SELECT /*+ ORDERED USE_NL(ps l) */
3386: ps.PARTY_SITE_ID
3399: ,l.COUNTRY
3400: ,ps.IDENTIFYING_ADDRESS_FLAG
3401: ,ps.STATUS
3402: ,l.ADDRESS1
3403: FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_PARTY_SITES ps, HZ_LOCATIONS l
3404: WHERE dsi.record_id = ps.party_site_id
3405: AND dsi.entity = 'PARTY_SITES'
3406: AND dsi.operation = p_operation
3407: AND dsi.staged_flag = 'N'
3413:
3414: PROCEDURE sync_all_party_sites (
3415: p_operation IN VARCHAR2,
3416: p_bulk_sync_type IN VARCHAR2,
3417: p_sync_all_party_site_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3418:
3419: l_limit NUMBER := 200;
3420: l_last_fetch BOOLEAN := FALSE;
3421: l_sql_errm VARCHAR2(2000);
3562: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3563: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3564: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3565: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3566: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='C' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3567: ELSE
3568: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3569: UPDATE hz_dqm_sync_interface
3570: SET error_data = l_sql_errm
3565: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3566: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='C' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3567: ELSE
3568: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3569: UPDATE hz_dqm_sync_interface
3570: SET error_data = l_sql_errm
3571: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3572: WHERE rowid = H_ROWID(l_err_index);
3573: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3570: SET error_data = l_sql_errm
3571: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3572: WHERE rowid = H_ROWID(l_err_index);
3573: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3574: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3575: insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), 'PARTY_SITES', p_operation, 'E', 'N', l_sql_errm);
3576: END IF;
3577: END IF;
3578: END LOOP;
3614: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3615: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3616: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3617: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3618: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='U' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3619: ELSE
3620: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3621: UPDATE hz_dqm_sync_interface
3622: SET error_data = l_sql_errm
3617: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3618: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='U' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3619: ELSE
3620: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3621: UPDATE hz_dqm_sync_interface
3622: SET error_data = l_sql_errm
3623: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3624: WHERE rowid = H_ROWID(l_err_index);
3625: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3622: SET error_data = l_sql_errm
3623: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3624: WHERE rowid = H_ROWID(l_err_index);
3625: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3626: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3627: insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), 'PARTY_SITES', p_operation, 'E', 'N', l_sql_errm);
3628: END IF;
3629: END IF;
3630: END LOOP;
3647: -- REPURI. Bug 4884742.
3648: -- Bulk Insert of Import Party Sites into Shadow Sync Interface table
3649: -- if Shadow Staging has already run and completed successfully
3650: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3651: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3652: BEGIN
3653: -- REPURI. Bug 4968126.
3654: -- Using the Merge instead of Insert statement
3655: -- so that duplicate records dont get inserted.
3719: PROCEDURE open_sync_contact_cursor (
3720: p_operation IN VARCHAR2,
3721: p_from_rec IN VARCHAR2,
3722: p_to_rec IN VARCHAR2,
3723: x_sync_contact_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3724: BEGIN
3725: OPEN x_sync_contact_cur FOR
3726: SELECT
3727: /*+ leading(dsi) USE_NL(OC R PP) */
3732: ,dsi.ROWID
3733: ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
3734: ,oc.CONTACT_NUMBER
3735: ,oc.JOB_TITLE
3736: FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_RELATIONSHIPS r,
3737: HZ_ORG_CONTACTS oc, HZ_PERSON_PROFILES pp
3738: WHERE oc.party_relationship_id = r.relationship_id
3739: AND dsi.record_id = oc.org_contact_id
3740: AND r.subject_id = pp.party_id
3754:
3755: PROCEDURE sync_all_contacts (
3756: p_operation IN VARCHAR2,
3757: p_bulk_sync_type IN VARCHAR2,
3758: p_sync_all_contact_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3759:
3760: l_limit NUMBER := 200;
3761: l_last_fetch BOOLEAN := FALSE;
3762: l_sql_errm VARCHAR2(2000);
3851: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3852: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3853: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3854: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3855: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='C' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3856: ELSE
3857: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3858: UPDATE hz_dqm_sync_interface
3859: SET error_data = l_sql_errm
3854: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3855: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='C' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3856: ELSE
3857: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3858: UPDATE hz_dqm_sync_interface
3859: SET error_data = l_sql_errm
3860: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3861: WHERE rowid = H_ROWID(l_err_index);
3862: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3859: SET error_data = l_sql_errm
3860: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3861: WHERE rowid = H_ROWID(l_err_index);
3862: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3863: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3864: insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, 'CONTACTS', p_operation, 'E', 'N', l_sql_errm);
3865: END IF;
3866: END IF;
3867: END LOOP;
3890: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3891: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3892: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3893: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3894: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='U' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3895: ELSE
3896: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3897: UPDATE hz_dqm_sync_interface
3898: SET error_data = l_sql_errm
3893: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3894: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='U' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3895: ELSE
3896: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3897: UPDATE hz_dqm_sync_interface
3898: SET error_data = l_sql_errm
3899: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3900: WHERE rowid = H_ROWID(l_err_index);
3901: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3898: SET error_data = l_sql_errm
3899: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3900: WHERE rowid = H_ROWID(l_err_index);
3901: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3902: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3903: insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, 'CONTACTS', p_operation, 'E', 'N', l_sql_errm);
3904: END IF;
3905: END IF;
3906: END LOOP;
3923: -- REPURI. Bug 4884742.
3924: -- Bulk Insert of Import Contacts into Shadow Sync Interface table
3925: -- if Shadow Staging has already run and completed successfully
3926: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3927: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3928: BEGIN
3929: -- REPURI. Bug 4968126.
3930: -- Using the Merge instead of Insert statement
3931: -- so that duplicate records dont get inserted.
3993: PROCEDURE open_sync_cpt_cursor (
3994: p_operation IN VARCHAR2,
3995: p_from_rec IN VARCHAR2,
3996: p_to_rec IN VARCHAR2,
3997: x_sync_cpt_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3998: BEGIN
3999: OPEN x_sync_cpt_cur FOR
4000: SELECT
4001: /*+ ORDERED USE_NL(cp) */
4016: ,translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
4017: ,cp.PHONE_LINE_TYPE
4018: ,cp.STATUS
4019: ,cp.CONTACT_POINT_PURPOSE
4020: FROM HZ_DQM_SYNC_INTERFACE dsi,HZ_CONTACT_POINTS cp
4021: WHERE dsi.record_id = cp.contact_point_id
4022: AND dsi.entity = 'CONTACT_POINTS'
4023: AND dsi.operation = p_operation
4024: AND dsi.staged_flag = 'N'
4029:
4030: PROCEDURE sync_all_contact_points (
4031: p_operation IN VARCHAR2,
4032: p_bulk_sync_type IN VARCHAR2,
4033: p_sync_all_cpt_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
4034:
4035: l_limit NUMBER := 200;
4036: l_last_fetch BOOLEAN := FALSE;
4037: l_sql_errm VARCHAR2(2000);
4160: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
4161: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
4162: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
4163: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4164: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='C' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4165: ELSE
4166: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4167: UPDATE hz_dqm_sync_interface
4168: SET error_data = l_sql_errm
4163: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4164: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='C' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4165: ELSE
4166: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4167: UPDATE hz_dqm_sync_interface
4168: SET error_data = l_sql_errm
4169: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4170: WHERE rowid = H_ROWID(l_err_index);
4171: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4168: SET error_data = l_sql_errm
4169: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4170: WHERE rowid = H_ROWID(l_err_index);
4171: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4172: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
4173: insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), 'CONTACT_POINTS', p_operation, 'E', 'N', l_sql_errm);
4174: END IF;
4175: END IF;
4176: END LOOP;
4205: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
4206: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
4207: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
4208: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4209: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='U' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4210: ELSE
4211: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4212: UPDATE hz_dqm_sync_interface
4213: SET error_data = l_sql_errm
4208: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4209: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='U' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4210: ELSE
4211: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4212: UPDATE hz_dqm_sync_interface
4213: SET error_data = l_sql_errm
4214: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4215: WHERE rowid = H_ROWID(l_err_index);
4216: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4213: SET error_data = l_sql_errm
4214: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4215: WHERE rowid = H_ROWID(l_err_index);
4216: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4217: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
4218: insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), 'CONTACT_POINTS', p_operation, 'E', 'N', l_sql_errm);
4219: END IF;
4220: END IF;
4221: END LOOP;
4238: -- REPURI. Bug 4884742.
4239: -- Bulk Insert the Import of Contact Points into Shadow Sync Interface table
4240: -- if Shadow Staging has already run and completed successfully
4241: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
4242: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
4243: BEGIN
4244: -- REPURI. Bug 4968126.
4245: -- Using the Merge instead of Insert statement
4246: -- so that duplicate records dont get inserted.
4316: p_to_osr IN VARCHAR2,
4317: p_os IN VARCHAR2,
4318: p_party_type IN VARCHAR2,
4319: p_operation IN VARCHAR2,
4320: x_sync_party_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4321: BEGIN
4322: IF p_party_type = 'ORGANIZATION' THEN
4323: open x_sync_party_cur FOR
4324: SELECT p.PARTY_ID, p.STATUS, p.ROWID
4418: p_from_osr IN VARCHAR2,
4419: p_to_osr IN VARCHAR2,
4420: p_os IN VARCHAR2,
4421: p_operation IN VARCHAR2,
4422: x_sync_party_site_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4423: BEGIN
4424: OPEN x_sync_party_site_cur FOR
4425: SELECT /*+ ORDERED USE_NL(ps l) */
4426: ps.PARTY_SITE_ID
4462: p_from_osr IN VARCHAR2,
4463: p_to_osr IN VARCHAR2,
4464: p_os IN VARCHAR2,
4465: p_operation IN VARCHAR2,
4466: x_sync_contact_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4467: BEGIN
4468: OPEN x_sync_contact_cur FOR
4469: SELECT
4470: /*+ ORDERED USE_NL(R OC PP)*/
4534: p_from_osr IN VARCHAR2,
4535: p_to_osr IN VARCHAR2,
4536: p_os IN VARCHAR2,
4537: p_operation IN VARCHAR2,
4538: x_sync_cpt_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4539: BEGIN
4540: OPEN x_sync_cpt_cur FOR
4541: SELECT
4542: /*+ ORDERED USE_NL(cp) */