[Home] [Help]
2648: decode(trunc(per.effective_start_date),trunc(sysdate),'U','C')) create_update_flag,
2649: per.person_profile_id,
2650: per.party_id,
2651: -- User NVL for bug 1404725 when we have to take
2652: -- value from hz_person_profiles. By selecting
2653: -- non-NVL value here is to differentiate when
2654: -- customer is updating the column by passing value
2655: -- and when he/she does not want to update the column
2656: -- by setting the column to null. This information
2674: decode(sst.person_last_name_phonetic, '', 'Y', 'N'),
2675: nvl(i.cust_tax_reference, per.tax_reference),
2676: i.cust_tax_reference,
2677: decode(sst.tax_reference, '', 'Y', 'N'),nvl(per.version_number,1)+1
2678: FROM hz_person_profiles per,
2679: hz_person_profiles sst,
2680: ra_customers_interface_all i, -- Bug 4956131
2681: hz_cust_accounts cust,
2682: (SELECT min(i1.rowid) myrowid
2675: nvl(i.cust_tax_reference, per.tax_reference),
2676: i.cust_tax_reference,
2677: decode(sst.tax_reference, '', 'Y', 'N'),nvl(per.version_number,1)+1
2678: FROM hz_person_profiles per,
2679: hz_person_profiles sst,
2680: ra_customers_interface_all i, -- Bug 4956131
2681: hz_cust_accounts cust,
2682: (SELECT min(i1.rowid) myrowid
2683: FROM ra_customers_interface_all i1 -- Bug 4956131
2762: l_last_fetch BOOLEAN := FALSE;
2763:
2764: BEGIN
2765:
2766: -- check if mix-n-match is set up on hz_person_profiles.
2767: l_enabled :=
2768: HZ_MIXNM_UTILITY.isMixNMatchEnabled(
2769: 'HZ_PERSON_PROFILES', l_entity_attr_id);
2770: IF l_enabled = 'N' THEN
2765:
2766: -- check if mix-n-match is set up on hz_person_profiles.
2767: l_enabled :=
2768: HZ_MIXNM_UTILITY.isMixNMatchEnabled(
2769: 'HZ_PERSON_PROFILES', l_entity_attr_id);
2770: IF l_enabled = 'N' THEN
2771: RETURN;
2772: END IF;
2773:
2842:
2843: -- end-dated user-entered profiles for which we need to create new.
2844:
2845: FORALL i IN create_start..create_end
2846: UPDATE hz_person_profiles
2847: SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC(SYSDATE-1))
2848: WHERE person_profile_id = i_ue_profile_id(i);
2849:
2850: -- create new user entered profiles
2849:
2850: -- create new user entered profiles
2851:
2852: FORALL i IN create_start..create_end
2853: INSERT INTO hz_person_profiles (
2854: created_by,
2855: creation_date,
2856: last_update_login,
2857: last_update_date,
2890: 'USER_ENTERED',
2891: 'USER_ENTERED',
2892: 'TCA-CUSTOMER-INTERFACE',
2893: 222,
2894: hz_person_profiles_s.nextval,
2895: i_party_id(i),
2896: SYSDATE,
2897: 1,
2898: i_jgzz_fiscal_code(i),
2908:
2909: -- update user entered profiles
2910:
2911: FORALL i IN update_start..update_end
2912: UPDATE hz_person_profiles
2913: SET
2914: last_updated_by = g_last_updated_by,
2915: last_update_login = g_last_update_login,
2916: 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)
3587: FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_PARTIES : ' || l_party_id.count);
3588:
3589: FORALL i IN 1..l_party_id.count
3590:
3591: UPDATE HZ_PERSON_PROFILES per
3592: SET tax_reference = l_tax_reference(i)
3593: WHERE per.party_id = l_party_id(i)
3594: AND actual_content_source = 'SST'
3595: AND effective_end_date IS NULL;
3593: WHERE per.party_id = l_party_id(i)
3594: AND actual_content_source = 'SST'
3595: AND effective_end_date IS NULL;
3596:
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