[Home] [Help]
2351: decode(sst.organization_name_phonetic, '', 'Y', 'N'),
2352: nvl(i.cust_tax_reference, org.tax_reference),
2353: i.cust_tax_reference,
2354: decode(sst.tax_reference, '', 'Y', 'N'),nvl(org.version_number,1)+1
2355: FROM hz_organization_profiles org,
2356: hz_organization_profiles sst,
2357: ra_customers_interface_all i, -- Bug 4956131
2358: hz_cust_accounts cust,
2359: (SELECT min(i1.rowid) myrowid
2352: nvl(i.cust_tax_reference, org.tax_reference),
2353: i.cust_tax_reference,
2354: decode(sst.tax_reference, '', 'Y', 'N'),nvl(org.version_number,1)+1
2355: FROM hz_organization_profiles org,
2356: hz_organization_profiles sst,
2357: ra_customers_interface_all i, -- Bug 4956131
2358: hz_cust_accounts cust,
2359: (SELECT min(i1.rowid) myrowid
2360: FROM ra_customers_interface_all i1 -- Bug 4956131
2421: l_last_fetch BOOLEAN := FALSE;
2422:
2423: BEGIN
2424:
2425: -- check if mix-n-match is set up on hz_organization_profiles.
2426: l_enabled :=
2427: HZ_MIXNM_UTILITY.isMixNMatchEnabled(
2428: 'HZ_ORGANIZATION_PROFILES', l_entity_attr_id);
2429: IF l_enabled = 'N' THEN
2424:
2425: -- check if mix-n-match is set up on hz_organization_profiles.
2426: l_enabled :=
2427: HZ_MIXNM_UTILITY.isMixNMatchEnabled(
2428: 'HZ_ORGANIZATION_PROFILES', l_entity_attr_id);
2429: IF l_enabled = 'N' THEN
2430: RETURN;
2431: END IF;
2432:
2491:
2492: -- end-dated user-entered profiles for which we need to create new.
2493:
2494: FORALL i IN create_start..create_end
2495: UPDATE hz_organization_profiles
2496: SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC(SYSDATE-1))
2497: WHERE organization_profile_id = i_ue_profile_id(i);
2498:
2499: -- create new user entered profiles
2498:
2499: -- create new user entered profiles
2500:
2501: FORALL i IN create_start..create_end
2502: INSERT INTO hz_organization_profiles (
2503: created_by,
2504: creation_date,
2505: last_update_login,
2506: last_update_date,
2535: 'USER_ENTERED',
2536: 'USER_ENTERED',
2537: 'TCA-CUSTOMER-INTERFACE',
2538: 222,
2539: hz_organization_profiles_s.nextval,
2540: i_party_id(i),
2541: SYSDATE,
2542: 1,
2543: i_jgzz_fiscal_code(i),
2549:
2550: -- update user entered profiles
2551:
2552: FORALL i IN update_start..update_end
2553: UPDATE hz_organization_profiles
2554: SET
2555: last_updated_by = g_last_updated_by,
2556: last_update_login = g_last_update_login,
2557: last_update_date = SYSDATE,
3551: to_date('31-12-4712','DD-MM-YYYY')
3552: )));
3553: END insert_nci_party_usages;
3554:
3555: /* Bug 11819914 Sync tax_reference in HZ_PARTIES, HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES */
3556:
3557: PROCEDURE sync_tax_reference ( p_request_id IN NUMBER
3558: ,x_return_status OUT NOCOPY VARCHAR2
3559: ,x_msg_data OUT NOCOPY VARCHAR2)
3597: FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_PERSON_PROFILES : ' || l_party_id.count);
3598:
3599: FORALL i IN 1..l_party_id.count
3600:
3601: UPDATE HZ_ORGANIZATION_PROFILES org
3602: SET tax_reference = l_tax_reference(i)
3603: WHERE party_id = l_party_id(i)
3604: AND actual_content_source = 'SST'
3605: AND effective_end_date IS NULL;
3603: WHERE party_id = l_party_id(i)
3604: AND actual_content_source = 'SST'
3605: AND effective_end_date IS NULL;
3606:
3607: FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_ORGANIZATION_PROFILES : ' || l_party_id.count);
3608:
3609: CLOSE c_tax_reference;
3610:
3611: FND_FILE.put_line(fnd_file.log,'sync_tax_reference - ');