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'

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart