Wednesday, December 15, 2021

Supplier Interface in Oracle Apps (Execution Script to Upload Data in Oracle Interface)

I am using xx_supp_stag_all staging table where I have uploaded Supplier data through SQL loader. I am sharing you two procedures.

 

In the First procedure , I will share you the validation script so that you can validate the Data from your Staging table like the values you are uploading for Supplier is available in Oracle apps or not.

 

In the Second Procedure I will share your the Execution script to upload validated date in Supplier Interface Table.

 

 

Second Procedure (Execution Script)

PROCEDURE xx_supplier_detl

IS

CURSOR cur_supplier

IS

SELECT org_code, supplier_name, invoice_ccy_code,

supplier_classification
, location_code

FROM xx_supp_stag_all

WHERE NVL (vailidate_flag_process, 'E') = 'P'

AND NVL(FLAG_PROCESS,'NULL') ='NULL'

GROUP BY org_code, supplier_name, invoice_ccy_code,

supplier_classification
, location_code;

CURSOR cur_supplier_site (v_org_code VARCHAR2, v_supp_name VARCHAR2)

IS

SELECT * FROM xx_supp_stag_all

WHERE NVL (vailidate_flag_process, 'E') = 'P'

AND NVL (flag_process ,'NULL') = 'NULL'

AND SUPPLIER_NAME = V_SUPP_NAME

AND ORG_CODE = V_ORG_CODE ;

-------------Operating Unit id ------

CURSOR operating_org_id (v_operating_unit VARCHAR2)

IS

SELECT organization_id

FROM hr_operating_units

WHERE UPPER (NAME) = UPPER (v_operating_unit);

------------Location Id---------------

CURSOR cur_loaction_id (v_location_code VARCHAR2)

IS

SELECT location_id

FROM hr_locations

WHERE location_code = v_location_code;

----------------Combination Code------

CURSOR cur_combination_id (v_combination_code VARCHAR2)

IS

SELECT code_combination_id

FROM gl_code_combinations

WHERE code_combination_id = TO_NUMBER (v_combination_code);

--WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5=V_COMBINATION_CODE;

--------------Payment Term Id ---------

CURSOR cur_term_id (v_term_code VARCHAR2)

IS

SELECT term_id

FROM ap_terms

WHERE UPPER (NAME) = UPPER (TRIM (v_term_code));

vendor_id_num 
NUMBER;

insert_success_flag 
VARCHAR2 (2);

v_sql_errm 
VARCHAR2 (2000);

site_vendor_id_num 
NUMBER;

p_organization_id 
NUMBER;

l_pay_code_combination_id 
NUMBER (10);

l_prepay_code_combination_id 
NUMBER (10);

l_location_id 
NUMBER (10);

l_term_id 
NUMBER (10);

v_country 
VARCHAR2 (200);

v_terms_date_basis 
VARCHAR2 (200);

BEGIN

<<head_supp_info>>

FOR i IN cur_supplier

LOOP

----------------Refresh vairiable ------------

vendor_id_num 
:= NULL;

insert_success_flag 
:= NULL;

v_sql_errm 
:= NULL;

site_vendor_id_num 
:= NULL;

p_organization_id 
:= NULL;

l_pay_code_combination_id 
:= NULL;

l_prepay_code_combination_id 
:= NULL;

l_location_id 
:= NULL;

l_term_id 
:= NULL;

v_country 
:= NULL;

v_terms_date_basis 
:= NULL;

vendor_id_num 
:= ap_suppliers_int_s.NEXTVAL;

BEGIN

SAVEPOINT header_insert;

-----------------Location Id------------

IF i.location_code IS NOT NULL

THEN

OPEN cur_loaction_id (i.location_code);

FETCH cur_loaction_id

INTO l_location_id;

IF cur_loaction_id%NOTFOUND

THEN

l_location_id 
:= NULL;

CLOSE cur_loaction_id;

END IF;

CLOSE cur_loaction_id;

END IF;

INSERT INTO ap_suppliers_int

(vendor_interface_id, vendor_name, vendor_name_alt,

enabled_flag
, attribute10,

vendor_type_lookup_code
,

invoice_currency_code
,

payment_currency_code
, create_debit_memo_flag,

payment_method_lookup_code
, bill_to_location_id,



ship_to_location_id
  
)

VALUES (vendor_id_num, i.supplier_name, i.supplier_name,

'Y', 'TEST DATA LOADING',

UPPER (i.supplier_classification),

NVL (i.invoice_ccy_code, 'INR'),

NVL (i.invoice_ccy_code, 'INR'), 'Y',

'CHECK', l_location_id,



l_location_id
  
);

insert_success_flag 
:= 'Y';

EXCEPTION

WHEN OTHERS

THEN

insert_success_flag 
:= 'N';

v_sql_errm 
:= SQLERRM;

UPDATE xx_supp_stag_all

SET flag_process = 'E',

error_message 
= v_sql_errm

WHERE supplier_name = i.supplier_name

AND org_code = i.org_code

