[Home] [Help]
288: FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', nullif(p_attr20, p_gmiss_char));
289:
290: IF (FND_FLEX_DESCVAL.validate_desccols(
291: 'AR',
292: 'HZ_PARTY_SITES',
293: 'V',
294: p_validation_date)) THEN
295: return 'Y';
296: ELSE
475: and owner_table_id is not null
476: and flex_val_error is not null -- e6
477: and createdby_error is not null -- e6
478: ) then
479: into hz_party_sites (
480: actual_content_source,
481: party_site_name,
482: request_id,
483: created_by,
592: :6,
593: hz_orig_system_ref_s.nextval,
594: site_orig_system,
595: site_orig_system_reference,
596: ''HZ_PARTY_SITES'',
597: party_site_id,
598: ''A'',
599: :5,
600: 1,
1044: select addr_sg.party_id,
1045: hz_ps.party_site_id
1046: from HZ_IMP_TMP_ERRORS err_table,
1047: hz_imp_addresses_sg addr_sg,
1048: hz_party_sites hz_ps
1049: where err_table.request_id = :request_id
1050: and interface_table_name = ''HZ_IMP_ADDRESSES_INT''
1051: and addr_sg.batch_id = :batch_id
1052: and addr_sg.batch_mode_flag = :batch_mode_flag
1075: postal_code ) =
1076: ( select address1, address2, address3, address4,
1077: country, county, city, state, province,
1078: postal_code
1079: from hz_party_sites hz_ps,
1080: hz_locations hz_loc
1081: where hz_ps.location_id = hz_loc.location_id
1082: and hz_ps.party_site_id = l_error_site_id(i)
1083: union -- nullify if no next available address
1093: program_update_date = P_DML_RECORD.SYSDATE
1094: where hz_pty.party_id = l_error_party_id(i);
1095:
1096: forall i in 1..l_error_party_id.count
1097: update hz_party_sites
1098: set identifying_address_flag = 'Y',
1099: object_version_number = object_version_number + 1,
1100: last_update_date = P_DML_RECORD.SYSDATE,
1101: last_updated_by = P_DML_RECORD.USER_ID,
1159: ) =
1160: ( select address1, address2, address3, address4,
1161: country, county, city, state, province,
1162: postal_code
1163: from hz_party_sites hz_ps,
1164: hz_locations hz_loc
1165: where hz_ps.location_id = hz_loc.location_id
1166: and hz_ps.party_site_id = l_update_site_id(i)
1167: ),
1327: site_int.ROWID,
1328: hz_ps.location_id,
1329: hr_locations_s.NextVal,
1330: site_sg.party_site_id,
1331: hz_party_sites_s.NextVal,
1332: site_int.party_site_number,
1333: site_int.party_site_name,
1334: site_sg.party_id,
1335: site_int.site_orig_system,
1478: from FND_LANGUAGES
1479: where installed_flag in (''B'', ''I'')
1480: ) fnd_lang,
1481: fnd_timezones_b timezone,
1482: hz_party_sites hz_ps,
1483: hz_locations hz_loc,
1484: HZ_ORIG_SYS_REFERENCES mosr,
1485: HZ_ORIG_SYSTEMS_B hos,
1486: hz_party_sites hz_ps1,
1482: hz_party_sites hz_ps,
1483: hz_locations hz_loc,
1484: HZ_ORIG_SYS_REFERENCES mosr,
1485: HZ_ORIG_SYSTEMS_B hos,
1486: hz_party_sites hz_ps1,
1487: HZ_ORIG_SYS_REFERENCES mosr2,
1488: fnd_lookup_values createdby_l
1489:
1490: WHERE mosr.orig_system (+) = site_sg.party_orig_system
1513: AND hz_ps1.identifying_address_flag(+) =''Y''
1514: AND mosr2.orig_system (+) = site_sg.site_orig_system
1515: AND mosr2.orig_system_reference (+) = site_sg.site_orig_system_reference
1516: AND mosr2.status (+) = ''A''
1517: AND mosr2.owner_table_name (+) = ''HZ_PARTY_SITES''
1518: AND mosr2.owner_table_id (+) = site_sg.party_site_id
1519: AND createdby_l.lookup_code (+) = site_int.created_by_module
1520: AND createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1521: AND createdby_l.language (+) = userenv(''LANG'')
1558: site_int.ROWID,
1559: hz_ps.location_id,
1560: hr_locations_s.NextVal,
1561: site_sg.party_site_id,
1562: hz_party_sites_s.NextVal,
1563: site_int.party_site_number,
1564: site_int.party_site_name,
1565: site_sg.party_id,
1566: site_int.site_orig_system,
1709: from FND_LANGUAGES
1710: where installed_flag in (''B'', ''I'')
1711: ) fnd_lang,
1712: fnd_timezones_b timezone,
1713: hz_party_sites hz_ps,
1714: hz_locations hz_loc,
1715: HZ_ORIG_SYS_REFERENCES mosr,
1716: HZ_ORIG_SYSTEMS_B hos,
1717: hz_party_sites hz_ps1,
1713: hz_party_sites hz_ps,
1714: hz_locations hz_loc,
1715: HZ_ORIG_SYS_REFERENCES mosr,
1716: HZ_ORIG_SYSTEMS_B hos,
1717: hz_party_sites hz_ps1,
1718: HZ_ORIG_SYS_REFERENCES mosr2,
1719: fnd_lookup_values createdby_l
1720: WHERE mosr.orig_system (+) = site_sg.party_orig_system
1721: AND mosr.orig_system_reference (+) = site_sg.party_orig_system_reference
1741: AND hz_ps1.status(+) = ''A''
1742: AND hz_ps1.identifying_address_flag(+) =''Y''AND mosr2.orig_system (+) = site_sg.site_orig_system
1743: AND mosr2.orig_system_reference (+) = site_sg.site_orig_system_reference
1744: AND mosr2.status (+) = ''A''
1745: AND mosr2.owner_table_name (+) = ''HZ_PARTY_SITES''
1746: AND mosr2.owner_table_id (+) = site_sg.party_site_id
1747: AND createdby_l.lookup_code (+) = site_int.created_by_module
1748: AND createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1749: AND createdby_l.language (+) = userenv(''LANG'')
1890: FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', l_attr20(i));
1891:
1892: IF (NOT FND_FLEX_DESCVAL.validate_desccols(
1893: 'AR',
1894: 'HZ_PARTY_SITES',
1895: 'V',
1896: p_validation_date)) THEN
1897: l_flex_val_errors(i) := 1;
1898: END IF;
1929: FOR i IN 1..l_site_id.count LOOP
1930: l_dss_security_errors(i) :=
1931: hz_dss_util_pub.test_instance(
1932: p_operation_code => 'UPDATE',
1933: p_db_object_name => 'HZ_PARTY_SITES',
1934: p_instance_pk1_value => l_site_id(i),
1935: p_user_name => fnd_global.user_name,
1936: x_return_status => dss_return_status,
1937: x_msg_count => dss_msg_count,
2217: /* create new party site and invalidate old one if it is necessary to move */
2218: /* condition: if address-related column change and correct_move_indicator='N'/null */
2219: begin
2220: forall j in 1..l_site_id.count save exceptions
2221: insert into hz_party_sites
2222: ( PARTY_SITE_ID,
2223: PARTY_ID,
2224: LOCATION_ID,
2225: PARTY_SITE_NUMBER,
2307: and l_third_party_update_error(j) IS NOT NULL /* bug 4079902 */
2308: and l_createdby_errors(j) IS NOT NULL);
2309: EXCEPTION
2310: WHEN OTHERS THEN
2311: ----dbms_output.put_line('Other exceptions hz_party_sites:' || SQLERRM);
2312: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2313: hz_utility_v2pub.debug(p_message=>'Other exceptions hz_party_sites:',
2314: p_prefix=>'ERROR',
2315: p_msg_level=>fnd_log.level_error);
2309: EXCEPTION
2310: WHEN OTHERS THEN
2311: ----dbms_output.put_line('Other exceptions hz_party_sites:' || SQLERRM);
2312: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2313: hz_utility_v2pub.debug(p_message=>'Other exceptions hz_party_sites:',
2314: p_prefix=>'ERROR',
2315: p_msg_level=>fnd_log.level_error);
2316: hz_utility_v2pub.debug(p_message=>SQLERRM,
2317: p_prefix=>'ERROR',
2439: where location_id = l_location_id(j)
2440: and l_corr_upd_count(j) = 1;
2441:
2442: forall j in 1..l_site_id.count
2443: update hz_party_sites
2444: set ATTRIBUTE_CATEGORY = DECODE(l_attr_category(j), NULL, ATTRIBUTE_CATEGORY, P_DML_RECORD.GMISS_CHAR, NULL, l_attr_category(j)),
2445: ATTRIBUTE1 = DECODE(l_attr1(j), NULL, attribute1, P_DML_RECORD.GMISS_CHAR, NULL, l_attr1(j)),
2446: ATTRIBUTE2 = DECODE(l_attr2(j), NULL, attribute2, P_DML_RECORD.GMISS_CHAR, NULL, l_attr2(j)),
2447: ATTRIBUTE3 = DECODE(l_attr3(j), NULL, attribute3, P_DML_RECORD.GMISS_CHAR, NULL, l_attr3(j)),
2461: ATTRIBUTE17 = DECODE(l_attr17(j), NULL, attribute17, P_DML_RECORD.GMISS_CHAR, NULL, l_attr17(j)),
2462: ATTRIBUTE18 = DECODE(l_attr18(j), NULL, attribute18, P_DML_RECORD.GMISS_CHAR, NULL, l_attr18(j)),
2463: ATTRIBUTE19 = DECODE(l_attr19(j), NULL, attribute19, P_DML_RECORD.GMISS_CHAR, NULL, l_attr19(j)),
2464: ATTRIBUTE20 = DECODE(l_attr20(j), NULL, attribute20, P_DML_RECORD.GMISS_CHAR, NULL, l_attr20(j))
2465: where hz_party_sites.party_site_id = l_site_id(j)
2466: and l_corr_upd_count(j) = 1;
2467:
2468:
2469: /* insert into hz_location_profiles with user data if maintaining old record */
2759: object_version_number = object_version_number + 1
2760: where status = 'A'
2761: and orig_system = l_site_orig_system(j)
2762: and orig_system_reference = l_old_site_osr(j)
2763: and owner_table_name = 'HZ_PARTY_SITES'
2764: and ( l_move_count(j) = 1 or
2765: (l_corr_upd_count(j) = 1 and
2766: l_site_orig_system_reference(j) <> l_old_site_osr(j)));
2767:
2840: object_version_number = object_version_number + 1
2841: where status = 'A'
2842: and orig_system = l_site_orig_system(j)
2843: and orig_system_reference = l_site_orig_system_reference(j)
2844: and owner_table_name = 'HZ_PARTY_SITES'
2845: and l_NEW_OSR_EXISTS(j) is not null
2846: and (l_move_count(j) = 1 or l_corr_upd_count(j) = 1)
2847: ;
2848: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2883: (select
2884: HZ_ORIG_SYSTEM_REF_S.NEXTVAL,
2885: l_site_orig_system(j),
2886: l_site_orig_system_reference(j),
2887: 'HZ_PARTY_SITES',
2888: decode(l_move_count(j),1,l_new_site_id(j),l_site_id(j)),
2889: 'A',
2890: P_DML_RECORD.SYSDATE,
2891: P_DML_RECORD.USER_ID,
2915: the l_owning_party_id(j) will be NULL
2916: Add logic to handle this case */
2917:
2918: forall j in 1..l_site_id.count
2919: update hz_party_sites hz_ps
2920: set program_update_date = P_DML_RECORD.SYSDATE,
2921: status = 'I',
2922: last_update_date = P_DML_RECORD.SYSDATE,
2923: last_updated_by = P_DML_RECORD.USER_ID,
3249: FND_GLOBAL.User_ID CREATED_BY,
3250: SYSDATE LAST_UPDATE_DATE,
3251: FND_GLOBAL.User_ID LAST_UPDATED_BY,
3252: FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
3253: FROM HZ_PARTY_SITES ps, HZ_IMP_ADDRESSES_SG pssg,HZ_LOCATIONS loc, --4742586
3254: HZ_IMP_ADDRESSES_INT psint
3255: WHERE loc.request_id = p_request_id --Bug No.4956874.SQLID:14455142
3256: AND ps.party_site_id = pssg.party_site_id -- Bug 5210879.
3257: AND loc.location_id = ps.location_id --4742586