CREATE OR REPLACE PACKAGE xxmcf_item_tax_pkg
/* ---------------------------------------------------------------------------------------
Program Name xxmcf_item_tax_pkg
TYPE PLSQL Package
Input Parms
Output Parms
Table Access
xxmcf_tax_item --select.insert,update
xxmcf_tax_item _err --select, insert, update
AUTHOR DHANUNJAYA SADHU
DATE 10-FEB-2010
VERSION 1.0
DESCRIPTION This package contains two procedures executed
for item tax inbound EXTRACT related operations.
PROCEDURE xxmcf_read_tax_inbound_file
----- to read USER data from INBOUND RETEK TAX file and
update the custom table xxmcf_------------------------------------------------------------------- ---
FUNCTION get_exempted_tax_codes
---- to return all possible 'exempted tax codes' for the INPUT SKU
and PROVINCE CODE combination
--------------------------------------------------------------------------------------------
DATE AUTHOR VERSION REASON
-----------------------------------------------------------------------------------------
10-FEB-2010 DHANUNJAYA SADHU 1.0 Inital version
-------------------------------------------------------------------------------------------*/
AS
PROCEDURE xxmcf_read_tax_inbound_line;
PROCEDURE get_exempted_tax_codes
(P_SKU IN VARCHAR2,
P_PROVINCE IN VARCHAR2,
X_TAX_CODE OUT VARCHAR2);
END xxmcf_item_tax_pkg;
/
CREATE OR REPLACE PACKAGE BODY xxmcf_item_tax_pkg
/* ---------------------------------------------------------------------------------------
Program Name xxmcf_item_tax_pkg
TYPE PLSQL Package
Input Parms
Output Parms
Table Access
xxmcf_item_tax --select.insert,update
xxmcf_item_tax_err --select, insert, update
AUTHOR DHANUNJAYA SADHU
DATE 10-FEB-2010
VERSION 1.0
DESCRIPTION This package contains two procedures executed
for item tax inbound EXTRACT related operations.
PROCEDURE xxmcf_read_tax_inbound_file
----- to read USER data from INBOUND retek tax file and
update the custom table xxcofi_replacement_user
------------------------------------------------------------------- ---
FUNCTION get_exempted_tax_codes
---- to return all possible 'exempted tax codes' for the INPUT SKU
and PROVINCE CODE combination
--------------------------------------------------------------------------------------------
DATE AUTHOR VERSION REASON
-----------------------------------------------------------------------------------------
10-FEB-2010 DHANUNJAYA SADHU 1.0 Inital version
-------------------------------------------------------------------------------------------*/
AS
PROCEDURE xxmcf_read_tax_inbound_line
AS
l_filehandle UTL_FILE.file_type;
l_newfilehandle UTL_FILE.file_type;
l_newline VARCHAR2(3500);
l_file_dc UTL_FILE.file_type;
l_buff_size NUMBER := 24576;
l_record_count NUMBER := 0;
l_valid_count NUMBER := 0;
l_total_count NUMBER := 0;
l_reject_count NUMBER := 0;
l_violate_count NUMBER := 0;
l_insert_count NUMBER := 0;
l_actual_count NUMBER := 0;
l_update_count NUMBER := 0;
l_top VARCHAR2(200) := NULL;
l_date VARCHAR2(60) := NULL;
l_mode VARCHAR2(5) := NULL;
l_filename VARCHAR2(50);
l_lastline VARCHAR2(100);
--- variables used to insert data into custom table ---
l_line_count NUMBER:=0; --- to get the processed line count---
l_province_string VARCHAR2(100); --- to populate province codes against each record-----
l_tax_string VARCHAR2(100); --- to handle the exempted tax value (0,1) against each record------
l_sku_num VARCHAR2(10); --- to get the SKU number---
l_tax_name VARCHAR2(6); ---- to get the TAX name------
l_file_valid VARCHAR2(6); ---- to check file is valid ----
l_trailer_valid VARCHAR2(6); ---- to check ftrail record is validd----
l_header_valid VARCHAR2(6); ---- to check fheader record is valid-=--
l_count_valid VARCHAR2(6);----- to get the vcount of valid records-----
l_record_valid VARCHAR2(6); ----- to chek record is valid or not---
l_loop_var NUMBER :=1; --- used as a loop variable----
l_error_reason VARCHAR2(200); --- error reason to display/insert into cusotom error table----
l_check_update_count NUMBER:=0;
BEGIN
fnd_profile.get('XXHBCDATA_TOP', l_top);
l_filename := 'rk9864d1';
l_filehandle := UTL_FILE.fopen(l_top || '/' || 'inbound', l_filename, 'R');
l_newfilehandle := UTL_FILE.fopen(l_top || '/' || 'inbound', l_filename, 'R');
DBMS_OUTPUT.PUT_LINE ( '*******************************************************************************');
DBMS_OUTPUT.PUT_LINE ( '-- MCF ITEM TAX INBOUND EXTRACT --');
DBMS_OUTPUT.PUT_LINE ( '*******************************************************************************');
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( 'Program is reading RETEK TAX INBOUND file ' || l_filename || ' from ' || '' || l_top || '/' || 'inbound');
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ( '--- ');
---- to get the FTAIL (last record) from the file
LOOP
BEGIN
UTL_FILE.get_line(l_newfilehandle, l_lastline);
--- to get count of total lines in the INBOUND FILE---
l_record_count := l_record_count + 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ( ' ');
IF (SUBSTR(l_lastline,1,5)<>'FTAIL') THEN
l_lastline:='';
END IF;
EXIT;
END;
END LOOP;
----loOp FOR aLL tHE rEcOrDs in The INBoUnd FILE------
LOOP
BEGIN
UTL_FILE.get_line(l_filehandle, l_newline);
l_line_count:=l_line_count+1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
IF l_line_count = 0
THEN
DBMS_OUTPUT.PUT_LINE ( 'The Specified INBOUND FILE is EMPTY...');
DBMS_OUTPUT.PUT_LINE ( 'NO RECORDS TO BE PROCESSED....');
DBMS_OUTPUT.PUT_LINE ( ' ');
END IF;
EXIT;
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose(l_filehandle);
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( 'THE Specified INBOUND FILE :' || l_filename || ' does not EXIST.');
DBMS_OUTPUT.PUT_LINE ( 'Please make sure that CORRECT file name is passed and file EXISTS in the inbound path :' || l_top || '/'|| 'inbound');
DBMS_OUTPUT.PUT_LINE ( 'Exiting the Program Execution..');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------------------------------------');
END;
--- province string is hardcoded as per the AVAILABLE PROVINCE CODE POSITIONS OF data coming from inbound data file--
l_province_string := 'NSABBCMBNBNFONPEQCSK';
IF l_line_count=1 THEN
/* VALIDATING THE FHEADER RECORD and also the FTRAIL record captured earlier */
IF (SUBSTR(l_newline,1,5) <> 'FHEAD') THEN
l_newline:='';
END IF;
DBMS_OUTPUT.PUT_LINE ( 'FHEADER RECORD IN THE FILE : '||l_newline);
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( 'FTAIL RECORD IN THE FILE : '||l_lastline);
DBMS_OUTPUT.PUT_LINE ( ' --- ');
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '*********************VALIDATION OF THE INBOUND FILE DATA***********************');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '--- ');
IF ( (SUBSTR(l_newline,1,5)='FHEAD')
AND (SUBSTR(l_newline,6,25) LIKE '%MCF_TAX_ITEM%')
AND (LENGTH(l_newline)=70 ) )
THEN
l_header_valid :='TRUE';
DBMS_OUTPUT.PUT_LINE ( 'FHEADER EXISTS AND THE FORMAT IS CORRECT IN THE INBOUND TAX FILE');
--DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------');
DBMS_OUTPUT.PUT_LINE ( '--- ');
ELSE
DBMS_OUTPUT.PUT_LINE ( 'FHEADER FORMAT IS NOT CoRRECT / DOES NOT EXIST IN THE INBOUND TAX FILE');
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( 'FILE IS REJECTED AND EXITING FROM PROGRAM..');
DBMS_OUTPUT.PUT_LINE ( ' --- ');
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------------');
EXIT;
END IF;
IF ( (SUBSTR(l_lastline,1,5)='FTAIL') )
--AND (LENGTH(l_lastline)=70 ) )
THEN
l_trailer_valid :='TRUE';
DBMS_OUTPUT.PUT_LINE ( 'FTRAIL EXISTS AND THE FORMAT IS CORRECT IN THE INBOUND TAX FILE');
DBMS_OUTPUT.PUT_LINE ( ' --- ');
--DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------');
IF (((REPLACE(SUBSTR(l_lastline,6,10),' ',''))/100)*100)= TO_CHAR((l_record_count-2))
THEN l_count_valid :='TRUE';
DBMS_OUTPUT.PUT_LINE ( 'COUNT OF RECORDS IN FILE :'||TO_CHAR((l_record_count-2))||' is equal to RECORD_COUNT in FTRAIL :'||(((REPLACE(SUBSTR(l_lastline,6,10),' ',''))/100)*100));
DBMS_OUTPUT.PUT_LINE ( ' --- ');
--DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------');
ELSE
DBMS_OUTPUT.PUT_LINE ( 'COUNT OF RECORDS IN FILE :'||TO_CHAR((l_record_count-2))||' is NOT equal to RECORD_COUNT in FTRAIL :'||(((REPLACE(SUBSTR(l_lastline,6,10),' ',''))/100)*100));
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( 'FILE IS REJECTED AND EXITING FROM PROGRAM..');
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------------');
EXIT;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE ( 'FTRAIL FORMAT IS NOT CORRECT / DOES NOT EXIST IN THE INBOUND TAX FILE');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( 'FILE IS REJECTED AND EXITING FROM PROGRAM..');
DBMS_OUTPUT.PUT_LINE ( ' --- ');
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------------');
EXIT;
END IF;
END IF;
IF ( l_header_valid ='TRUE' ) AND ( l_trailer_valid ='TRUE') AND ( l_count_valid ='TRUE') THEN
IF (l_line_count>1) AND (SUBSTR(l_newline,1,5) <> 'FTAIL') THEN
/* Not to allow the FHEADER line to get processed */
DBMS_OUTPUT.PUT_LINE ( '*******************************************************************************');
/* assign values of fields in FILE to variables based on position.
these variables will be used to insert data into custaom table..*/
l_total_count := l_total_count + 1;
l_sku_num:= SUBSTR(l_newline,1,8);
l_tax_name:=REPLACE(SUBSTR(l_newline,9,6),' ','');
l_tax_string:=SUBSTR(l_newline,15,10);
IF LENGTH(l_newline)=70 THEN ----Validation of length of each record--------
l_record_valid :='TRUE';
ELSE
l_record_valid :='FALSE';
l_error_reason :=CONCAT('RECORD LENGTH IS NOT EQUAL TO 70 FOR THE SKU : ',l_sku_num);
DBMS_OUTPUT.PUT_LINE ( l_error_reason);
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( 'HENCE,Inserting into ERROR table "xxmcf_tax_item_err" ');
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------------');
END IF;
IF SUBSTR(l_newline,1,8) IS NULL THEN --- validation of SkU number-------
l_error_reason :='SKU is NOT PROVIDED/NULL FOR THE RECORD';
DBMS_OUTPUT.PUT_LINE ( 'HENCE,Inserting into ERROR table "xxmcf_tax_item_err" ');
END IF;
END IF;
IF ( l_total_count >0) AND (SUBSTR(l_newline,1,5) <> 'FTAIL') THEN
-- NOT TO ALLOW FTRAIL AND FHEADER LINE TO BE PROCESSED--
FOR rec IN 1..10 ------ SPLITTING EACH FILE RECORD INTO 10 RECORDS AGAINST EACH PROVINCE------
LOOP
IF l_tax_name IS NULL THEN --- VALIDATION OF TAX NAME----
l_error_reason :=CONCAT('TAX NAME IS NULL FOR THE sku:Province combination ',l_sku_num||':'||SUBSTR(l_province_string,l_loop_var,2));
DBMS_OUTPUT.PUT_LINE ( l_error_reason);
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( 'HENCE,Inserting into ERROR table "xxmcf_tax_item_err" ');
DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------------------------------------------');
END IF;
IF REPLACE(SUBSTR(l_tax_string,rec,1),' ','') IS NULL THEN --- VALIDATION OF TAX VALUE---------
l_error_reason :=CONCAT('TAX VALUE IS NULL FOR THE sku:Province:TAX combination ',l_sku_num||':'||SUBSTR(l_province_string,l_loop_var,2));
DBMS_OUTPUT.PUT_LINE (l_error_reason);
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( 'HENCE,Inserting into ERROR table "xxmcf_tax_item_err" ');
DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------------------------------------------');
END IF;
IF (l_record_valid='TRUE') AND (l_error_reason IS NULL) THEN
---- IF ALL VALIDATIONS ARE PASSED AND RECORD SHOULD BE INSERTED/UPDATED INTO CUSTOM TABLE -------
BEGIN
--- CHECKING Whether record already Exists in the custom table ------
SELECT COUNT(1)
INTO l_check_update_count
FROM
xxmcf_tax_item
WHERE sku_number=l_sku_num
AND province_code=SUBSTR(l_province_string,l_loop_var,2)
AND tax_name=l_tax_name
AND last_update_date<>TRUNC(SYSDATE);
----update the existing record if already exists-----
IF l_check_update_count=1 THEN
DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ( 'RECORD ALREADY EXISTS FOR ');
DBMS_OUTPUT.PUT_LINE ( ' for SKU : Province : Tax Combination '||l_sku_num||' : '||SUBSTR(l_province_string,l_loop_var,2)||' : '||l_tax_name);
DBMS_OUTPUT.PUT_LINE ( '---');
DBMS_OUTPUT.PUT_LINE ( 'HENCE...UPDATING the CUSTOM TABLE xxmcf_tax_item with new tax values');
DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ( ' ');
----- if a record for the sku-TAX-province combination already exists,
----- update the same record with new values
UPDATE
xxmcf_tax_item
SET tax_value=SUBSTR(l_tax_string,rec,1),
last_update_date=TRUNC(SYSDATE)
WHERE sku_number=l_sku_num
AND province_code=SUBSTR(l_province_string,l_loop_var,2)
AND tax_name=l_tax_name;
l_update_count:=l_update_count+1;
END IF;
END;
IF l_check_update_count<>1 THEN
BEGIN
-----INSERT THE RECORD INTO CUSTOM TABLE ----
DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------------------------------------------');
INSERT INTO xxmcf_tax_item
VALUES
( l_sku_num
,SUBSTR(l_province_string,l_loop_var,2)
,l_tax_name
,REPLACE(SUBSTR(l_tax_string,rec,1),' ','')
,TRUNC(SYSDATE)
,TRUNC(SYSDATE));
l_insert_count:=l_insert_count+1;
DBMS_OUTPUT.PUT_LINE ( 'DATA Successfully inserted into CUSTOM TABLE xxmcf_tax_item ');
DBMS_OUTPUT.PUT_LINE ( ' for SKU : Province : Tax Combination '||l_sku_num||' : '||SUBSTR(l_province_string,l_loop_var,2)||' : '||l_tax_name);
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '---');
DBMS_OUTPUT.PUT_LINE ( '-------------------------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE ( 'ERROR WHILE INSERTING DATA into CUSTOM TABLE xxmcf_tax_item ');
--DBMS_OUTPUT.PUT_LINE ( 'FOR SKU : Province : Tax Combination '||l_sku_num||' : '||SUBSTR(l_province_string,l_loop_var,2)||' : '||l_tax_name);
--DBMS_OUTPUT.PUT_LINE ( ' ---cause--- ');
-- DBMS_OUTPUT.PUT_LINE ( SQLERRM);
--DBMS_OUTPUT.PUT_LINE ( ' --- ');
l_error_reason := SQLERRM;
DBMS_OUTPUT.PUT_LINE ( ' ');
BEGIN
SELECT COUNT(1)
INTO l_violate_count
FROM
xxmcf_tax_item
WHERE sku_number=l_sku_num
AND province_code=SUBSTR(l_province_string,l_loop_var,2)
AND tax_name=l_tax_name
AND last_update_date=TRUNC(SYSDATE);
IF l_violate_count=1 THEN
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( 'FOR THE SAME RUN, the SKU-PROVINCE-TAX combination should be unique.');
DBMS_OUTPUT.PUT_LINE ( 'HENCE...INSERTING INTO CUSTOM ERRROR TABLE xxmcf_tax_item_err');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '---');
l_error_reason:=CONCAT('FOR THE SAME RUN DATA :',l_error_reason);
BEGIN
INSERT INTO xxmcf_tax_item_err
VALUES
( l_sku_num
,SUBSTR(l_province_string,l_loop_var,2)
,l_tax_name
,SUBSTR(l_tax_string,rec,1)
,TRUNC(SYSDATE)
,TRUNC(SYSDATE)
,l_error_reason);
l_reject_count:=l_reject_count+1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ( 'ERROR WHILE INSERTING DATA into error table xxmcf_item_tax_err');
DBMS_OUTPUT.PUT_LINE ( 'FOR SKU : Province '||l_sku_num||' : '||SUBSTR(l_province_string,l_loop_var,2));
END;
END IF;
END;
END;
END IF;
ELSE ------ in case of any ERROR ---- record is rejected and populated in error table----
BEGIN
INSERT INTO xxmcf_tax_item_err
VALUES
( l_sku_num
,SUBSTR(l_province_string,l_loop_var,2)
,l_tax_name
,SUBSTR(l_tax_string,rec,1)
,TRUNC(SYSDATE)
,TRUNC(SYSDATE)
,l_error_reason);
l_reject_count:=l_reject_count+1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ( 'ERROR WHILE INSERTING DATA into error table xxmcf_item_tax_err FOR SKU'||l_sku_num);
END;
END IF;
l_loop_var:=l_loop_var+2; ---- to increment the loop variable---
l_error_reason:=''; --- to reassign the error-reason to NULL AGAIN-----
l_check_update_count:=0; -- to get the province code ( 2 digits) for each record----
END LOOP; --- END OF THE INTERNAL LOOP-----
END IF;
END IF;
l_loop_var:=1;
---- to re-assign the loop variable with its initial value
END LOOP; ---- END OF THE MAIN LOOP-----
l_actual_count:=l_record_count-2;
COMMIT;
IF ( l_header_valid ='TRUE' ) AND ( l_trailer_valid ='TRUE') AND ( l_count_valid ='TRUE') THEN
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '*******************************************************************************');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( 'NUMBER of RECORDS in PRESENT RUN : ' || l_record_count);
DBMS_OUTPUT.PUT_LINE ( 'Actual NUMBER OF RECORDS Excluding FHEADER AND FTRAIL : ' || l_actual_count);
DBMS_OUTPUT.PUT_LINE ( 'NO.of PROVINCES whose DATA is INTERFACED THROUGH EACH RECORD : ' ||'10');
l_actual_count:=l_actual_count*10;
DBMS_OUTPUT.PUT_LINE ( '---');
DBMS_OUTPUT.PUT_LINE ( ' TOTAL NUMBER OF RECORDS PROCESSED : ' || l_actual_count);
DBMS_OUTPUT.PUT_LINE ( '--- ');
DBMS_OUTPUT.PUT_LINE ( 'NUMBER of NEW RECORDS INSERTED into CUSTOM TABLE xxmcf_tax_item : ' || l_insert_count);
DBMS_OUTPUT.PUT_LINE ( 'NUMBER of NEW RECORDS INSERTED into ERROR TABLE xxmcf_tax_item_err : ' || l_reject_count);
DBMS_OUTPUT.PUT_LINE ( 'NUMBER of CHANGED/UPDATED RECORDS in CUSTOM TABLE xxmcf_tax_item : ' || l_update_count);
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '*******************************************************************************');
END IF;
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose(l_filehandle);
DBMS_OUTPUT.PUT_LINE ( 'Invalid Path for file operation');
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose(l_filehandle);
DBMS_OUTPUT.PUT_LINE ( 'Invalid Mode for file operation');
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose(l_filehandle);
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ( ' ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE ( 'THE Specified INBOUND FILE :' || l_filename || ' does not EXIST.');
DBMS_OUTPUT.PUT_LINE ( 'Please make sure that CORRECT file name is passed and file EXISTS in the inbound path..' || l_top || '/' || 'inbound' );
DBMS_OUTPUT.PUT_LINE ( 'Exiting the Program Execution..');
DBMS_OUTPUT.PUT_LINE ( ' ');
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------------------------------------');
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.fclose(l_filehandle);
DBMS_OUTPUT.PUT_LINE ( 'Invalid Filehandle');
WHEN UTL_FILE.read_error
THEN
UTL_FILE.fclose(l_filehandle);
DBMS_OUTPUT.PUT_LINE ( 'Error while reading a file');
WHEN UTL_FILE.internal_error
THEN
UTL_FILE.fclose(l_filehandle);
DBMS_OUTPUT.PUT_LINE ( 'Internal Error while file operation');
WHEN OTHERS
THEN
UTL_FILE.fclose(l_filehandle);
-- fnd_file.put_line (1, '***********WHEN OTHERS EXCEPTION Raised***********');
DBMS_OUTPUT.PUT_LINE ( 'Error occurred ' || ' The oracle error message is : ' || TO_CHAR(SQLCODE) || SQLERRM);
END xxmcf_read_tax_inbound_line;
----------------------------------
----------------------------------
PROCEDURE get_exempted_tax_codes
(P_SKU IN VARCHAR2,
P_PROVINCE IN VARCHAR2,
X_TAX_CODE OUT VARCHAR2)
IS
CURSOR cur_tax IS
SELECT DISTINCT TAX_NAME
FROM xxmcf_tax_item
WHERE ((sku_number=P_SKU
AND province_code=P_PROVINCE
AND tax_value=0)
OR (
sku_number<>P_SKU))
AND TAX_NAME NOT IN ( SELECT TAX_NAME FROM xxmcf_tax_item
WHERE sku_number=P_SKU
AND province_code=P_PROVINCE
AND tax_value<>0);
l_tax_count NUMBER;
l_tax VARCHAR2(100);
l_tax_string VARCHAR2(100):='';
BEGIN
/* for testing */
insert into
xxcofi_message_broker_test(Inparam1,Inparam2)
values(P_SKU,P_PROVINCE);
/* end*/
---- to check whether any TAX RECORD exists for SKU AND PROVINCE Combination--------
BEGIN
SELECT TAX_NAME
INTO l_tax
FROM xxmcf_tax_item
WHERE sku_number=P_SKU
AND province_code=P_PROVINCE
aNd rowNum<2;
EXCEPTION --- NO DATA FOUND, SEND ERROR MESSAGE AS RETURN PARAM-------
WHEN NO_DATA_FOUND THEN
l_tax:='NO DATA FOUND';
X_TAX_CODE:= l_tax;
RETURN;
END;
---- to check whether any EXEMPTED TAX RECORD exists for SKU AND PROVINCE Combination--------
BEGIN
SELECT TAX_NAME
INTO l_tax
FROM xxmcf_tax_item
WHERE ((sku_number=P_SKU
AND province_code=P_PROVINCE
AND tax_value=0)
OR (
sku_number<>P_SKU))
aNd rowNum<2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_tax:=' ';
X_TAX_CODE:= l_tax;
RETURN;
END;
--- to send ',' separated String of all exempted taxes for SKU AND PROVINCE Combination----
FOR rec in cur_tax
LOOP
l_tax_string:= CONCAT(l_tax_string,',');
l_tax_string:= CONCAT(l_tax_string,rec.TAX_NAME);
END LOOP;
X_TAX_CODE:= SUBSTR(l_tax_string,2);
END get_exempted_tax_codes;
END xxmcf_item_tax_pkg;
/
No comments:
Post a Comment