[Home] [Help]
1: PACKAGE BODY HZ_organization_profiles_pkg AS
2: /*$Header: ARHORGTB.pls 120.6 2005/07/26 18:59:40 jhuang ship $ */
3:
4: g_miss_content_source_type CONSTANT VARCHAR2(30) := 'USER_ENTERED';
5: g_sst_source_type CONSTANT VARCHAR2(30) := 'SST';
8: p_duns_number_c IN VARCHAR2
9: ) RETURN NUMBER IS
10:
11: l_char VARCHAR2(1);
12: l_str HZ_ORGANIZATION_PROFILES.DUNS_NUMBER_C%TYPE;
13:
14: BEGIN
15:
16: -- if duns_number is null and duns_number_c is not null then get the
208:
209:
210: WHILE l_success = 'N' LOOP
211: BEGIN
212: INSERT INTO hz_organization_profiles (
213: organization_profile_id,
214: party_id,
215: organization_name,
216: attribute_category,
370: home_country
371: )
372: VALUES (
373: DECODE(x_organization_profile_id,
374: fnd_api.g_miss_num, hz_organization_profiles_s.NEXTVAL,
375: NULL, hz_organization_profiles_s.NEXTVAL,
376: x_organization_profile_id),
377: DECODE(x_party_id, fnd_api.g_miss_num, NULL, x_party_id),
378: DECODE(x_organization_name,
371: )
372: VALUES (
373: DECODE(x_organization_profile_id,
374: fnd_api.g_miss_num, hz_organization_profiles_s.NEXTVAL,
375: NULL, hz_organization_profiles_s.NEXTVAL,
376: x_organization_profile_id),
377: DECODE(x_party_id, fnd_api.g_miss_num, NULL, x_party_id),
378: DECODE(x_organization_name,
379: fnd_api.g_miss_char, NULL,
726: l_success := 'Y';
727:
728: EXCEPTION
729: WHEN DUP_VAL_ON_INDEX THEN
730: IF INSTRB(SQLERRM, 'HZ_ORGANIZATION_PROFILES_U1') <> 0 OR
731: INSTRB(SQLERRM, 'HZ_ORGANIZATION_PROFILES_PK') <> 0
732: THEN
733: DECLARE
734: l_count NUMBER;
727:
728: EXCEPTION
729: WHEN DUP_VAL_ON_INDEX THEN
730: IF INSTRB(SQLERRM, 'HZ_ORGANIZATION_PROFILES_U1') <> 0 OR
731: INSTRB(SQLERRM, 'HZ_ORGANIZATION_PROFILES_PK') <> 0
732: THEN
733: DECLARE
734: l_count NUMBER;
735: l_dummy VARCHAR2(1);
734: l_count NUMBER;
735: l_dummy VARCHAR2(1);
736: -- Bug 2117973: changed select...intos into cursors.
737: CURSOR c_seq IS
738: SELECT hz_organization_profiles_s.NEXTVAL
739: FROM dual;
740: CURSOR c_dupchk IS
741: SELECT 'Y'
742: FROM hz_organization_profiles hop
738: SELECT hz_organization_profiles_s.NEXTVAL
739: FROM dual;
740: CURSOR c_dupchk IS
741: SELECT 'Y'
742: FROM hz_organization_profiles hop
743: WHERE hop.organization_profile_id = x_organization_profile_id;
744: BEGIN
745: l_count := 1;
746: WHILE l_count > 0 LOOP
942: l_duns_number_c:=lpad(x_duns_number_c,9,'0');
943: END IF;
944:
945:
946: UPDATE hz_organization_profiles hop
947: SET hop.organization_profile_id =
948: DECODE(x_organization_profile_id,
949: NULL, hop.organization_profile_id,
950: fnd_api.g_miss_num, NULL, x_organization_profile_id),
1869: ) IS
1870:
1871: CURSOR c IS
1872: SELECT *
1873: FROM hz_organization_profiles hop
1874: WHERE hop.ROWID = x_Rowid
1875: FOR UPDATE NOWAIT;
1876: recinfo c%ROWTYPE;
1877:
2664: NVL(hop.application_id, fnd_api.g_miss_num),
2665: NVL(hop.do_not_confuse_with, fnd_api.g_miss_char),
2666: NVL(hop.actual_content_source, fnd_api.g_miss_char),
2667: NVL(hop.home_country, fnd_api.g_miss_char)
2668: FROM hz_organization_profiles hop
2669: WHERE hop.organization_profile_id = x_organization_profile_id;
2670: BEGIN
2671: OPEN c_orgprof;
2672: FETCH c_orgprof
2829:
2830: PROCEDURE delete_row (x_organization_profile_id IN NUMBER) IS
2831: BEGIN
2832:
2833: DELETE FROM hz_organization_profiles hop
2834: WHERE hop.organization_profile_id = x_organization_profile_id;
2835:
2836: IF (SQL%NOTFOUND) THEN
2837: RAISE NO_DATA_FOUND;
2838: END IF;
2839:
2840: END Delete_Row;
2841:
2842: END HZ_organization_profiles_pkg;