Supplier Upload EBS

Using hz_imp_parties_int interface

image 29 supplier upload 1
CREATE OR REPLACE PACKAGE idc_supplier_upload_pkg AS
PROCEDURE idc_supplier_insert
(P_BATCH_ID VARCHAR2,
P_SYSTEM VARCHAR2,
P_SUPPLIER VARCHAR2,
P_PARTY_NAME VARCHAR2,
P_ADDRESS VARCHAR2,
P_CITY VARCHAR2,
P_STATE VARCHAR2,
P_POSTAL VARCHAR2,
P_COUNTRY VARCHAR2,
P_CONTACT VARCHAR2,
P_CONTACT_NO VARCHAR2) ;
END idc_supplier_upload_pkg;
/
 CREATE OR REPLACE PACKAGE BODY idc_supplier_upload_pkg AS
  PROCEDURE idc_supplier_insert
 (P_BATCH_ID VARCHAR2,
 P_SYSTEM VARCHAR2,
 P_SUPPLIER VARCHAR2,
 P_PARTY_NAME VARCHAR2,
 P_ADDRESS VARCHAR2,
 P_CITY VARCHAR2,
 P_STATE VARCHAR2,
 P_POSTAL VARCHAR2,
 P_COUNTRY VARCHAR2,
 P_CONTACT VARCHAR2,
 P_CONTACT_NO VARCHAR2) is
 l_batch_id NUMBER                           := P_BATCH_ID;-- -1
    l_system VARCHAR2(100)                      := P_SYSTEM;--0
    l_party_academic_title VARCHAR2(240)        := 'IMPDEMO6';
    l_party_reference VARCHAR2(100)             := 'DEMOSYNC'||idc_seq.nextval;
    l_party_site_reference VARCHAR2(100);
    l_party_site_reference2 VARCHAR2(100);
    l_party_person_reference VARCHAR2(100);
    l_party_person_reference2 VARCHAR2(100);
    l_party_name VARCHAR2(100);
    l_duns_number VARCHAR2(100);
    l_person_first_name VARCHAR2(100);
    l_person_last_name VARCHAR2(100);
    l_person_first_name2 VARCHAR2(100);
    l_person_last_name2 VARCHAR2(100);
    l_party_known_as    VARCHAR2(240);
    l_party_pref_currency VARCHAR2(100);
    l_party_ctrl_year     NUMBER;
    l_party_incorp_year   NUMBER;
    l_party_year_est      NUMBER;
    l_party_emp_total     NUMBER;
    l_party_emp_est_ind   VARCHAR2(100);
    l_party_emp_min_ind   VARCHAR2(100);
    l_emp_primary_add     VARCHAR2(10);
    l_emp_primary_add_est_ind  VARCHAR2(30);
    l_emp_primary_add_min_ind  VARCHAR2(30);
    l_party_lob                VARCHAR2(240);
    l_party_rent_ind           VARCHAR2(30);
    l_party_ceo_name           VARCHAR2(240);
    l_party_ceo_title          VARCHAR2(240);
    l_person_name              VARCHAR2(360);
    l_person_name2             VARCHAR2(360);
    l_person_title             VARCHAR2(60);
    l_person_title2            VARCHAR2(60);      
    l_person_gender            VARCHAR2(30);      
    l_person_gender2           VARCHAR2(30);  
    l_person_prename_adjunct   VARCHAR2(30);  
    l_person_prename_adjunct2  VARCHAR2(30); 
    l_person_middle_name       VARCHAR2(60); 
    l_person_middle_name2      VARCHAR2(60); 
    l_person_name_suffix       VARCHAR2(30);
    l_person_name_suffix2      VARCHAR2(30);
    l_person_prev_last_name    VARCHAR2(150);
    l_person_prev_last_name2   VARCHAR2(150);
    l_persons_initials         VARCHAR2(6);
    l_persons_initials2        VARCHAR2(6);
    l_person_known_as          VARCHAR2(240);
    l_person_known_as2         VARCHAR2(240);
    l_person_identifier        VARCHAR2(60);
    l_person_identifier2       VARCHAR2(60);
    l_person_dob               DATE;
    l_person_dob2              DATE;
    l_person_birthplace        VARCHAR2(60);
    l_person_birthplace2       VARCHAR2(60);
    l_person_dod               DATE;
    l_person_dod2              DATE;
    l_person_ethnicity         VARCHAR2(60);
    l_person_ethnicity2        VARCHAR2(60);
    l_address_name             VARCHAR2(240);
    l_address_name2            VARCHAR2(240);
    l_address_line1            VARCHAR2(240);
    l_address_line2            VARCHAR2(240); 
    l_address2_line1           VARCHAR2(240);
    l_address2_line2           VARCHAR2(240);  
    l_site_city                VARCHAR2(60); 
    l_site_city2               VARCHAR2(60);
    l_site_state               VARCHAR2(60);
    l_site_state2              VARCHAR2(60);
    l_site_postalcode          VARCHAR2(60);
    l_site_postalcode2         VARCHAR2(60);
    l_site_country             VARCHAR2(2); 
    l_site_country2            VARCHAR2(2); 
 BEGIN
    -- party details
 l_party_name                := P_PARTY_NAME;--- 1
    l_duns_number               := '210307406'||idc_seq.nextval;
    l_party_known_as            := P_PARTY_NAME;-- REPEAT 1
    l_party_pref_currency       := 'USD';
    l_party_ctrl_year           := 1929;
    l_party_incorp_year         := 1950;
    l_party_year_est            := 1927;
    l_party_emp_total           := 75;
    l_party_emp_est_ind         := 'N';
    l_party_emp_min_ind         := 'C';
    l_emp_primary_add           := '75';
    l_emp_primary_add_est_ind   := 'N';
    l_emp_primary_add_min_ind   := 'C';
    l_party_lob                 := '';
    l_party_rent_ind            := 'H'; -- shares. 
    l_party_ceo_name            := P_PARTY_NAME;-- REPEAT 1
    l_party_ceo_title           := 'PRINCIPAL';
 -- person details   
    l_party_person_reference    := l_party_reference || '-PERSON-01';
    l_party_person_reference2   := l_party_reference || '-PERSON-02';  
    l_person_name               := P_PARTY_NAME;-- REPEAT 1
    l_person_name2              := 'Jeff Anderson';
    l_person_title              := 'MRS.';
    l_person_title2             := 'MR.';   
    l_person_gender             := 'FEMALE';
    l_person_gender2            := 'MALE';
    l_person_prename_adjunct    := 'DR.';
    l_person_prename_adjunct2   := 'DR.';
    l_person_first_name         := P_SUPPLIER;--11
    l_person_last_name          :='';-- 'THOMPSON';
    l_person_first_name2        := 'Jeff';
    l_person_last_name2         := 'Anderson';
    l_person_middle_name        := 'C';
    l_person_middle_name2       := 'Bradley';
    l_person_name_suffix        := NULL;
    l_person_name_suffix2       := NULL;
    l_person_prev_last_name     := 'SAMS';
    l_person_prev_last_name2    := NULL;
    l_persons_initials          := 'BCT';
    l_persons_initials2         := 'JBA';
    l_person_known_as           := 'LIZ';
    l_person_known_as2          := NULL;
    l_person_identifier         := 'B9384-24';
    l_person_identifier2        := '293880193';
    l_person_dob                := to_date('24-JAN-1953','DD-MON-YYYY');
    l_person_dob2               := to_date('14-MAY-1965','DD-MON-YYYY');
    l_person_birthplace         := 'Boston';
    l_person_birthplace2        := 'Virginia, USA';
    l_person_dod                := NULL;
    l_person_dod2               := NULL;
    l_person_ethnicity          := 'C';
    l_person_ethnicity2         := 'N/A';
 --address details   
    l_party_site_reference      := l_party_reference || '-SITE-01'||idc_seq.nextval;
    l_party_site_reference2     := l_party_reference || '-SITE-02';  
    l_address_name              := P_ADDRESS ;--3
    l_address_name2             := 'BOLOGNA';
    l_address_line1             := P_ADDRESS;--3
    l_address2_line1            := 'Via Delle Lame, 122';
    l_address_line2             := NULL;
    l_address2_line2            := NULL;
    l_site_city                 := P_CITY;--4
    l_site_city2                := 'Bologna';
    l_site_country              := P_COUNTRY;--7
    l_site_country2             := 'IT';
    l_site_state                := P_STATE;--5
    l_site_state2               := NULL;
    l_site_postalcode           := P_POSTAL;--6
    l_site_postalcode2          := '40121';
 -- party
    INSERT INTO hz_imp_parties_int (        --
        batch_id,                           -- Supplier Party
        party_orig_system,                  --
        party_orig_system_reference,
        party_type,
        organization_name,
        duns_number_c,
        KNOWN_AS,
        PREF_FUNCTIONAL_CURRENCY,
        CONTROL_YR,
        INCORP_YEAR,
        YEAR_ESTABLISHED,
        EMPLOYEES_TOTAL,
        TOTAL_EMP_EST_IND,
        TOTAL_EMP_MIN_IND,
        EMP_AT_PRIMARY_ADR,
        EMP_AT_PRIMARY_ADR_EST_IND,
        EMP_AT_PRIMARY_ADR_MIN_IND,
        LINE_OF_BUSINESS,
        RENT_OWN_IND,
        CEO_NAME,
        CEO_TITLE,
        person_academic_title)
    VALUES (
        l_batch_id,
        l_system,
        l_party_reference,
        'ORGANIZATION',
        l_party_name,
        l_duns_number,
        l_party_known_as,
        l_party_pref_currency,
        l_party_ctrl_year,
        l_party_incorp_year,
        l_party_year_est,
        l_party_emp_total,
        l_party_emp_est_ind,
        l_party_emp_min_ind,
        l_emp_primary_add,
        l_emp_primary_add_est_ind,
        l_emp_primary_add_min_ind,
        l_party_lob,
        l_party_rent_ind,
        l_party_ceo_name,
        l_party_ceo_title,
        l_party_academic_title
    );
 --person 1
    INSERT INTO hz_imp_parties_int (        --
        batch_id,                           -- Supplier Person
        party_orig_system,                  --
        party_orig_system_reference,
        party_type,
        person_first_name,
        person_last_name,
        person_title,
        organization_name,
        GENDER,
        PERSON_PRE_NAME_ADJUNCT,
        PERSON_MIDDLE_NAME,
        PERSON_NAME_SUFFIX,
        PERSON_PREVIOUS_LAST_NAME,
        PERSON_INITIALS,
        KNOWN_AS,
        PERSON_IDENTIFIER,
        DATE_OF_BIRTH,
        PLACE_OF_BIRTH,
        DATE_OF_DEATH,
        DECLARED_ETHNICITY)
    VALUES (
        l_batch_id,
        l_system,
        l_party_person_reference,
        'PERSON',
        l_person_first_name,
        l_person_last_name,
        l_person_title,
        l_person_name,
        l_person_gender,
        l_person_prename_adjunct2,
        l_person_middle_name2,
        l_person_name_suffix,
        l_person_prev_last_name,
        l_persons_initials,
        l_person_known_as,
        l_person_identifier,
        l_person_dob,
        l_person_birthplace,
        l_person_dod,
        l_person_ethnicity
    );
 INSERT INTO hz_imp_contacts_int(
        batch_id,
        contact_orig_system,
        contact_number,
        contact_orig_system_reference,
        sub_orig_system,
        sub_orig_system_reference,
        obj_orig_system,
        obj_orig_system_reference,
        relationship_type,
        relationship_code,
        start_date
    ) VALUES (
        l_batch_id,
        l_system,
        l_person_first_name || l_person_last_name, --
        l_party_person_reference,
        l_system,
        l_party_person_reference, -- SUBJECT_ORIG_SYSTEM_REFERENCE
        l_system,
        l_party_reference,
        'CONTACT',               --RELATIONSHIP_TYPE
        'CONTACT_OF',            --RELATIONSHIP_CODE
         SYSDATE
    );
  /*  
    -- person 2
    INSERT INTO hz_imp_parties_int (        --
        batch_id,                           -- Supplier Person
        party_orig_system,                  --
        party_orig_system_reference,
        party_type,
        person_first_name,
        person_last_name,
        person_title,
        organization_name,
        GENDER,
        PERSON_PRE_NAME_ADJUNCT,
        PERSON_MIDDLE_NAME,
        PERSON_NAME_SUFFIX,
        PERSON_PREVIOUS_LAST_NAME,
        PERSON_INITIALS,
        KNOWN_AS,
        PERSON_IDENTIFIER,
        DATE_OF_BIRTH,
        PLACE_OF_BIRTH,
        DATE_OF_DEATH,
        DECLARED_ETHNICITY)
    VALUES (
        l_batch_id,
        l_system,
        l_party_person_reference2,
        'PERSON',
        l_person_first_name2,
        l_person_last_name2,
        l_person_title2,
        l_person_name2,
        l_person_gender2,
        l_person_prename_adjunct2,
        l_person_middle_name2,
        l_person_name_suffix2,
        l_person_prev_last_name2,
        l_persons_initials2,
        l_person_known_as2,
        l_person_identifier2,
        l_person_dob2,
        l_person_birthplace2,
        l_person_dod2,
        l_person_ethnicity2
    );
 INSERT INTO hz_imp_contacts_int(
        batch_id,
        contact_orig_system,
        contact_number,
        contact_orig_system_reference,
        sub_orig_system,
        sub_orig_system_reference,
        obj_orig_system,
        obj_orig_system_reference,
        relationship_type,
        relationship_code,
        start_date
    ) VALUES (
        l_batch_id,
        l_system,
        l_person_first_name2 || l_person_last_name2, --
        l_party_person_reference2,
        l_system,
        l_party_person_reference2, -- SUBJECT_ORIG_SYSTEM_REFERENCE
        l_system,
        l_party_reference,
        'CONTACT',               --RELATIONSHIP_TYPE
        'CONTACT_OF',            --RELATIONSHIP_CODE
        SYSDATE
    );
    /    --address 1    INSERT INTO hz_imp_addresses_int (      --        batch_id,                           -- Supplier Site        party_orig_system,                  --        party_orig_system_reference,        site_orig_system,        site_orig_system_reference,        party_site_name,        address1,        address2,        country,        --address1_std,        --country_std,        state,        --province,        --county,        city,        postal_code    ) VALUES (        l_batch_id,        l_system,        l_party_reference,        l_system,        l_party_site_reference,        l_address_name,        l_address_line1,        l_address_line2,        l_site_country,        l_site_state,        --'West Coast',        --'San Mateo',        l_site_city,        l_site_postalcode    );  /  
    --address 2
    INSERT INTO hz_imp_addresses_int (      --
        batch_id,                           -- Supplier Site
        party_orig_system,                  --
        party_orig_system_reference,
        site_orig_system,
        site_orig_system_reference,
        party_site_name,
        address1,
        address2,
        country,
        --address1_std,
        --country_std,
        state,
        --province,
        --county,
        city,
        postal_code
    ) VALUES (
        l_batch_id,
        l_system,
        l_party_reference,
        l_system,
        l_party_site_reference2,
        l_address_name2,
        l_address2_line1,
        l_address2_line2,
        l_site_country2,
        l_site_state2,
        --'West Coast',
        --'San Mateo',
        l_site_city2,
        l_site_postalcode2
    );
    */
    COMMIT;
 END;
 END;
 --SELECT * FROM USER_ERRORS WHERE LOWER(NAME) ='idc_supplier_upload_pkg'

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Enable registration in settings - general
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart