[Home] [Help]
908: sic_code)';
909:
910: l_s2 varchar2(10000) := '
911: when (all_errors is not null and party_type = ''ORGANIZATION'') then -- o1
912: into hz_organization_profiles (
913: actual_content_source,
914: application_id,
915: content_source_type,
916: created_by,
1004: :5, -- program_application_id
1005: :6, -- program_id
1006: :3, -- sysdate
1007: :7, -- request_id
1008: hz_organization_profiles_s.nextval,
1009: party_id,
1010: organization_name,
1011: ceo_name,
1012: ceo_title,
1080: l_s3 varchar2(10000) := '
1081: when (all_errors is not null
1082: and l_content_source_type <> ''USER_ENTERED''
1083: and party_type = ''ORGANIZATION'') then
1084: into hz_organization_profiles ( -- o2
1085: actual_content_source,
1086: application_id,
1087: content_source_type,
1088: created_by,
1176: :5, -- program_application_id
1177: :6, -- program_id
1178: :3, -- sysdate
1179: :7, -- request_id
1180: hz_organization_profiles_s.nextval+1,
1181: party_id,
1182: organization_name,
1183: ceo_name,
1184: ceo_title,
1251: l_s4 varchar2(10000) := 'when (all_errors is not null
1252: and l_content_source_type <> ''USER_ENTERED''
1253: and party_type = ''ORGANIZATION''
1254: and :10 = ''Y'') then
1255: into hz_organization_profiles ( -- o3
1256: actual_content_source,
1257: application_id,
1258: content_source_type,
1259: created_by,
1347: :5, -- program_application_id
1348: :6, -- program_id
1349: :3, -- sysdate
1350: :7, -- request_id
1351: hz_organization_profiles_s.nextval+2,
1352: party_id,
1353: organization_name,
1354: ceo_name,
1355: ceo_title,
3227: ps.new_osr_exists_flag,
3228: ps.old_orig_system_reference
3229: FROM HZ_IMP_PARTIES_INT pi, HZ_IMP_PARTIES_SG ps, HZ_PARTIES p,
3230: HZ_PERSON_PROFILES pp,
3231: HZ_ORGANIZATION_PROFILES op,
3232: fnd_lookup_values party_type_l,
3233: fnd_lookup_values month_l,
3234: fnd_lookup_values legal_status_l,
3235: fnd_lookup_values local_bus_iden_type_l,
3438:
3439: l_dml_exception varchar2(1) := 'N';
3440:
3441: l_insert_org_profile varchar2(10000) :=
3442: 'insert into hz_organization_profiles op1
3443: (
3444: ORGANIZATION_PROFILE_ID,
3445: PARTY_ID,
3446: ORGANIZATION_NAME,
3520: DO_NOT_CONFUSE_WITH,
3521: ACTUAL_CONTENT_SOURCE
3522: )
3523: select
3524: hz_organization_profiles_s.nextval,
3525: PARTY_ID,
3526: decode(:l_organization_name, NULL, ORGANIZATION_NAME, :G_MISS_CHAR, ORGANIZATION_NAME,
3527: :l_organization_name),
3528: decode(:l_ceo_name, NULL, CEO_NAME, :G_MISS_CHAR, NULL,
3659: APPLICATION_ID,
3660: decode(:l_do_not_confuse_with, NULL, DO_NOT_CONFUSE_WITH, :G_MISS_CHAR, NULL,
3661: :l_do_not_confuse_with),
3662: ACTUAL_CONTENT_SOURCE
3663: from hz_organization_profiles where ';
3664:
3665: l_insert_per_profile varchar2(10000) :=
3666: 'insert into hz_person_profiles pp1
3667: (
3819: NULL, ''HZ_IMPORT'', :l_created_by_module))
3820: from hz_person_profiles where ';
3821:
3822: l_update_org_profile varchar2(10000) :=
3823: 'update hz_organization_profiles op1 set
3824: ORGANIZATION_NAME = DECODE(:l_organization_name,
3825: NULL, ORGANIZATION_NAME,
3826: :G_MISS_CHAR, ORGANIZATION_NAME,
3827: :l_organization_name),
4533: /*** Update HZ_PARTIES ***/
4534: /******************************/
4535:
4536: /* Loop to handle duplicate party_id or party_number exceptions. Only
4537: needed for hz_parties. When inserting into hz_organization_profiles or
4538: hz_person_profiles, l_num_row_processed is used to determine if an interface
4539: record was inserted successfully, which excludes problem with duplicate
4540: party_id as well. So this problem will not be reported again. Then when
4541: inserting into profiles table, whenever there is any exception, just
5092: p_msg_level=>fnd_log.level_statement);
5093: END IF;
5094:
5095: /********************************************/
5096: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
5097: /*** Mix and Match : disabled ***/
5098: /*** Version Profile: NEW_VERSION ***/
5099: /********************************************/
5100:
5180: p_msg_level=>fnd_log.level_statement);
5181: END IF;
5182:
5183: /*************************************************/
5184: /*** End date current HZ_ORGANIZATION_PROFILES ***/
5185: /*** Mix and Match : disabled ***/
5186: /*** Version Profile: NEW_VERSION ***/
5187: /*************************************************/
5188:
5188:
5189: -- End date current profile and save the profile ids.
5190: -- Then use the profile ids for copying values to new profile
5191: ForAll j in 1..l_party_orig_system_reference.count
5192: update hz_organization_profiles op1 set
5193: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
5194: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
5195: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
5196: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
5333: p_msg_level=>fnd_log.level_statement);
5334: END IF;
5335:
5336: /********************************************/
5337: /*** Update HZ_ORGANIZATION_PROFILES ***/
5338: /*** Mix and Match : disabled ***/
5339: /*** Version Profile: NO_VERSION ***/
5340: /********************************************/
5341:
5493: p_msg_level=>fnd_log.level_statement);
5494: END IF;
5495:
5496: /******************************************/
5497: /*** Update HZ_ORGANIZATION_PROFILES ***/
5498: /*** Mix and Match : disabled ***/
5499: /*** Version Profile: ONE_DAY_VERSION ***/
5500: /******************************************/
5501:
5584: l_content_source_type, l_actual_content_source;
5585:
5586:
5587: /********************************************/
5588: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
5589: /*** Mix and Match : disabled ***/
5590: /*** Version Profile: ONE_DAY_VERSION ***/
5591: /********************************************/
5592: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5674: l_content_source_type, l_actual_content_source;
5675:
5676:
5677: /********************************************/
5678: /*** End date HZ_ORGANIZATION_PROFILES ***/
5679: /*** Mix and Match : disabled ***/
5680: /*** Version Profile: ONE_DAY_VERSION ***/
5681: /********************************************/
5682:
5686: p_msg_level=>fnd_log.level_statement);
5687: END IF;
5688:
5689: ForAll j in 1..l_party_orig_system_reference.count
5690: update hz_organization_profiles op1 set
5691: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
5692: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
5693: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
5694: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
5890:
5891: IF l_org_mixnmatch_enabled = 'N' AND
5892: l_content_source_type <> 'USER_ENTERED' THEN
5893: /********************************************/
5894: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
5895: /*** Mix and Match : disabled ***/
5896: /********************************************/
5897: /* Insert new org profile if content source is 3rd party and
5898: 3rd party data not exists */
5897: /* Insert new org profile if content source is 3rd party and
5898: 3rd party data not exists */
5899:
5900: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5901: hz_utility_v2pub.debug(p_message=>'Insert into hz_organization_profiles',
5902: p_prefix =>l_debug_prefix,
5903: p_msg_level=>fnd_log.level_statement);
5904: END IF;
5905:
5903: p_msg_level=>fnd_log.level_statement);
5904: END IF;
5905:
5906: ForAll j in 1..l_party_orig_system_reference.count
5907: insert into hz_organization_profiles
5908: (
5909: ORGANIZATION_PROFILE_ID,
5910: PARTY_ID,
5911: ORGANIZATION_NAME,
5985: DO_NOT_CONFUSE_WITH,
5986: ACTUAL_CONTENT_SOURCE
5987: )
5988: select
5989: hz_organization_profiles_s.nextval,
5990: l_party_id(j), -- assume l_party_id cannot be null or G_MISS
5991: nvl(l_organization_name(j), l_party_name(j)),
5992: decode(l_ceo_name(j), P_DML_RECORD.GMISS_CHAR, NULL,
5993: l_ceo_name(j)),
6126: from dual
6127: where
6128: l_num_row_processed(j) = 1
6129: and l_party_type(j) = 'ORGANIZATION'
6130: and not exists (select 1 from hz_organization_profiles op2
6131: where op2.content_source_type = l_content_source_type
6132: and op2.actual_content_source = l_actual_content_source
6133: and op2.party_id = l_party_id(j));
6134:
6325:
6326: /* Handle records with no DNB data */
6327:
6328: /********************************************/
6329: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
6330: /*** Mix and Match : enabled ***/
6331: /*** Orig system : USER_ENTERED ***/
6332: /*** Has no DNB data ***/
6333: /*** Version Profile: NEW_VERSION ***/
6345: and :l_num_row_processed = 1
6346: and :l_party_type = ''ORGANIZATION''
6347: and CONTENT_SOURCE_TYPE = ''USER_ENTERED''
6348: and ACTUAL_CONTENT_SOURCE = ''SST''
6349: and not exists (select 1 from hz_organization_profiles op2
6350: where op2.content_source_type = ''USER_ENTERED''
6351: and op2.actual_content_source = ''USER_ENTERED''
6352: and :l_party_id = op2.party_id)'
6353: USING
6417: l_do_not_confuse_with(j), P_DML_RECORD.GMISS_CHAR, l_do_not_confuse_with(j),
6418: l_party_id(j), l_num_row_processed(j), l_party_type(j), l_party_id(j);
6419:
6420: /*************************************************/
6421: /*** End date current HZ_ORGANIZATION_PROFILES ***/
6422: /*** Mix and Match : enabled ***/
6423: /*** Orig system : USER_ENTERED ***/
6424: /*** Has no DNB data ***/
6425: /*** Version Profile: NEW_VERSION ***/
6433:
6434: -- End date current profile and save the profile ids.
6435: -- Then use the profile ids for copying values to new profile
6436: ForAll j in 1..l_party_orig_system_reference.count
6437: update hz_organization_profiles op1 set
6438: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
6439: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
6440: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
6441: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
6452: and l_num_row_processed(j) = 1
6453: and l_party_type(j) = 'ORGANIZATION'
6454: and CONTENT_SOURCE_TYPE = 'USER_ENTERED'
6455: and ACTUAL_CONTENT_SOURCE = 'SST'
6456: and not exists (select 1 from hz_organization_profiles op2
6457: where op2.content_source_type = 'USER_ENTERED'
6458: and op2.actual_content_source = 'USER_ENTERED'
6459: and op1.party_id = op2.party_id)
6460: and nvl(request_id, -1) <> P_DML_RECORD.REQUEST_ID;
6591: p_msg_level=>fnd_log.level_statement);
6592: END IF;
6593:
6594: /********************************************/
6595: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
6596: /*** Mix and Match : enabled ***/
6597: /*** Orig system : USER_ENTERED ***/
6598: /*** Has DNB data ***/
6599: /*** Version Profile: NEW_VERSION ***/
6675: l_do_not_confuse_with(j), P_DML_RECORD.GMISS_CHAR, l_do_not_confuse_with(j),
6676: l_party_id(j), l_num_row_processed(j), l_party_type(j);
6677:
6678: /*************************************************/
6679: /*** End date current HZ_ORGANIZATION_PROFILES ***/
6680: /*** Mix and Match : enabled ***/
6681: /*** Orig system : USER_ENTERED ***/
6682: /*** Has DNB data ***/
6683: /*** Version Profile: NEW_VERSION ***/
6685:
6686: -- End date current profile and save the profile ids.
6687: -- Then use the profile ids for copying values to new profile
6688: ForAll j in 1..l_party_orig_system_reference.count
6689: update hz_organization_profiles op1 set
6690: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
6691: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
6692: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
6693: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
6832: p_msg_level=>fnd_log.level_statement);
6833: END IF;
6834:
6835: /******************************************/
6836: /*** Update HZ_ORGANIZATION_PROFILES ***/
6837: /*** Mix and Match : enabled ***/
6838: /*** Orig system : USER_ENTERED ***/
6839: /*** Has no DNB data ***/
6840: /*** Version Profile: NO_VERSION ***/
6848: and :l_num_row_processed = 1
6849: and :l_party_type = ''ORGANIZATION''
6850: and CONTENT_SOURCE_TYPE = ''USER_ENTERED''
6851: and ACTUAL_CONTENT_SOURCE = ''SST''
6852: and not exists (select 1 from hz_organization_profiles op2
6853: where op2.content_source_type = ''USER_ENTERED''
6854: and op2.actual_content_source = ''USER_ENTERED''
6855: and op1.party_id = op2.party_id)'
6856: USING
6998:
6999: IF l_org_mixnmatch_enabled = 'Y' THEN
7000:
7001: /******************************************/
7002: /*** Update HZ_ORGANIZATION_PROFILES ***/
7003: /*** Mix and Match : enabled ***/
7004: /*** Orig system : USER_ENTERED ***/
7005: /*** Has DNB data ***/
7006: /*** Version Profile: NO_VERSION ***/
7160: p_msg_level=>fnd_log.level_statement);
7161: END IF;
7162:
7163: /******************************************/
7164: /*** Update HZ_ORGANIZATION_PROFILES ***/
7165: /*** Mix and Match : enabled ***/
7166: /*** Orig system : USER_ENTERED ***/
7167: /*** Has no DNB data ***/
7168: /*** Version Profile: ONE_DAY_VERSION ***/
7188: and trunc(EFFECTIVE_START_DATE) = :l_sysdate
7189: and :l_party_type = ''ORGANIZATION''
7190: and CONTENT_SOURCE_TYPE = ''USER_ENTERED''
7191: and ACTUAL_CONTENT_SOURCE = ''SST''
7192: and not exists (select 1 from hz_organization_profiles op2
7193: where op2.content_source_type = ''USER_ENTERED''
7194: and op2.actual_content_source = ''USER_ENTERED''
7195: and op1.party_id = op2.party_id)'
7196:
7260: l_party_id(j), l_num_row_processed(j), trunc(P_DML_RECORD.SYSDATE), l_party_type(j);
7261:
7262:
7263: /********************************************/
7264: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
7265: /*** Mix and Match : enabled ***/
7266: /*** Orig system : USER_ENTERED ***/
7267: /*** Has no DNB data ***/
7268: /*** Version Profile: ONE_DAY_VERSION ***/
7283: and :l_party_type = ''ORGANIZATION''
7284: and trunc(EFFECTIVE_START_DATE) < :l_sysdate
7285: and CONTENT_SOURCE_TYPE = ''USER_ENTERED''
7286: and ACTUAL_CONTENT_SOURCE = ''SST''
7287: and not exists (select 1 from hz_organization_profiles op2
7288: where op2.content_source_type = ''USER_ENTERED''
7289: and op2.actual_content_source = ''USER_ENTERED''
7290: and :l_party_id = op2.party_id)'
7291: USING
7355: l_do_not_confuse_with(j), P_DML_RECORD.GMISS_CHAR, l_do_not_confuse_with(j),
7356: l_party_id(j), l_num_row_processed(j), l_party_type(j), trunc(P_DML_RECORD.SYSDATE), l_party_id(j);
7357:
7358: /******************************************/
7359: /*** End date HZ_ORGANIZATION_PROFILES ***/
7360: /*** Mix and Match : enabled ***/
7361: /*** Orig system : USER_ENTERED ***/
7362: /*** Has no DNB data ***/
7363: /*** Version Profile: ONE_DAY_VERSION ***/
7368: p_msg_level=>fnd_log.level_statement);
7369: END IF;
7370:
7371: ForAll j in 1..l_party_orig_system_reference.count
7372: update hz_organization_profiles op1 set
7373: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
7374: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
7375: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
7376: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
7387: and trunc(EFFECTIVE_START_DATE) < trunc(P_DML_RECORD.SYSDATE)
7388: and l_party_type(j) = 'ORGANIZATION'
7389: and CONTENT_SOURCE_TYPE = 'USER_ENTERED'
7390: and ACTUAL_CONTENT_SOURCE = 'SST'
7391: and not exists (select 1 from hz_organization_profiles op2
7392: where op2.content_source_type = 'USER_ENTERED'
7393: and op2.actual_content_source = 'USER_ENTERED'
7394: and op1.party_id = op2.party_id)
7395: and nvl(request_id, -1) <> P_DML_RECORD.REQUEST_ID;
7590:
7591: IF l_org_mixnmatch_enabled = 'Y' THEN
7592:
7593: /******************************************/
7594: /*** Update HZ_ORGANIZATION_PROFILES ***/
7595: /*** Mix and Match : enabled ***/
7596: /*** Orig system : USER_ENTERED ***/
7597: /*** Version Profile: ONE_DAY_VERSION ***/
7598: /*** Has DNB data ***/
7681: l_do_not_confuse_with(j), P_DML_RECORD.GMISS_CHAR, l_do_not_confuse_with(j),
7682: l_party_id(j), l_num_row_processed(j), trunc(P_DML_RECORD.SYSDATE), l_party_type(j);
7683:
7684: /********************************************/
7685: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
7686: /*** Mix and Match : enabled ***/
7687: /*** Orig system : USER_ENTERED ***/
7688: /*** Has DNB data ***/
7689: /*** Version Profile: ONE_DAY_VERSION ***/
7772: l_do_not_confuse_with(j), P_DML_RECORD.GMISS_CHAR, l_do_not_confuse_with(j),
7773: l_party_id(j), l_num_row_processed(j), l_party_type(j), trunc(P_DML_RECORD.SYSDATE);
7774:
7775: /********************************************/
7776: /*** End date HZ_ORGANIZATION_PROFILES ***/
7777: /*** Mix and Match : enabled ***/
7778: /*** Orig system : USER_ENTERED ***/
7779: /*** Version Profile: ONE_DAY_VERSION ***/
7780: /*** Has DNB data ***/
7786: p_msg_level=>fnd_log.level_statement);
7787: END IF;
7788:
7789: ForAll j in 1..l_party_orig_system_reference.count
7790: update hz_organization_profiles op1 set
7791: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
7792: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
7793: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
7794: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
7998: p_msg_level=>fnd_log.level_statement);
7999: END IF;
8000:
8001: /********************************************/
8002: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
8003: /*** Mix and Match : enabled ***/
8004: /*** Version Profile: NEW_VERSION ***/
8005: /*** Orig system : 3rd Party ***/
8006: /*** With existing 3rd party data ***/
8083: l_party_id(j), l_num_row_processed(j), l_party_type(j),
8084: l_content_source_type, l_actual_content_source;
8085:
8086: /*************************************************/
8087: /*** End date current HZ_ORGANIZATION_PROFILES ***/
8088: /*** Mix and Match : enabled ***/
8089: /*** Version Profile: NEW_VERSION ***/
8090: /*** Orig system : 3rd Party ***/
8091: /*** With existing 3rd party data ***/
8096: p_msg_level=>fnd_log.level_statement);
8097: END IF;
8098:
8099: ForAll j in 1..l_party_orig_system_reference.count
8100: update hz_organization_profiles op1 set
8101: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
8102: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
8103: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
8104: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
8240: p_msg_level=>fnd_log.level_statement);
8241: END IF;
8242:
8243: /******************************************/
8244: /*** Update HZ_ORGANIZATION_PROFILES ***/
8245: /*** Mix and Match : enabled ***/
8246: /*** Version Profile: NO_VERSION ***/
8247: /*** Orig system : 3rd Party ***/
8248: /*** With existing 3rd party data ***/
8403:
8404: IF l_org_mixnmatch_enabled = 'Y' THEN
8405:
8406: /******************************************/
8407: /*** Update HZ_ORGANIZATION_PROFILES ***/
8408: /*** Mix and Match : enabled ***/
8409: /*** Version Profile: ONE_DAY_VERSION ***/
8410: /*** Orig system : 3rd Party ***/
8411: /*** With existing 3rd party data ***/
8489:
8490:
8491:
8492: /********************************************/
8493: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
8494: /*** Mix and Match : enabled ***/
8495: /*** Version Profile: ONE_DAY_VERSION ***/
8496: /*** Orig system : 3rd Party ***/
8497: /*** With existing 3rd party data ***/
8580: l_party_id(j), l_num_row_processed(j), l_party_type(j), trunc(P_DML_RECORD.SYSDATE),
8581: l_content_source_type, l_actual_content_source;
8582:
8583: /******************************************/
8584: /*** End date HZ_ORGANIZATION_PROFILES ***/
8585: /*** Mix and Match : enabled ***/
8586: /*** Version Profile: ONE_DAY_VERSION ***/
8587: /*** Orig system : 3rd Party ***/
8588: /*** With existing 3rd party data ***/
8593: p_msg_level=>fnd_log.level_statement);
8594: END IF;
8595:
8596: ForAll j in 1..l_party_orig_system_reference.count
8597: update hz_organization_profiles op1 set
8598: LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
8599: LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
8600: LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
8601: REQUEST_ID = P_DML_RECORD.REQUEST_ID,
8795:
8796: IF l_org_mixnmatch_enabled = 'Y' THEN
8797:
8798: /********************************************/
8799: /*** Insert into HZ_ORGANIZATION_PROFILES ***/
8800: /*** Mix and Match : enabled ***/
8801: /*** Orig system : 3rd Party ***/
8802: /*** Without existing 3rd party data ***/
8803: /********************************************/
8814: profile without getting values from the current user entered profile.
8815: Insert record after above update or the new record will be processed
8816: by the updates */
8817: ForAll j in 1..l_party_orig_system_reference.count
8818: insert into hz_organization_profiles
8819: (
8820: ORGANIZATION_PROFILE_ID,
8821: PARTY_ID,
8822: ORGANIZATION_NAME,
8896: DO_NOT_CONFUSE_WITH,
8897: ACTUAL_CONTENT_SOURCE
8898: )
8899: select
8900: hz_organization_profiles_s.nextval,
8901: l_party_id(j), -- assume l_party_id cannot be null or G_MISS
8902: nvl(l_organization_name(j), l_party_name(j)),
8903: decode(l_ceo_name(j), P_DML_RECORD.GMISS_CHAR, NULL,
8904: l_ceo_name(j)),
9037: from dual
9038: where
9039: l_num_row_processed(j) = 1
9040: and l_party_type(j) = 'ORGANIZATION'
9041: and not exists (select 1 from hz_organization_profiles op2
9042: where op2.content_source_type = l_content_source_type
9043: and op2.actual_content_source = l_actual_content_source
9044: and op2.party_id = l_party_id(j)
9045: and op2.effective_end_date is null);
9528:
9529: --Initialize API return status to success.
9530: x_return_status := FND_API.G_RET_STS_SUCCESS;
9531:
9532: l_org_mixnmatch_enabled := HZ_MIXNM_UTILITY.isMixNMatchEnabled('HZ_ORGANIZATION_PROFILES',l_entity_attr_id);
9533: l_per_mixnmatch_enabled := HZ_MIXNM_UTILITY.isMixNMatchEnabled('HZ_PERSON_PROFILES',l_entity_attr_id);
9534:
9535: l_content_source_type := P_DML_RECORD.ACTUAL_CONTENT_SRC;
9536: IF l_content_source_type = 'USER_ENTERED' THEN