[Home] [Help]
1022: WHERE rn>1;
1023:
1024: CURSOR c_parties_d IS
1025: SELECT pint.party_orig_system, pint.party_orig_system_reference,psg.party_id
1026: FROM hz_imp_parties_int pint, hz_imp_parties_sg psg
1027: WHERE pint.batch_id=P_BATCH_ID
1028: AND pint.party_orig_system=P_PARTY_OS
1029: AND pint.interface_status='D'
1030: AND Pint.rowid=psg.int_row_id
1054: EXIT;
1055: END IF;
1056:
1057: FORALL i in l_rowid.FIRST..l_rowid.LAST
1058: UPDATE HZ_IMP_PARTIES_INT party
1059: SET INTERFACE_STATUS = 'D'
1060: WHERE rowid=l_rowid(i);
1061:
1062: IF l_last_fetch = TRUE THEN
1208: CURSOR c_displayed_duns(p_batch_id number, p_batch_mode_flag varchar2) IS
1209: select /*+ parallel(pi) leading (pi) use_nl(ps) use_nl(osr) */
1210: ps.party_id, osr.owner_table_id displayed_duns_party_id
1211: from hz_orig_sys_references osr,
1212: hz_imp_parties_int pi,
1213: hz_imp_parties_sg ps
1214: where osr.owner_table_name = 'HZ_PARTIES'
1215: and osr.orig_system = 'DNB'
1216: and osr.orig_system_reference = pi.displayed_duns
1295: IF P_RERUN_FLAG <> 'N' THEN
1296:
1297: IF P_END_OF_DL_FLAG = 'Y' THEN
1298:
1299: update HZ_IMP_PARTIES_INT
1300: set error_id = null,
1301: interface_status = null
1302: where BATCH_ID = P_BATCH_ID
1303: and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1378: /* Parties are loaded in stage 2. So if work unit is
1379: completed for either stage 2 or stage 3, it means the
1380: parties should have been loaded. For other entities,
1381: only need to check stage 3. */
1382: update HZ_IMP_PARTIES_INT
1383: set error_id = null, interface_status = null
1384: where batch_id = P_BATCH_ID
1385: and party_orig_system = P_ORIG_SYSTEM
1386: and interface_status = 'C'
1898: into hz_imp_errors (
1899: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1900: token1_name, token1_value, token2_name, token2_value)
1901: values (
1902: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1903: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FISCAL_YEAREND_MONTH',
1904: 'LOOKUP_TYPE', 'MONTH')
1905: when (E2_FLAG is null) then
1906: into hz_imp_errors (
1906: into hz_imp_errors (
1907: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1908: token1_name, token1_value, token2_name, token2_value)
1909: values (
1910: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1911: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LEGAL_STATUS', 'LOOKUP_TYPE',
1912: 'LEGAL_STATUS')
1913: when (E3_FLAG is null) then
1914: into hz_imp_errors (
1914: into hz_imp_errors (
1915: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1916: token1_name, token1_value, token2_name, token2_value)
1917: values (
1918: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1919: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LOCAL_BUS_IDEN_TYPE',
1920: 'LOOKUP_TYPE', 'LOCAL_BUS_IDEN_TYPE')
1921: when (E4_FLAG is null) then
1922: into hz_imp_errors (
1922: into hz_imp_errors (
1923: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1924: token1_name, token1_value, token2_name, token2_value)
1925: values (
1926: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1927: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'REGISTRATION_TYPE',
1928: 'LOOKUP_TYPE', 'REGISTRATION_TYPE')
1929: when (E5_FLAG is null) then
1930: into hz_imp_errors (
1930: into hz_imp_errors (
1931: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1932: token1_name, token1_value, token2_name, token2_value)
1933: values (
1934: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1935: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'HQ_BRANCH_IND', 'LOOKUP_TYPE',
1936: 'HQ_BRANCH_IND')
1937: when (E6_FLAG is null) then
1938: into hz_imp_errors (
1938: into hz_imp_errors (
1939: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1940: token1_name, token1_value, token2_name, token2_value)
1941: values (
1942: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1943: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'MINORITY_OWNED_IND',
1944: 'LOOKUP_TYPE', 'YES/NO')
1945: when (E7_FLAG is null) then
1946: into hz_imp_errors (
1946: into hz_imp_errors (
1947: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1948: token1_name, token1_value, token2_name, token2_value)
1949: values (
1950: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1951: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'GSA_INDICATOR_FLAG', 'LOOKUP_TYPE',
1952: 'YES/NO')
1953: when (E8_FLAG is null) then
1954: into hz_imp_errors (
1954: into hz_imp_errors (
1955: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1956: token1_name, token1_value, token2_name, token2_value)
1957: values (
1958: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1959: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'IMPORT_IND', 'LOOKUP_TYPE', 'YES/NO')
1960: when (E9_FLAG is null) then
1961: into hz_imp_errors (
1962: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1961: into hz_imp_errors (
1962: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1963: token1_name, token1_value, token2_name, token2_value)
1964: values (
1965: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1966: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EXPORT_IND', 'LOOKUP_TYPE', 'YES/NO')
1967: when (E10_FLAG is null) then
1968: into hz_imp_errors (
1969: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1968: into hz_imp_errors (
1969: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1970: token1_name, token1_value, token2_name, token2_value)
1971: values (
1972: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1973: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'BRANCH_FLAG', 'LOOKUP_TYPE',
1974: 'YES/NO')
1975: when (E11_FLAG is null) then
1976: into hz_imp_errors (
1976: into hz_imp_errors (
1977: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1978: token1_name, token1_value, token2_name, token2_value)
1979: values (
1980: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1981: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DISADV_8A_IND', 'LOOKUP_TYPE',
1982: 'YES/NO')
1983: when (E12_FLAG is null) then
1984: into hz_imp_errors (
1984: into hz_imp_errors (
1985: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1986: token1_name, token1_value, token2_name, token2_value)
1987: values (
1988: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1989: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LABOR_SURPLUS_IND',
1990: 'LOOKUP_TYPE', 'YES/NO')
1991: when (E13_FLAG is null) then
1992: into hz_imp_errors (
1992: into hz_imp_errors (
1993: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1994: token1_name, token1_value, token2_name, token2_value)
1995: values (
1996: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1997: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'OOB_IND', 'LOOKUP_TYPE', 'YES/NO')
1998: when (E14_FLAG is null) then
1999: into hz_imp_errors (
2000: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1999: into hz_imp_errors (
2000: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2001: token1_name, token1_value, token2_name, token2_value)
2002: values (
2003: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2004: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PARENT_SUB_IND', 'LOOKUP_TYPE',
2005: 'YES/NO')
2006: when (E15_FLAG is null) then
2007: into hz_imp_errors (
2007: into hz_imp_errors (
2008: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2009: token1_name, token1_value, token2_name, token2_value)
2010: values (
2011: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2012: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PUBLIC_PRIVATE_OWNERSHIP_FLAG',
2013: 'LOOKUP_TYPE', 'YES/NO')
2014: when (E16_FLAG is null) then
2015: into hz_imp_errors (
2015: into hz_imp_errors (
2016: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2017: token1_name, token1_value, token2_name, token2_value)
2018: values (
2019: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2020: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SMALL_BUS_IND', 'LOOKUP_TYPE',
2021: 'YES/NO')
2022: when (E17_FLAG is null) then
2023: into hz_imp_errors (
2023: into hz_imp_errors (
2024: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2025: token1_name, token1_value, token2_name, token2_value)
2026: values (
2027: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2028: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMP_EST_IND',
2029: 'LOOKUP_TYPE', 'TOTAL_EMP_EST_IND')
2030: when (E18_FLAG is null) then
2031: into hz_imp_errors (
2031: into hz_imp_errors (
2032: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2033: token1_name, token1_value, token2_name, token2_value)
2034: values (
2035: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2036: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMP_MIN_IND',
2037: 'LOOKUP_TYPE', 'TOTAL_EMP_MIN_IND')
2038: when (E19_FLAG is null) then
2039: into hz_imp_errors (
2039: into hz_imp_errors (
2040: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2041: token1_name, token1_value, token2_name, token2_value)
2042: values (
2043: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2044: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMPLOYEES_IND', 'LOOKUP_TYPE',
2045: 'TOTAL_EMPLOYEES_INDICATOR')
2046: when (E20_FLAG is null) then
2047: into hz_imp_errors (
2047: into hz_imp_errors (
2048: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2049: token1_name, token1_value, token2_name, token2_value)
2050: values (
2051: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2052: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'WOMAN_OWNED_IND', 'LOOKUP_TYPE',
2053: 'YES/NO')
2054: when (E21_FLAG is null) then
2055: into hz_imp_errors (
2055: into hz_imp_errors (
2056: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2057: token1_name, token1_value, token2_name, token2_value)
2058: values (
2059: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2060: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMP_AT_PRIMARY_ADR_EST_IND',
2061: 'LOOKUP_TYPE', 'EMP_AT_PRIMARY_ADR_EST_IND')
2062: when (E22_FLAG is null) then
2063: into hz_imp_errors (
2063: into hz_imp_errors (
2064: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2065: token1_name, token1_value, token2_name, token2_value)
2066: values (
2067: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2068: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMP_AT_PRIMARY_ADR_MIN_IND',
2069: 'LOOKUP_TYPE', 'EMP_AT_PRIMARY_ADR_MIN_IND')
2070: when (E23_FLAG is null) then
2071: into hz_imp_errors (
2071: into hz_imp_errors (
2072: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2073: token1_name, token1_value, token2_name, token2_value)
2074: values (
2075: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2076: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'MARITAL_STATUS', 'LOOKUP_TYPE',
2077: 'MARITAL_STATUS')
2078: when (E24_FLAG is null) then
2079: into hz_imp_errors (
2079: into hz_imp_errors (
2080: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2081: token1_name, token1_value, token2_name, token2_value)
2082: values (
2083: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2084: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PERSON_PRE_NAME_ADJUNCT',
2085: 'LOOKUP_TYPE', 'CONTACT_TITLE')
2086: when (E25_FLAG is null) then
2087: into hz_imp_errors (
2086: when (E25_FLAG is null) then
2087: into hz_imp_errors (
2088: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2089: values (
2090: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2091: 'HZ_IMP_DECEASED_FLAG_ERROR')
2092: when (E26_FLAG is null) then
2093: into hz_imp_errors (
2094: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2093: into hz_imp_errors (
2094: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2095: token1_name, token1_value, token2_name, token2_value)
2096: values (
2097: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2098: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'HEAD_OF_HOUSEHOLD_FLAG',
2099: 'LOOKUP_TYPE', 'YES/NO')
2100: when (E27_FLAG is null) then
2101: into hz_imp_errors (
2101: into hz_imp_errors (
2102: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2103: token1_name, token1_value, token2_name, token2_value)
2104: values (
2105: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2106: 'HZ_API_DATE_GREATER', 'DATE2', 'SYSDATE', 'DATE1', 'DATE_OF_BIRTH')
2107: when (E28_FLAG is null) then
2108: into hz_imp_errors (
2109: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2108: into hz_imp_errors (
2109: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2110: token1_name, token1_value, token2_name, token2_value)
2111: values (
2112: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2113: 'HZ_API_DATE_GREATER', 'DATE2', 'SYSDATE', 'DATE1', 'DATE_OF_DEATH')
2114: when (E29_FLAG is null) then
2115: into hz_imp_errors (
2116: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2115: into hz_imp_errors (
2116: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2117: token1_name, token1_value, token2_name, token2_value)
2118: values (
2119: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2120: 'HZ_API_DATE_GREATER', 'DATE2', 'DATE_OF_DEATH', 'DATE1', 'DATE_OF_BIRTH')
2121: when (E30_FLAG is null) then
2122: into hz_imp_errors (
2123: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2121: when (E30_FLAG is null) then
2122: into hz_imp_errors (
2123: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2124: values (
2125: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2126: 'HZ_IMP_PARTY_TYPE_ERROR')
2127: when (E31_FLAG is null) then
2128: into hz_imp_errors (
2129: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2128: into hz_imp_errors (
2129: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2130: token1_name, token1_value, token2_name, token2_value)
2131: values (
2132: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2133: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RENT_OWN_IND', 'LOOKUP_TYPE',
2134: 'OWN_RENT_IND')
2135: when (E32_FLAG is null) then
2136: into hz_imp_errors (
2136: into hz_imp_errors (
2137: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2138: token1_name, token1_value)
2139: values (
2140: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2141: 'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_PARTIES')
2142: when (E33_FLAG is null) then
2143: into hz_imp_errors (
2144: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2142: when (E33_FLAG is null) then
2143: into hz_imp_errors (
2144: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2145: values (
2146: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2147: 'HZ_IMP_PARTY_NAME_ERROR')
2148: when (E34_FLAG='P') then
2149: into hz_imp_errors (
2150: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2149: into hz_imp_errors (
2150: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2151: token1_name, token1_value)
2152: values (
2153: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2154: 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_person_err)
2155: when (E34_FLAG='O') then
2156: into hz_imp_errors (
2157: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2156: into hz_imp_errors (
2157: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2158: token1_name, token1_value)
2159: values (
2160: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2161: 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_org_err)
2162: when (E34_FLAG is null) then
2163: into hz_imp_errors (
2164: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2163: into hz_imp_errors (
2164: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2165: token1_name, token1_value)
2166: values (
2167: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2168: 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_others_err)
2169: when (E35_FLAG is null) then
2170: into hz_imp_errors (
2171: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2170: into hz_imp_errors (
2171: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2172: token1_name, token1_value, token2_name, token2_value)
2173: values (
2174: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2175: 'HZ_API_NO_RECORD', 'RECORD', 'PARTY', 'VALUE', ' ')
2176: -- Bug 4310257
2177: when (E36_FLAG is null) then
2178: into hz_imp_errors (
2178: into hz_imp_errors (
2179: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2180: token1_name, token1_value, token2_name, token2_value)
2181: values (
2182: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2183: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'GENDER', 'LOOKUP_TYPE',
2184: 'HZ_GENDER')
2185: when (E37_FLAG is null) then
2186: into hz_imp_errors (
2186: into hz_imp_errors (
2187: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2188: token1_name, token1_value, token2_name, token2_value)
2189: values (
2190: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2191: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PERSON_IDEN_TYPE', 'LOOKUP_TYPE',
2192: 'HZ_PERSON_IDEN_TYPE')
2193: when (E38_FLAG is null) then
2194: into hz_imp_errors (
2194: into hz_imp_errors (
2195: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2196: token1_name, token1_value, token2_name, token2_value)
2197: values (
2198: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2199: 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2200: 'HZ_CREATED_BY_MODULES')
2201: when (E39_FLAG is null) then
2202: into hz_imp_errors (
2201: when (E39_FLAG is null) then
2202: into hz_imp_errors (
2203: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2204: values (
2205: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2206: 'HZ_NO_CHANGE_PARTY_NAME')
2207: when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2208: into hz_imp_errors (
2209: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2208: into hz_imp_errors (
2209: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2210: token1_name, token1_value, token2_name, token2_value)
2211: values (
2212: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2213: 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTIES_U1', 'ENTITY', 'HZ_PARTIES')
2214: when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
2215: into hz_imp_errors (
2216: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2215: into hz_imp_errors (
2216: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2217: token1_name, token1_value, token2_name, token2_value)
2218: values (
2219: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2220: 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTIES_U2', 'ENTITY', 'HZ_PARTIES')
2221: when (ACTION_MISMATCH_FLAG is null) then
2222: into hz_imp_errors (
2223: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2221: when (ACTION_MISMATCH_FLAG is null) then
2222: into hz_imp_errors (
2223: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2224: values (
2225: creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2226: 'HZ_IMP_ACTION_MISMATCH')
2227: select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
2228: e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
2229: E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
2234: E34_FLAG, E35_FLAG, E36_FLAG, E37_FLAG, E38_FLAG, E39_FLAG,
2235: DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG,
2236: INT.DATE_OF_BIRTH, INT.DATE_OF_DEATH, FND_GLOBAL.USER_NAME
2237: from hz_imp_tmp_errors e,
2238: HZ_IMP_PARTIES_INT int
2239: where e.batch_id = P_BATCH_ID
2240: and e.request_id = P_REQUEST_ID
2241: and e.int_row_id = int.rowid
2242: and e.interface_table_name = 'HZ_IMP_PARTIES_INT';
2238: HZ_IMP_PARTIES_INT int
2239: where e.batch_id = P_BATCH_ID
2240: and e.request_id = P_REQUEST_ID
2241: and e.int_row_id = int.rowid
2242: and e.interface_table_name = 'HZ_IMP_PARTIES_INT';
2243:
2244: insert all
2245: when (E1_FLAG is null) then
2246: into hz_imp_errors (
3224: FETCH c_err BULK COLLECT INTO l_row_id, l_table_name, l_error_id;
3225: CLOSE c_err;
3226:
3227: ForAll i in 1..l_row_id.count
3228: update hz_imp_parties_int
3229: set interface_status = 'E',
3230: error_id = l_error_id(i)
3231: where rowid = l_row_id(i)
3232: and l_table_name(i) = 'HZ_IMP_PARTIES_INT';
3228: update hz_imp_parties_int
3229: set interface_status = 'E',
3230: error_id = l_error_id(i)
3231: where rowid = l_row_id(i)
3232: and l_table_name(i) = 'HZ_IMP_PARTIES_INT';
3233:
3234: ForAll i in 1..l_row_id.count
3235: update hz_imp_addresses_int
3236: set interface_status = 'E',
3630: l_return_status VARCHAR2(1);
3631:
3632: CURSOR c_hr_data(p_batch_id number) IS
3633: SELECT 'Y'
3634: FROM hz_imp_parties_int
3635: WHERE batch_id = p_batch_id
3636: AND party_orig_system = 'DEFAULT'
3637: AND interface_status is null
3638: AND party_orig_system_reference like 'PER%'
3926: CHECK_INVALID_PARTY(P_BATCH_ID,P_REQUEST_ID,l_user_id,l_last_update_login,P_PROGRAM_ID,
3927: P_PROGRAM_APPLICATION_ID,l_return_status);
3928:
3929: IF l_return_status = 'E' THEN
3930: l_error_message := 'Invalid party_id(s) is found in hz_imp_parties_int that does not exist in hz_parties. Please correct the party_id and resubmit the batch for import. Please check error report for invalid party_id(s).';
3931: FND_FILE.PUT_LINE(FND_FILE.LOG, 'log:' || l_error_message);
3932: update_import_status(P_BATCH_ID, 'ERROR');
3933:
3934: errbuf := l_error_message;
3940: -- Analyze interface tables
3941: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Analyze all interface tables ' ||
3942: to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3943:
3944: fnd_stats.gather_table_stats('AR', 'HZ_IMP_PARTIES_INT');
3945: fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSES_INT');
3946: fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSUSES_INT');
3947: fnd_stats.gather_table_stats('AR', 'HZ_IMP_CLASSIFICS_INT');
3948: fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTPTS_INT');
4429: )/:3
4430: ) wu
4431: from
4432: ( -- Party
4433: select /*+ index_ffs(a,hz_imp_parties_int_u1) parallel_index(a)*/
4434: party_orig_system_reference
4435: from hz_imp_parties_int a
4436: where party_orig_system = :2
4437: and batch_id=:1
4431: from
4432: ( -- Party
4433: select /*+ index_ffs(a,hz_imp_parties_int_u1) parallel_index(a)*/
4434: party_orig_system_reference
4435: from hz_imp_parties_int a
4436: where party_orig_system = :2
4437: and batch_id=:1
4438: union all -- Party Site
4439: select /*+ index_ffs(a,hz_imp_addresses_int_n1) parallel_index(a)*/
4526: )/:3
4527: ) wu
4528: from
4529: ( -- Party
4530: select /*+ index(a,hz_imp_parties_int_u1) */
4531: party_orig_system_reference
4532: from hz_imp_parties_int a
4533: where party_orig_system = :2
4534: and batch_id=:1
4528: from
4529: ( -- Party
4530: select /*+ index(a,hz_imp_parties_int_u1) */
4531: party_orig_system_reference
4532: from hz_imp_parties_int a
4533: where party_orig_system = :2
4534: and batch_id=:1
4535: union all -- Party Site
4536: select /*+ index(a,hz_imp_addresses_int_n1) */
4633:
4634: l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
4635:
4636: select num_rows into party_count
4637: from sys.dba_tables where upper(table_name) = 'HZ_IMP_PARTIES_INT'
4638: and owner = l_schema;
4639:
4640: if(est_count is not null and party_count is not null) then
4641: if party_count>(est_count*0.15) and party_count>50000 then
4831: l_sysdate DATE := sysdate;
4832:
4833: CURSOR c_invalid_party(L_BATCH_ID NUMBER) IS
4834: SELECT rowid,party_id
4835: FROM hz_imp_parties_int hip
4836: WHERE
4837: batch_id=L_BATCH_ID
4838: AND hip.party_id is not null
4839: AND not exists
4859: program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
4860: token1_name, token1_value)
4861: values (
4862: l_sysdate, P_USER_ID, l_sysdate, P_USER_ID, P_LAST_UPDATE_LOGIN, P_PROGRAM_APPLICATION_ID,
4863: P_PROGRAM_ID, l_sysdate, HZ_IMP_ERRORS_S.NextVal, P_BATCH_ID, P_REQUEST_ID, 'HZ_IMP_PARTIES_INT',
4864: 'HZ_IMP_INVALID_PARTY_ID','PARTY_ID',l_party_id(i))
4865: RETURNING error_id BULK COLLECT INTO l_error_id;
4866:
4867:
4865: RETURNING error_id BULK COLLECT INTO l_error_id;
4866:
4867:
4868: FORALL i in l_row_id.first..l_row_id.last
4869: UPDATE HZ_IMP_PARTIES_INT
4870: SET INTERFACE_STATUS='E',
4871: ERROR_ID=l_error_id(i)
4872: WHERE ROWID=l_row_id(i);
4873: