Home ยป Supplier Upload EBS
Supplier Upload EBS
Using hz_imp_parties_int interface
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'