AND vailidate_flag_process = 'P';

GOTO next_supp;

END;

IF insert_success_flag = 'Y'

THEN

<<site_supp_info>>

FOR supp_site IN cur_supplier_site (i.org_code, i.supplier_name)

LOOP

---------------Operating Org Id ---------------

BEGIN

OPEN operating_org_id (i.org_code);

FETCH operating_org_id

INTO p_organization_id;

IF operating_org_id%NOTFOUND

THEN

p_organization_id 
:= NULL;

CLOSE operating_org_id;

END IF;

CLOSE operating_org_id;

BEGIN



SELECT territory_code

INTO v_country

FROM fnd_territories

WHERE UPPER (nls_territory) = UPPER (supp_site.country);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_country 
:= NULL;

WHEN OTHERS

THEN

v_country 
:= NULL;

END;

------------Combination Code-----------

IF supp_site.gl_liability_ac IS NOT NULL

THEN

OPEN cur_combination_id (supp_site.gl_liability_ac);

FETCH cur_combination_id

INTO l_pay_code_combination_id;

IF cur_combination_id%NOTFOUND

THEN

l_pay_code_combination_id 
:= NULL;

CLOSE cur_combination_id;

END IF;

CLOSE cur_combination_id;

END IF;





-------------
  
  IF supp_site.gl_advance_supplier_ac IS NOT NULL

THEN

OPEN cur_combination_id

(supp_site.gl_advance_supplier_ac);

FETCH cur_combination_id

INTO l_prepay_code_combination_id;

IF cur_combination_id%NOTFOUND

THEN

l_pay_code_combination_id 
:= NULL;

CLOSE cur_combination_id;

END IF;

CLOSE cur_combination_id;

END IF;

---------------Term_id -----------------

IF supp_site.payment_term IS NOT NULL

THEN

OPEN cur_term_id (supp_site.payment_term);

FETCH cur_term_id

INTO l_term_id;

IF cur_term_id%NOTFOUND

THEN

l_term_id 
:= NULL;

CLOSE cur_term_id;

END IF;

CLOSE cur_term_id;

END IF;

---------------Term data basis-----------

BEGIN

SELECT lookup_code

INTO v_terms_date_basis

FROM fnd_lookup_values

WHERE fnd_lookup_values.lookup_type = 'TERMS DATE BASIS'

AND UPPER (lookup_code) =

UPPER (supp_site.term_date_basis);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_terms_date_basis 
:= NULL;

WHEN OTHERS

THEN

v_terms_date_basis 
:= NULL;

END;

INSERT INTO ap_supplier_sites_int

(vendor_interface_id, vendor_site_interface_id,

vendor_site_code
,

vendor_site_code_alt
,

payment_method_lookup_code
, terms_id,

address_line1
,

address_line2
,

address_line3
, org_id,

city
, province,

area_code
, state,

country
,

phone
,

fax_area_code
,

fax
,

zip
,

email_address
, bill_to_location_id,

ship_to_location_id
, match_option,

terms_date_basis
, purchasing_site_flag,

pay_site_flag
, create_debit_memo_flag,

accts_pay_code_combination_id
,

prepay_code_combination_id
,

attribute10
,

attribute9
,

attribute6
, attribute7

)

VALUES (vendor_id_num, ap_suppliers_int_s.NEXTVAL,

NVL (supp_site.supplier_site_name, 'OFFICE'),

NVL (supp_site.supplier_site_name, 'OFFICE'),

'CHECK', l_term_id,

NVL (supp_site.address_line1, 'Not Specified'),

supp_site
.address_line2,

supp_site
.address_line3, p_organization_id,

supp_site
.city, supp_site.city,

NVL (supp_site.std_code, 00), supp_site.state,

v_country
,

SUBSTR (supp_site.phone_number, 1, 15),

NVL (supp_site.std_code, 00),

SUBSTR (supp_site.fax_number, 1, 15),

supp_site
.postal_code,

supp_site
.email_address, l_location_id,

l_location_id
, 'R',

-- upper(NVL(V_TERMS_DATE_BASIS,'Invoice')),

NVL (v_terms_date_basis, 'Invoice'), 'Y',

'Y', 'Y',

l_pay_code_combination_id
,

l_prepay_code_combination_id
,

'TEST DATA LOADING',,

supp_site
.term_date_basis,

supp_site
.phone_number, supp_site.fax_number

);

UPDATE xx_supp_stag_all

SET flag_process = 'P'

WHERE entry_id = supp_site.entry_id;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK TO SAVEPOINT header_insert;

insert_success_flag 
:= 'N';

v_sql_errm 
:= SQLERRM;

UPDATE xx_supp_stag_all

SET flag_process = 'E',

error_message 
= v_sql_errm

WHERE supplier_name = i.supplier_name

AND org_code = i.org_code

AND vailidate_flag_process = 'P';

GOTO next_supp;

END;

END LOOP site_supp_info;

END IF;

<<next_supp>>

COMMIT;

END LOOP head_supp_info;

END xx_supplier_detl;

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...