CREATE OR REPLACE PACKAGE APPS.SUPPLIERS_SITES_CONV_PKG AS
PROCEDURE SUPPLIERS_SITES_CONV_PRC(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
END SUPPLIERS_SITES_CONV_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.SUPPLIERS_SITES_CONV_PKG AS
PROCEDURE SUPPLIERS_SITES_CONV_PRC(
errbuf OUT VARCHAR2,
retcode OUT NUMBER
)
IS
CURSOR c
IS
SELECT a.ROWID, a.*
FROM xx_supplier_sites_stg a
WHERE a.status = 'N';
--and a.vendor_id between 101 and 1000;
l_invoice_currency_code VARCHAR2 (10);
L_BILL_TO_LOCATION_ID NUMBER;
l_payment_currency_code VARCHAR2 (10);
x_code_combination VARCHAR2(181 byte);
l_error_msg1 varchar2(300);
l_error_msg2 varchar2(300);
l_ccid Number;
l_flag VARCHAR2 (1);
l_org_id NUMBER;
l_vendor_id NUMBER;
prepay_dist_id NUMBER;
liability_dist_id NUMBER;
err_msg VARCHAR2 (2000);
l_terms_id NUMBER;
l_country VARCHAR2 (50);
l_address_name VARCHAR2 (240);
l_address_line1 VARCHAR2 (240);
l_address_line2 VARCHAR2 (240);
l_address_line3 VARCHAR2 (240);
l_address_line4 VARCHAR2 (240);
l_city VARCHAR2 (100);
l_province VARCHAR2 (150);
l_zip VARCHAR2 (20);
l_state VARCHAR2 (30);
l_phone_number VARCHAR2 (50);
--L_BILL_TO_LOCATION_ID NUMBER;
l_area_code VARCHAR2 (50);
l_phone VARCHAR2 (50);
BEGIN
BEGIN
mo_global.init ('ONT');
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
FOR i IN c
LOOP
BEGIN
l_flag := 'Y';
err_msg := NULL;
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE TRIM (vendor_id) = TRIM (i.vendor_id)
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
err_msg := err_msg || '--' || 'VENDOR_NAME NOT EXISTING';
fnd_file.put_line (fnd_file.LOG,
'Invalid Supplier: ' || i.vendor_id
);
END;
--BEGIN
--L_BILL_TO_LOCATION_ID:=NULL;
--IF l_org_id=631 THEN
--L_BILL_TO_LOCATION_ID:=142;
--END IF;
----IF l_org_id=102 THEN
----L_BILL_TO_LOCATION_ID:=194;
----END IF;
----IF l_org_id=103 THEN
----L_BILL_TO_LOCATION_ID:=NULL;
----END IF;
-- EXCEPTION
--WHEN OTHERS THEN
--NULL;
----L_BILL_TO_LOCATION_ID:=NULL;
--END;
BEGIN
SELECT code_combination_id
INTO prepay_dist_id
FROM gl_code_combinations
WHERE segment2||'.'||segment3||'.'||segment4||'.'||segment5=TRIM(i.prepay_code_comb_id)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
and CHART_OF_ACCOUNTS_ID=50445
AND ROWNUM=1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('enter exception');
x_code_combination := i.prepay_code_comb_id||'.'||'000'||'.'||'000';
airtel_create_ccid_item(x_code_combination,l_error_msg1,l_error_msg2,l_ccid);
prepay_dist_id := l_ccid;
END;
BEGIN
SELECT code_combination_id
INTO liability_dist_id
FROM gl_code_combinations
WHERE segment2||'.'||segment3||'.'||segment4||'.'||segment5=TRIM(i.accts_pay_code_comb_id)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
and CHART_OF_ACCOUNTS_ID=50445
AND ROWNUM=1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('enter exception');
x_code_combination := i.accts_pay_code_comb_id||'.'||'000'||'.'||'000';
airtel_create_ccid_item(x_code_combination,l_error_msg1,l_error_msg2,l_ccid);
liability_dist_id := l_ccid;
end;
BEGIN
SELECT DISTINCT DECODE (invoice_currency_code,
'ZMK', 'ZMW',
'USD', 'USD',
'ZAR', 'ZAR'
),
DECODE (payment_currency_code,
'ZMK', 'ZMW',
'USD', 'USD',
'ZAR', 'ZAR'
) INTO l_invoice_currency_code,l_payment_currency_code
FROM xx_supplier_sites_stg
WHERE TRIM (vendor_id) = TRIM (i.vendor_id)
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN NULL;
END;
BEGIN
SELECT term_id
INTO l_terms_id
FROM ap_terms
WHERE TRIM (term_id) = TRIM (i.terms_id)
AND end_date_active IS NULL
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
err_msg := err_msg || '--' || 'l_TERMS_ID NOT EXISTING';
fnd_file.put_line (fnd_file.LOG,
'Invalid Payment Term : ' || i.terms_id
);
END;
-- BEGIN
---- SELECT country, address_line1,
---- address_line2, address_line3, address_line4,
---- city, province, zip, state
---- INTO l_country, l_address_line1,
---- l_address_line2, l_address_line3, l_address_line4,
---- l_city, l_province, l_zip, l_state
---- FROM xx_supplier_sites_stg
---- WHERE TRIM (vendor_id) = TRIM (i.vendor_id) AND ROWNUM = 1;
-- SELECT country, address_line1,
-- address_line2, address_line3, address_line4,
-- city, province, zip, state,
-- area_code,phone
-- INTO l_country, l_address_line1,
-- l_address_line2, l_address_line3, l_address_line4,
-- l_city, l_province, l_zip, l_state,
-- l_area_code,l_phone
-- FROM xx_supplier_sites_stg;-- where vendor_id=561600 ;
-- --WHERE TRIM (vendor_id) = TRIM (i.vendor_id) AND ROWNUM = 1;
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- err_msg := err_msg || '--' || 'Address NOT EXISTING';
-- l_flag := 'E';
-- fnd_file.put_line (fnd_file.LOG,
-- 'Address Information Not Found : '
-- );
-- END;
IF l_flag = 'Y'
THEN
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'Before Inserting into Supplier Site Interface'
);
INSERT INTO ap_supplier_sites_int
(vendor_site_interface_id,
vendor_site_code,vendor_id, pay_site_flag,purchasing_site_flag,RFQ_ONLY_SITE_FLAG,ATTENTION_AR_FLAG,
address_line1,address_line2, address_line3,city, state,zip,
PROVINCE,AREA_CODE,country,phone,SHIP_TO_LOCATION_ID,BILL_TO_LOCATION_ID,INACTIVE_DATE,fax,FAX_AREA_CODE,telex,
TERMS_DATE_BASIS, VAT_CODE,
prepay_code_combination_id,accts_pay_code_combination_id, PAY_GROUP_LOOKUP_CODE,
PAYMENT_PRIORITY, terms_id,ALWAYS_TAKE_DISC_FLAG,PAY_DATE_BASIS_LOOKUP_CODE,
invoice_currency_code,PAYMENT_CURRENCY_CODE,PAYMENT_METHOD_LOOKUP_CODE,
HOLD_ALL_PAYMENTS_FLAG,HOLD_FUTURE_PAYMENTS_FLAG,HOLD_UNMATCHED_INVOICES_FLAG,
AUTO_TAX_CALC_FLAG,TAX_REPORTING_SITE_FLAG,
--VALIDATION_NUMBER,
EXCLUDE_FREIGHT_FROM_DISCOUNT,
VAT_REGISTRATION_NUM,ADDRESS_LINE4,ADDRESS_STYLE,COUNTY,ALLOW_AWT_FLAG,AWT_GROUP_ID,BANK_CHARGE_BEARER,
PCARD_SITE_FLAG,MATCH_OPTION,COUNTRY_OF_ORIGIN_CODE,CREATE_DEBIT_MEMO_FLAG,OFFSET_TAX_FLAG,EMAIL_ADDRESS
,PRIMARY_PAY_SITE_FLAG,GAPLESS_INV_NUM_FLAG,TOLERANCE_ID--,LOCATION_ID
,party_site_id,--SERVICES_TOLERANCE_ID,
--TCA_SYNC_STATE--,TCA_SYNC_PROVINCE--,TCA_SYNC_COUNTY,--,TCA_SYNC_CITY,--TCA_SYNC_ZIP,--TCA_SYNC_COUNTRY,
--PAY_AWT_GROUP_ID
org_id,operating_unit_name, created_by, creation_date,last_updated_by, last_update_date,
DUNS_NUMBER,SELLING_COMPANY_IDENTIFIER)
VALUES (ap_suppliers_int_s.NEXTVAL,
SUBSTR (TRIM (i.vendor_site_code), 1, 300),l_vendor_id,i.pay_site_flag,i.purchasing_site_flag,i.RFQ_ONLY_SITE_FLAG,i.ATTENTION_AR_FLAG,
i.address_line1,i.address_line2, i.address_line3,SUBSTR (i.city, 1, 250), TRIM (i.state),TRIM (i.zip)
,TRIM (i.province),TRIM (i.area_code),
TRIM (i.country),SUBSTR (TRIM (i.phone), 1, 150), i.SHIP_TO_LOCATION_ID,i.BILL_TO_LOCATION_ID,i.INACTIVE_DATE,i.fax,i.FAX_AREA_CODE,i.telex,
i.TERMS_DATE_BASIS,i.VAT_CODE,
prepay_dist_id,liability_dist_id,i.PAY_GROUP_LOOKUP_CODE,
i.PAYMENT_PRIORITY,l_terms_id,i.ALWAYS_TAKE_DISC_FLAG,i.PAY_DATE_BASIS_LOOKUP_CODE,
l_invoice_currency_code,l_PAYMENT_CURRENCY_CODE,i.PAYMENT_METHOD_LOOKUP_CODE,
i.HOLD_ALL_PAYMENTS_FLAG,i.HOLD_FUTURE_PAYMENTS_FLAG,i.HOLD_UNMATCHED_INVOICES_FLAG,
'Y',i.TAX_REPORTING_SITE_FLAG,
--i.VALIDATION_NUMBER,
i.EXCLUDE_FREIGHT_FROM_DISCOUNT,
i.VAT_REGISTRATION_NUM,i.ADDRESS_LINE4,i.ADDRESS_STYLE,i.COUNTY,i.ALLOW_AWT_FLAG,i.AWT_GROUP_ID,i.BANK_CHARGE_BEARER,
i.PCARD_SITE_FLAG,i.MATCH_OPTION,i.COUNTRY_OF_ORIGIN_CODE,i.CREATE_DEBIT_MEMO_FLAG,i.OFFSET_TAX_FLAG,i.EMAIL_ADDRESS,
i.PRIMARY_PAY_SITE_FLAG,i.GAPLESS_INV_NUM_FLAG,i.TOLERANCE_ID--,i.LOCATION_ID
,i.party_site_id,--i.SERVICES_TOLERANCE_ID,
--i.TCA_SYNC_STATE--,i.TCA_SYNC_PROVINCE--,i.TCA_SYNC_COUNTY--i.TCA_SYNC_CITY--,i.TCA_SYNC_ZIP
--,i.TCA_SYNC_COUNTRY,
--i.PAY_AWT_GROUP_ID
630,'Airtel Zambia OU',1110, SYSDATE,1110, SYSDATE,i.DUNS_NUMBER,i.SELLING_COMPANY_IDENTIFIER);
fnd_file.put_line
(fnd_file.LOG,
'After Inserting into Supplier Site Interface'
);
UPDATE xx_supplier_sites_stg
SET status = 'P'
WHERE ROWID = i.ROWID;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
err_msg :=
err_msg
|| '--'
|| 'ERROR AT INSERT '
|| SQLCODE
|| '--'
|| SQLERRM;
fnd_file.put_line
(fnd_file.LOG,
'Error in Inserting into Supplier Interface '
|| SQLCODE
|| '--'
|| SQLERRM
);
UPDATE xx_supplier_sites_stg
SET status = 'E',
error_msg = err_msg
WHERE ROWID = i.ROWID;
COMMIT;
END;
IF l_flag = 'Y'
THEN
UPDATE xx_supplier_sites_stg
SET status = 'P'
WHERE ROWID = i.ROWID;
COMMIT;
ELSE
UPDATE xx_supplier_sites_stg
SET status = 'E',
error_msg = err_msg
WHERE ROWID = i.ROWID;
COMMIT;
END IF;
ELSE
UPDATE xx_supplier_sites_stg
SET status = 'E',
error_msg = err_msg
WHERE ROWID = i.ROWID;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in Main Loop '
|| SQLCODE
|| ' Error message: '
|| SQLERRM
);
END;
END LOOP;
END SUPPLIERS_SITES_CONV_PRC;
END SUPPLIERS_SITES_CONV_PKG;
/
No comments:
Post a Comment