[Home] [Help]
398: p_realtime_sync_flag IN VARCHAR2 DEFAULT 'Y',
399: p_error_data IN VARCHAR2
400: ) IS
401: BEGIN
402: INSERT INTO hz_dqm_sync_interface (
403: PARTY_ID,
404: RECORD_ID,
405: PARTY_SITE_ID,
406: ORG_CONTACT_ID,
429: hz_utility_pub.creation_date,
430: hz_utility_pub.last_update_login,
431: hz_utility_pub.last_update_date,
432: hz_utility_pub.user_id,
433: HZ_DQM_SYNC_INTERFACE_S.nextval
434: );
435: END insert_dqm_sync_error_rec;
436:
437:
1087: EXIT WHEN c_contact%NOTFOUND;
1088: BEGIN
1089: sync_single_contact_online(l_org_contact_id, p_operation);
1090: EXCEPTION WHEN OTHERS THEN
1091: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1092: -- FOR ONLINE FLOWS
1093: l_sql_err_message := SQLERRM;
1094: insert_dqm_sync_error_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U','E','Y', l_sql_err_message);
1095: END ;
1363: END LOOP;
1364:
1365: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1366: -- insert a record into hz_dqm_sh_sync_interface table for each record
1367: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1368: BEGIN
1369: HZ_DQM_SYNC.insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_operation);
1370: EXCEPTION WHEN OTHERS THEN
1371: NULL;
1365: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1366: -- insert a record into hz_dqm_sh_sync_interface table for each record
1367: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1368: BEGIN
1369: HZ_DQM_SYNC.insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_operation);
1370: EXCEPTION WHEN OTHERS THEN
1371: NULL;
1372: END;
1373: END IF;
1372: END;
1373: END IF;
1374:
1375: EXCEPTION WHEN OTHERS THEN
1376: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1377: -- FOR ONLINE FLOWS
1378: l_sql_err_message := SQLERRM;
1379: insert_dqm_sync_error_rec(p_party_id, NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'Y', l_sql_err_message);
1380: END;
1516: INTO H_ORG_CONTACT_ID(1), H_PARTY_ID(1), H_STATUS(1)
1517: , H_TX2(1)
1518: , H_TX11(1)
1519: , H_TX22(1)
1520: FROM HZ_ORG_CONTACTS oc, HZ_DQM_SYNC_INTERFACE d,
1521: HZ_RELATIONSHIPS r, HZ_PERSON_PROFILES pp
1522: WHERE d.ENTITY = 'CONTACTS'
1523: AND oc.org_contact_id = p_org_contact_id
1524: AND oc.org_contact_id = d.RECORD_ID
1760: WHERE PARTY_ID = H_PARTY_ID(1);
1761:
1762: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1763: -- insert a record into hz_dqm_sh_sync_interface table for each record
1764: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1765: BEGIN
1766: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_operation);
1767: EXCEPTION WHEN OTHERS THEN
1768: NULL;
1762: -- REPURI. Bug 4884742. If shadow staging is completely successfully
1763: -- insert a record into hz_dqm_sh_sync_interface table for each record
1764: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1765: BEGIN
1766: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_operation);
1767: EXCEPTION WHEN OTHERS THEN
1768: NULL;
1769: END;
1770: END IF;
1769: END;
1770: END IF;
1771:
1772: EXCEPTION WHEN OTHERS THEN
1773: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1774: -- FOR ONLINE FLOWS
1775: l_sql_err_message := SQLERRM;
1776: insert_dqm_sync_error_rec(l_party_id, p_org_contact_id, NULL, NULL, 'CONTACTS', p_operation, 'E', 'Y', l_sql_err_message);
1777: END;
1960: , H_TX10(1)
1961: , H_TX11(1)
1962: , H_TX12(1)
1963: , H_TX13(1)
1964: FROM HZ_CONTACT_POINTS cp, HZ_DQM_SYNC_INTERFACE d
1965: WHERE d.ENTITY = 'CONTACT_POINTS'
1966: AND cp.contact_point_id = p_contact_point_id
1967: AND cp.contact_point_id = d.RECORD_ID
1968: AND (cp.status is null OR cp.status = 'A' or cp.status = 'I') and rownum = 1 ;
2314: WHERE PARTY_ID = H_PARTY_ID(1);
2315:
2316: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2317: -- insert a record into hz_dqm_sh_sync_interface table for each record
2318: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2319: BEGIN
2320: 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);
2321: EXCEPTION WHEN OTHERS THEN
2322: NULL;
2316: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2317: -- insert a record into hz_dqm_sh_sync_interface table for each record
2318: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2319: BEGIN
2320: 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);
2321: EXCEPTION WHEN OTHERS THEN
2322: NULL;
2323: END;
2324: END IF;
2323: END;
2324: END IF;
2325:
2326: EXCEPTION WHEN OTHERS THEN
2327: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
2328: -- FOR ONLINE FLOWS
2329: l_sql_err_message := SQLERRM;
2330: 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);
2331: END;
2547: , H_TX22(1)
2548: , H_TX24(1)
2549: , H_TX25(1)
2550: , H_TX28(1)
2551: FROM HZ_PARTY_SITES ps, HZ_DQM_SYNC_INTERFACE d, HZ_LOCATIONS l
2552: WHERE d.ENTITY='PARTY_SITES'
2553: AND ps.party_site_id = p_party_site_id
2554: AND d.record_id = ps.party_site_id
2555: AND ps.location_id = l.location_id
2932: WHERE PARTY_ID = H_PARTY_ID(1);
2933:
2934: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2935: -- insert a record into hz_dqm_sh_sync_interface table for each record
2936: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2937: BEGIN
2938: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_operation);
2939: EXCEPTION WHEN OTHERS THEN
2940: NULL;
2934: -- REPURI. Bug 4884742. If shadow staging is completely successfully
2935: -- insert a record into hz_dqm_sh_sync_interface table for each record
2936: IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2937: BEGIN
2938: HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_operation);
2939: EXCEPTION WHEN OTHERS THEN
2940: NULL;
2941: END;
2942: END IF;
2941: END;
2942: END IF;
2943:
2944: EXCEPTION WHEN OTHERS THEN
2945: -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
2946: -- FOR ONLINE FLOWS
2947: l_sql_err_message := SQLERRM;
2948: 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);
2949: END;
2952: p_operation IN VARCHAR2,
2953: p_party_type IN VARCHAR2,
2954: p_from_rec IN VARCHAR2,
2955: p_to_rec IN VARCHAR2,
2956: x_sync_party_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
2957:
2958: BEGIN
2959:
2960: IF p_party_type = 'ORGANIZATION' THEN
2972: ,op.SIC_CODE_TYPE
2973: ,p.CATEGORY_CODE
2974: ,p.REFERENCE_USE_FLAG
2975: ,op.CORPORATION_CLASS
2976: FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op, HZ_DQM_SYNC_INTERFACE dsi
2977: WHERE p.party_id = op.party_id
2978: AND p.party_id = dsi.party_id
2979: AND p.PARTY_TYPE = 'ORGANIZATION'
2980: AND dsi.entity = 'PARTY'
2998: ,NULL
2999: ,p.CATEGORY_CODE
3000: ,p.REFERENCE_USE_FLAG
3001: ,NULL
3002: FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe, HZ_DQM_SYNC_INTERFACE dsi
3003: WHERE p.party_id = pe.party_id
3004: AND p.party_id = dsi.party_id
3005: AND p.PARTY_TYPE = 'PERSON'
3006: AND dsi.entity = 'PARTY'
3024: ,NULL
3025: ,p.CATEGORY_CODE
3026: ,p.REFERENCE_USE_FLAG
3027: ,NULL
3028: FROM HZ_PARTIES p, HZ_DQM_SYNC_INTERFACE dsi
3029: WHERE p.party_id = dsi.party_id
3030: AND dsi.entity = 'PARTY'
3031: AND dsi.staged_flag = 'N'
3032: AND dsi.operation = p_operation
3041:
3042: PROCEDURE sync_all_parties (
3043: p_operation IN VARCHAR2,
3044: p_bulk_sync_type IN VARCHAR2,
3045: p_sync_all_party_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3046:
3047: l_limit NUMBER := 200;
3048: l_last_fetch BOOLEAN := FALSE;
3049: l_sql_errm VARCHAR2(2000);
3206: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3207: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3208: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3209: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3210: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='C' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3211: ELSE
3212: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3213: UPDATE hz_dqm_sync_interface
3214: SET error_data = l_sql_errm
3209: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3210: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='C' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3211: ELSE
3212: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3213: UPDATE hz_dqm_sync_interface
3214: SET error_data = l_sql_errm
3215: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3216: WHERE rowid = H_ROWID(l_err_index);
3217: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3214: SET error_data = l_sql_errm
3215: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3216: WHERE rowid = H_ROWID(l_err_index);
3217: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3218: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3219: insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
3220: END IF;
3221: END IF;
3222: END LOOP;
3263: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3264: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3265: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3266: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3267: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='U' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3268: ELSE
3269: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3270: UPDATE hz_dqm_sync_interface
3271: SET error_data = l_sql_errm
3266: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3267: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='U' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3268: ELSE
3269: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3270: UPDATE hz_dqm_sync_interface
3271: SET error_data = l_sql_errm
3272: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3273: WHERE rowid = H_ROWID(l_err_index);
3274: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3271: SET error_data = l_sql_errm
3272: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3273: WHERE rowid = H_ROWID(l_err_index);
3274: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3275: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3276: insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
3277: END IF;
3278: END IF;
3279: END LOOP;
3283: -- REPURI. Bug 4884742.
3284: -- Bulk Insert the Import Parties into Shadow Sync Interface table
3285: -- if Shadow Staging has already run and completed successfully
3286: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3287: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3288: BEGIN
3289: -- REPURI. Bug 4968126.
3290: -- Using the Merge instead of Insert statement
3291: -- so that duplicate records dont get inserted.
3352: PROCEDURE open_sync_party_site_cursor (
3353: p_operation IN VARCHAR2,
3354: p_from_rec IN VARCHAR2,
3355: p_to_rec IN VARCHAR2,
3356: x_sync_party_site_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3357: BEGIN
3358: OPEN x_sync_party_site_cur FOR
3359: SELECT /*+ ORDERED USE_NL(ps l) */
3360: ps.PARTY_SITE_ID
3373: ,l.COUNTRY
3374: ,ps.IDENTIFYING_ADDRESS_FLAG
3375: ,ps.STATUS
3376: ,l.ADDRESS1
3377: FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_PARTY_SITES ps, HZ_LOCATIONS l
3378: WHERE dsi.record_id = ps.party_site_id
3379: AND dsi.entity = 'PARTY_SITES'
3380: AND dsi.operation = p_operation
3381: AND dsi.staged_flag = 'N'
3387:
3388: PROCEDURE sync_all_party_sites (
3389: p_operation IN VARCHAR2,
3390: p_bulk_sync_type IN VARCHAR2,
3391: p_sync_all_party_site_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3392:
3393: l_limit NUMBER := 200;
3394: l_last_fetch BOOLEAN := FALSE;
3395: l_sql_errm VARCHAR2(2000);
3536: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3537: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3538: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3539: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3540: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='C' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3541: ELSE
3542: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3543: UPDATE hz_dqm_sync_interface
3544: SET error_data = l_sql_errm
3539: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3540: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='C' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3541: ELSE
3542: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3543: UPDATE hz_dqm_sync_interface
3544: SET error_data = l_sql_errm
3545: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3546: WHERE rowid = H_ROWID(l_err_index);
3547: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3544: SET error_data = l_sql_errm
3545: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3546: WHERE rowid = H_ROWID(l_err_index);
3547: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3548: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3549: 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);
3550: END IF;
3551: END IF;
3552: END LOOP;
3588: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3589: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3590: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3591: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3592: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='U' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3593: ELSE
3594: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3595: UPDATE hz_dqm_sync_interface
3596: SET error_data = l_sql_errm
3591: log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3592: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='U' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3593: ELSE
3594: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3595: UPDATE hz_dqm_sync_interface
3596: SET error_data = l_sql_errm
3597: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3598: WHERE rowid = H_ROWID(l_err_index);
3599: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3596: SET error_data = l_sql_errm
3597: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3598: WHERE rowid = H_ROWID(l_err_index);
3599: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3600: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3601: 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);
3602: END IF;
3603: END IF;
3604: END LOOP;
3621: -- REPURI. Bug 4884742.
3622: -- Bulk Insert of Import Party Sites into Shadow Sync Interface table
3623: -- if Shadow Staging has already run and completed successfully
3624: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3625: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3626: BEGIN
3627: -- REPURI. Bug 4968126.
3628: -- Using the Merge instead of Insert statement
3629: -- so that duplicate records dont get inserted.
3693: PROCEDURE open_sync_contact_cursor (
3694: p_operation IN VARCHAR2,
3695: p_from_rec IN VARCHAR2,
3696: p_to_rec IN VARCHAR2,
3697: x_sync_contact_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3698: BEGIN
3699: OPEN x_sync_contact_cur FOR
3700: SELECT
3701: /*+ leading(dsi) USE_NL(OC R PP) */
3706: ,dsi.ROWID
3707: ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
3708: ,oc.CONTACT_NUMBER
3709: ,oc.JOB_TITLE
3710: FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_RELATIONSHIPS r,
3711: HZ_ORG_CONTACTS oc, HZ_PERSON_PROFILES pp
3712: WHERE oc.party_relationship_id = r.relationship_id
3713: AND dsi.record_id = oc.org_contact_id
3714: AND r.subject_id = pp.party_id
3728:
3729: PROCEDURE sync_all_contacts (
3730: p_operation IN VARCHAR2,
3731: p_bulk_sync_type IN VARCHAR2,
3732: p_sync_all_contact_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3733:
3734: l_limit NUMBER := 200;
3735: l_last_fetch BOOLEAN := FALSE;
3736: l_sql_errm VARCHAR2(2000);
3825: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3826: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3827: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3828: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3829: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='C' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3830: ELSE
3831: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3832: UPDATE hz_dqm_sync_interface
3833: SET error_data = l_sql_errm
3828: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3829: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='C' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3830: ELSE
3831: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3832: UPDATE hz_dqm_sync_interface
3833: SET error_data = l_sql_errm
3834: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3835: WHERE rowid = H_ROWID(l_err_index);
3836: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3833: SET error_data = l_sql_errm
3834: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3835: WHERE rowid = H_ROWID(l_err_index);
3836: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3837: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3838: 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);
3839: END IF;
3840: END IF;
3841: END LOOP;
3864: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3865: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3866: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3867: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3868: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='U' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3869: ELSE
3870: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3871: UPDATE hz_dqm_sync_interface
3872: SET error_data = l_sql_errm
3867: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3868: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='U' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3869: ELSE
3870: IF p_bulk_sync_type = 'DQM_SYNC' THEN
3871: UPDATE hz_dqm_sync_interface
3872: SET error_data = l_sql_errm
3873: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3874: WHERE rowid = H_ROWID(l_err_index);
3875: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3872: SET error_data = l_sql_errm
3873: ,staged_flag = decode (error_data, NULL, 'N', 'E')
3874: WHERE rowid = H_ROWID(l_err_index);
3875: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3876: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3877: 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);
3878: END IF;
3879: END IF;
3880: END LOOP;
3897: -- REPURI. Bug 4884742.
3898: -- Bulk Insert of Import Contacts into Shadow Sync Interface table
3899: -- if Shadow Staging has already run and completed successfully
3900: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3901: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3902: BEGIN
3903: -- REPURI. Bug 4968126.
3904: -- Using the Merge instead of Insert statement
3905: -- so that duplicate records dont get inserted.
3967: PROCEDURE open_sync_cpt_cursor (
3968: p_operation IN VARCHAR2,
3969: p_from_rec IN VARCHAR2,
3970: p_to_rec IN VARCHAR2,
3971: x_sync_cpt_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3972: BEGIN
3973: OPEN x_sync_cpt_cur FOR
3974: SELECT
3975: /*+ ORDERED USE_NL(cp) */
3990: ,translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
3991: ,cp.PHONE_LINE_TYPE
3992: ,cp.STATUS
3993: ,cp.CONTACT_POINT_PURPOSE
3994: FROM HZ_DQM_SYNC_INTERFACE dsi,HZ_CONTACT_POINTS cp
3995: WHERE dsi.record_id = cp.contact_point_id
3996: AND dsi.entity = 'CONTACT_POINTS'
3997: AND dsi.operation = p_operation
3998: AND dsi.staged_flag = 'N'
4003:
4004: PROCEDURE sync_all_contact_points (
4005: p_operation IN VARCHAR2,
4006: p_bulk_sync_type IN VARCHAR2,
4007: p_sync_all_cpt_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
4008:
4009: l_limit NUMBER := 200;
4010: l_last_fetch BOOLEAN := FALSE;
4011: l_sql_errm VARCHAR2(2000);
4134: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
4135: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
4136: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
4137: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4138: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='C' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4139: ELSE
4140: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4141: UPDATE hz_dqm_sync_interface
4142: SET error_data = l_sql_errm
4137: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4138: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='C' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4139: ELSE
4140: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4141: UPDATE hz_dqm_sync_interface
4142: SET error_data = l_sql_errm
4143: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4144: WHERE rowid = H_ROWID(l_err_index);
4145: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4142: SET error_data = l_sql_errm
4143: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4144: WHERE rowid = H_ROWID(l_err_index);
4145: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4146: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
4147: 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);
4148: END IF;
4149: END IF;
4150: END LOOP;
4179: l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
4180: l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
4181: IF (instr(l_sql_errm,'ORA-00001')>0) THEN
4182: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4183: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='U' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4184: ELSE
4185: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4186: UPDATE hz_dqm_sync_interface
4187: SET error_data = l_sql_errm
4182: log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4183: DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='U' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4184: ELSE
4185: IF p_bulk_sync_type = 'DQM_SYNC' THEN
4186: UPDATE hz_dqm_sync_interface
4187: SET error_data = l_sql_errm
4188: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4189: WHERE rowid = H_ROWID(l_err_index);
4190: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4187: SET error_data = l_sql_errm
4188: ,staged_flag = decode (error_data, NULL, 'N', 'E')
4189: WHERE rowid = H_ROWID(l_err_index);
4190: ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4191: -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
4192: 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);
4193: END IF;
4194: END IF;
4195: END LOOP;
4212: -- REPURI. Bug 4884742.
4213: -- Bulk Insert the Import of Contact Points into Shadow Sync Interface table
4214: -- if Shadow Staging has already run and completed successfully
4215: IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
4216: (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
4217: BEGIN
4218: -- REPURI. Bug 4968126.
4219: -- Using the Merge instead of Insert statement
4220: -- so that duplicate records dont get inserted.
4290: p_to_osr IN VARCHAR2,
4291: p_os IN VARCHAR2,
4292: p_party_type IN VARCHAR2,
4293: p_operation IN VARCHAR2,
4294: x_sync_party_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4295: BEGIN
4296: IF p_party_type = 'ORGANIZATION' THEN
4297: open x_sync_party_cur FOR
4298: SELECT p.PARTY_ID, p.STATUS, p.ROWID
4392: p_from_osr IN VARCHAR2,
4393: p_to_osr IN VARCHAR2,
4394: p_os IN VARCHAR2,
4395: p_operation IN VARCHAR2,
4396: x_sync_party_site_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4397: BEGIN
4398: OPEN x_sync_party_site_cur FOR
4399: SELECT /*+ ORDERED USE_NL(ps l) */
4400: ps.PARTY_SITE_ID
4436: p_from_osr IN VARCHAR2,
4437: p_to_osr IN VARCHAR2,
4438: p_os IN VARCHAR2,
4439: p_operation IN VARCHAR2,
4440: x_sync_contact_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4441: BEGIN
4442: OPEN x_sync_contact_cur FOR
4443: SELECT
4444: /*+ ORDERED USE_NL(R OC PP)*/
4508: p_from_osr IN VARCHAR2,
4509: p_to_osr IN VARCHAR2,
4510: p_os IN VARCHAR2,
4511: p_operation IN VARCHAR2,
4512: x_sync_cpt_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4513: BEGIN
4514: OPEN x_sync_cpt_cur FOR
4515: SELECT
4516: /*+ ORDERED USE_NL(cp) */