/*-------------------------------------------------------------------------------
|| PROGRAM: Read and load FACS-ALIS credit card records
|| AUTHOR: Michael R. Weber
|| CREATED: August 27th, 2007
|| PURPOSE: This procedure reads FACS tables and loads the CCRR table.
||REVISED:
*/-------------------------------------------------------------------------------
PROCEDURE load_ccrr_FNC
IS
/* File processing variables */
v_ccrr_rec_in_count NUMBER := 0;
v_ccrr_rec_out_count NUMBER := 0;
alis_error EXCEPTION;
field_length_error EXCEPTION;
ft_is_null EXCEPTION;
/* record building variables */
v_app_firm_cd CHAR(1);
/* Program variables */
/* constants */
c_date_time CONSTANT DATE := SYSDATE;
/* variables */
v_person_firm_id CHAR(1) := NULL;
/* record building constants */
c_one_space CONSTANT CHAR(1) := ' ';
c_fourty_spaces CONSTANT CHAR(40) := ' ';
/* cursor declaration */
/*
|| This cursor will use an ALIS view containing the proper app_status,
|| payment_type, and app_service_type codes. SEL * FROM closeout, alis_view
|| where PID matches AND pull_date IS NULL AND status = 'P'.
*/
CURSOR getfee_cur IS
SELECT CLOSE.doi_application_id CLOSE_PID,
ALIS.fee_detail_line_number ALIS_LINE_NUM,
ALIS.application_src,
CLOSE.proc_file_seq_nbr CLOSE_FSN,
ALIS.trans_processing_src,
CLOSE.total_application_services_amt CLOSE_TOTAL_APP_AMT,
ALIS.total_ticket_amt,
ALIS.trans_auth_date_time,
CLOSE.processor_transaction_number CLOSE_PROC_TRANS_NUM,
ALIS.confirmation_number,
CLOSE.payment_type_an,
CLOSE.cardholder_nm REMITTER_NAME,
ALIS.licensee_ssn_fein,
SUBSTR(ALIS.applicant_long_name, 1, 40) ALIS_APPLICANT_NAME,
ALIS.svc_type_cd,
CLOSE.lic_tycl_svc_cd1,
ALIS.license_tycl REVENUE_TYCL,
ALIS.county_code,
CLOSE.fee_type_ind,
ALIS.fee_charged_amt,
ALIS.revenue_fee_type REVENUE_FT,
ALIS.receipt_record_pulled_dt
FROM [highlight]ialis_view_fccs ALIS, [/highlight]
closeout_detail CLOSE
WHERE ALIS.payment_invoice_id = CLOSE.doi_application_id(+)
AND CLOSE.status_flag IN('P')
AND ALIS.receipt_record_pulled_dt IS NULL
ORDER BY CLOSE.doi_application_id, alis.fee_detail_line_number;
-- ******************* Start Program ********************
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SAVEPOINT begining_of_proc;
/*
|| Test and load all the cursor rows to the CCRR table.
|| If an exception is encountered, quit loading and rollback all changes.
*/
FOR v_getfee IN getfee_cur LOOP
BEGIN
v_ccrr_rec_in_count := v_ccrr_rec_in_count + 1;
v_person_firm_id := person_firm(v_getfee.licensee_ssn_fein);
IF v_person_firm_id IS NULL THEN
DBMS_OUTPUT.put_line('Warning! There was no person/firm value for PID '
|| v_getfee.close_pid || ', line ' || v_getfee.alis_line_num);
END IF;
IF LENGTH(v_getfee.licensee_ssn_fein) > 12 THEN
DBMS_OUTPUT.put_line('Error: SSN/FEIN is too long in record ' || v_getfee.close_pid);
RAISE field_length_error;
END IF;
IF v_getfee.alis_applicant_name IS NULL THEN
DBMS_OUTPUT.put_line('Warning! There was no applicant name for PID '
|| v_getfee.close_pid || ', line ' || v_getfee.alis_line_num
|| ' -- Defaulting to spaces');
v_getfee.alis_applicant_name := c_fourty_spaces;
END IF;
IF v_getfee.lic_tycl_svc_cd1 IS NULL THEN
v_getfee.lic_tycl_svc_cd1 := c_one_space;
END IF;
IF v_getfee.svc_type_cd = '70' AND v_getfee.revenue_ft = 'J' THEN
v_getfee.revenue_tycl := '7100';
END IF;
IF v_getfee.svc_type_cd = '71' AND v_getfee.revenue_ft = 'J' THEN
v_getfee.revenue_tycl := '7101';
END IF;
IF v_getfee.county_code IS NULL THEN
v_getfee.county_code := '00';
END IF;
IF v_getfee.revenue_tycl = '3707' AND v_getfee.revenue_ft = 'L' THEN
v_getfee.revenue_tycl := '3706';
END IF;
IF v_getfee.revenue_ft IS NULL THEN
RAISE ft_is_null;
END IF;
IF v_getfee.revenue_ft IN('J', 'L', 'T')
AND v_getfee.county_code != '00' THEN
v_getfee.county_code := '00';
END IF;
IF v_getfee.revenue_ft IN('B', 'C') AND v_getfee.county_code = '00' THEN
DBMS_OUTPUT.put_line('Warning! Fee type ' || v_getfee.revenue_ft
|| ' has county code 00 for PID ' || v_getfee.close_pid
|| ', line ' || v_getfee.alis_line_num);
v_getfee.county_code := '00';
END IF;
/*
|| INSERT the record to the CCRR. We do not set receipt number and
|| abstract until after the batch is balanced.
*/
INSERT INTO credit_card_records_received
(doi_application_id,
[...])
VALUES (v_getfee.close_pid,
[...]
v_getfee.county_code);
alis_rdr_pkg_fccs.update_receipt_pulled_date(v_getfee.close_pid,
c_date_time,
c_doi_id,
v_error_number,
v_error_message);
IF v_error_message != 0 THEN
RAISE alis_error;
END IF;
v_ccrr_rec_out_count := v_ccrr_rec_out_count + 1;
EXCEPTION
WHEN alis_error THEN
DBMS_OUTPUT.put_line('Error: Unable to update ' || v_getfee.close_pid
|| ' ALIS: ' || v_error_message);
RAISE NO_DATA_FOUND;
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.put_line('Error: Duplicate ID_INVOICE_PID encountered: '
|| v_getfee.close_pid);
RAISE NO_DATA_FOUND;
WHEN field_length_error THEN
RAISE NO_DATA_FOUND;
WHEN ft_is_null THEN
DBMS_OUTPUT.put_line('Error: No fee type code for PID '
|| v_getfee.close_pid || ', line ' || v_getfee.alis_line_num);
RAISE NO_DATA_FOUND;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('There were no credit card transactions to process');
ROLLBACK TO begining_of_proc;
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.put_line('Error: Data value error in record ' || v_getfee.close_pid);
RAISE NO_DATA_FOUND;
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line(v_ccrr_rec_in_count || ' records read');
DBMS_OUTPUT.put_line(v_ccrr_rec_out_count || ' records written');
IF v_ccrr_rec_in_count != v_ccrr_rec_out_count THEN
DBMS_OUTPUT.put_line('Warning! Not all records were processed!');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('UPDATES NOT SAVED!');
ROLLBACK TO begining_of_proc;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
DBMS_OUTPUT.put_line('UPDATES NOT SAVED!');
ROLLBACK TO begining_of_proc;
END load_ccrr_FNC;
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa