CREATE OR REPLACE PACKAGE BODY APPS.XXDC_NOV
IS
--
-- This Package is for Coverting data from NOV Ticket and Violation Code files to Transactions, Lines and Receipts..
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ------ ------------------------------------------
FUNCTION get_constant_val (p_constant_name IN VARCHAR2)
RETURN VARCHAR2
IS
v_value VARCHAR2 (1000);
BEGIN
EXECUTE IMMEDIATE 'BEGIN :x := '
|| gc_pkg
|| '.'
|| p_constant_name
|| '; end;'
USING OUT v_value;
RETURN v_value;
END;
PROCEDURE write_log (p_debug IN VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_debug);
END;
PROCEDURE sync_status (p_request_id IN NUMBER, p_retcode OUT VARCHAR2)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.SYNC_STATUS';
CURSOR get_err_tickets_cur (v_req_id IN NUMBER)
IS
SELECT *
FROM xxdc.xxdc_nov_ticket
WHERE request_id = NVL (v_req_id, request_id)
AND process_status IN ('RS', 'RD');
CURSOR get_err_violcode_cur (v_req_id IN NUMBER)
IS
SELECT *
FROM xxdc.xxdc_nov_violcode
WHERE request_id = NVL (v_req_id, request_id)
AND process_status IN ('RS', 'RD');
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
p_retcode := 0;
FOR get_err_tickets_rec IN get_err_tickets_cur (p_request_id)
LOOP
UPDATE xxdc_nov_violcode nv
SET nv.process_status =
DECODE (nv.process_status,
'RS', 'RS',
'RD', 'RD',
get_err_tickets_rec.process_status
),
nv.last_updated_by = fnd_global.user_id,
nv.last_update_date = SYSDATE
WHERE nv.violation_number = get_err_tickets_rec.violation_number
AND nv.request_id = NVL (p_request_id, nv.request_id);
END LOOP;
FOR get_err_violcode_rec IN get_err_violcode_cur (p_request_id)
LOOP
UPDATE xxdc_nov_ticket nt
SET nt.process_status =
DECODE (nt.process_status,
'RS', 'RS',
'RD', 'RD',
get_err_violcode_rec.process_status
),
nt.last_updated_by = fnd_global.user_id,
nt.last_update_date = SYSDATE
WHERE NVL (nt.violation_number, -1) =
get_err_violcode_rec.violation_number
AND nt.request_id = NVL (p_request_id, nt.request_id);
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
p_retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END;
PROCEDURE prevalidation (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_debug IN VARCHAR2 DEFAULT 'Y',
p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.PREVALIDATION';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
retcode := 0;
--xxdc_p2_conv_util.gen_pre_validation (gc_conv_id, p_request_id);
xxdc_p2_conv_util.prevalidation (p_conv_id => gc_conv_id,
p_request_id => p_request_id,
p_log => gv_debug_log_clob,
p_result => retcode
);
xxdc_p2_conv_util.gen_datatype_validation (gc_conv_id, p_request_id);
sync_status (p_request_id, retcode);
UPDATE xxdc_nov_ticket nt
SET nt.last_updated_by = fnd_global.user_id,
nt.last_update_date = SYSDATE,
nt.process_status =
DECODE (nt.process_status,
gc_new_status, gc_pending_status,
nt.process_status
);
UPDATE xxdc_nov_violcode nv
SET nv.last_updated_by = fnd_global.user_id,
nv.last_update_date = SYSDATE,
nv.process_status =
DECODE (nv.process_status,
gc_new_status, gc_pending_status,
nv.process_status
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
END prevalidation;
PROCEDURE convert_data (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_debug IN VARCHAR2 DEFAULT 'Y',
p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.CONVERT_DATA';
v_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
v_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
v_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
v_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
v_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
v_header_rec ar_invoice_api_pub.trx_header_rec_type;
v_line_rec ar_invoice_api_pub.trx_line_rec_type;
v_batch_source_id ra_customer_trx_all.batch_source_id%TYPE;
v_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%TYPE;
v_cust_account_id ra_customer_trx_all.bill_to_customer_id%TYPE;
v_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
v_term_id ra_customer_trx_all.term_id%TYPE;
v_violation_number ra_customer_trx_all.trx_number%TYPE;
v_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
v_memo_line_id ra_customer_trx_lines_all.memo_line_id%TYPE;
v_inventory_item_id ra_customer_trx_lines_all.inventory_item_id%TYPE;
v_receipt_method_id ar_cash_receipts_all.receipt_method_id%TYPE;
v_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
v_nov_trx_hdr_id NUMBER;
v_nov_trx_line_id NUMBER;
v_row_cnt INTEGER := 0;
v_person_id per_all_people_f.person_id%TYPE;
v_msg_index_out NUMBER;
v_issue_date DATE;
v_chr_issue_dt VARCHAR2 (25);
v_chr_dt_att3 VARCHAR2 (25);
v_party_id hz_parties.party_id%TYPE;
v_party_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
CURSOR get_ticket_data_cur
IS
SELECT *
FROM xxdc_nov_ticket
WHERE process_status IN ('P', 'PW', 'E')
-- AND ROWNUM < 2
AND request_id = NVL (p_request_id, request_id);
CURSOR get_violcode_data_cur (v_viol_number IN VARCHAR2)
IS
SELECT *
FROM xxdc_nov_violcode
WHERE violation_number = v_viol_number
AND request_id = NVL (p_request_id, request_id);
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
retcode := '0';
SELECT batch_source_id
INTO v_batch_source_id
FROM ra_batch_sources_all
WHERE UPPER (NAME) = UPPER (gc_batch_source_name);
gv_process_msg := 'Batch Source Id - ' || v_batch_source_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT cust_trx_type_id
INTO v_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE UPPER (NAME) = UPPER (gc_cust_trx_type);
gv_process_msg := 'Customer Trx Type Id - ' || v_cust_trx_type_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT term_id
INTO v_term_id
FROM ra_terms
WHERE UPPER (NAME) = UPPER (gc_term_name);
gv_process_msg := 'Payment Term Id - ' || v_term_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT memo_line_id
INTO v_memo_line_id
FROM ar_memo_lines_all_vl
WHERE UPPER (NAME) = UPPER (gc_memo_line_name);
gv_process_msg := 'v_memo_line_id - ' || v_memo_line_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT receipt_method_id
INTO v_receipt_method_id
FROM ar_receipt_methods
WHERE UPPER (NAME) = UPPER (gc_receipt_method);
gv_process_msg := 'v_receipt_method_id - ' || v_receipt_method_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
FOR get_ticket_data_rec IN get_ticket_data_cur
LOOP
/*
* Resetting the variables.
*/
v_cust_account_id := NULL;
v_site_use_id := NULL;
v_person_id := NULL;
v_header_rec := NULL;
v_line_rec := NULL;
v_batch_source_rec := NULL;
v_customer_trx_id := NULL;
v_cash_receipt_id := NULL;
v_violation_number := NULL;
v_header_tbl.DELETE;
v_lines_tbl.DELETE;
v_dist_tbl.DELETE;
v_salescredits_tbl.DELETE;
/*
* Savepoint for roll back.
*/
SAVEPOINT conv_record;
/*
* Get Customer info for the invoice.
*/
xxdc_p2_conv_util.get_bill_to_acct_site_use_id
(p_caller => v_prog_unit,
p_legacy_acct_num => get_ticket_data_rec.legacy_account_number,
p_bill_to_party_id => v_party_id,
p_cust_acct_id => v_cust_account_id,
p_bill_to_site_id => v_site_use_id,
p_bill_to_site_use_id => v_party_site_use_id
);
/*
* Get Employee number for DFF.
*/
v_person_id :=
xxdc_p2_conv_util.get_person_id
(v_prog_unit,
get_ticket_data_rec.employee_number
);
/*
* Setting warning flag to 'Y' to update the process_status as 'W"
*/
gv_warning_flag := NULL;
IF (v_person_id IS NULL)
THEN
gv_warning_flag := 'Y';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'The employee was not found for '
|| get_ticket_data_rec.violation_number
|| ' for '
|| get_ticket_data_rec.employee_number
);
END IF;
/*
* sequence for trx hdr id.
*/
SELECT xxdc_nov_trx_hdr_s.NEXTVAL
INTO v_nov_trx_hdr_id
FROM DUAL;
v_violation_number := get_ticket_data_rec.violation_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_nov_trx_hdr_id - '
|| v_nov_trx_hdr_id
|| 'v_cust_account_id - '
|| v_cust_account_id
|| 'v_site_use_id - '
|| v_site_use_id
|| 'v_person_id - '
|| v_person_id
|| 'v_violation_number - '
|| v_violation_number,
p_prog_unit => v_prog_unit
);
/*
* Loading Batch Source Info...
*/
v_batch_source_rec.batch_source_id := v_batch_source_id;
/*
* Loading Header Information for invoice..
*/
v_header_rec.trx_header_id := v_nov_trx_hdr_id;
v_header_rec.trx_number :=
'CN'
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY')
|| get_ticket_data_rec.violation_number;
v_header_rec.trx_date :=
NVL (TO_DATE (get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format
),
SYSDATE
);
v_header_rec.trx_currency := gc_currency_code;
v_header_rec.cust_trx_type_id := v_cust_trx_type_id;
v_header_rec.bill_to_customer_id := v_cust_account_id;
v_header_rec.bill_to_site_use_id := v_site_use_id;
v_header_rec.sold_to_customer_id := v_cust_account_id;
v_header_rec.term_id := v_term_id;
v_header_rec.attribute_category := gc_trx_hdr_dff_ctxt;
--Ticket Number
v_header_rec.attribute1 :=
'CN' || get_ticket_data_rec.violation_number;
-- Name of the Inspecter.
v_header_rec.attribute2 := v_person_id;
-- Ticket Date
v_chr_issue_dt := get_ticket_data_rec.ticket_issue_date;
v_issue_date := TO_DATE (v_chr_issue_dt, xxd_utils.gc_dor_dt_format);
v_chr_dt_att3 :=
TO_CHAR (v_issue_date, xxd_utils.gc_fnd_std_dt_format);
v_header_rec.attribute3 := v_chr_dt_att3;
/*v_header_rec.attribute3 :=
TO_CHAR
(TO_DATE (get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format --'MM/DD/YYYY HH24:MI:SS'
),
xxd_utils.gc_fnd_std_dt_format
);
*/
--case comments
v_header_rec.attribute5 := get_ticket_data_rec.case_comment;
v_header_tbl (1) := v_header_rec;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'After setting header table - v_header_tbl - '
|| v_header_tbl.COUNT,
p_prog_unit => v_prog_unit
);
/*
* Violation Code table loop
*/
FOR get_violcode_data_rec IN
get_violcode_data_cur (get_ticket_data_rec.violation_number)
LOOP
/*
* Loading the standard memo line...
*/
IF (v_row_cnt = 0)
THEN
/*
* sequence for trx line id.
*/
SELECT xxdc_nov_trx_line_s.NEXTVAL
INTO v_nov_trx_line_id
FROM DUAL;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'v_row_cnt - '
|| v_row_cnt
|| 'v_nov_trx_line_id - '
|| v_nov_trx_line_id
|| ' trx date - '
|| TO_DATE
(get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format
)
|| 'Date for att 3 - '
|| v_chr_dt_att3,
p_prog_unit => v_prog_unit
);
v_row_cnt := v_row_cnt + 1;
v_line_rec.trx_header_id := v_nov_trx_hdr_id;
v_line_rec.trx_line_id := v_nov_trx_line_id;
v_line_rec.line_number := v_row_cnt;
v_line_rec.quantity_invoiced :=
get_ticket_data_rec.original_fine_amount;
v_line_rec.line_type := gc_line_type;
v_line_rec.memo_line_id := v_memo_line_id;
v_line_rec.amount := get_ticket_data_rec.original_fine_amount;
v_lines_tbl (v_row_cnt) := v_line_rec;
v_line_rec := NULL;
END IF;
/*
* sequence for trx line id.
*/
SELECT xxdc_nov_trx_line_s.NEXTVAL
INTO v_nov_trx_line_id
FROM DUAL;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_row_cnt - '
|| v_row_cnt
|| 'v_nov_trx_line_id - '
|| v_nov_trx_line_id,
p_prog_unit => v_prog_unit
);
/*
* Query for item id for violation code.
*/
v_inventory_item_id :=
xxdc_p2_conv_util.get_inventory_item_id
(v_prog_unit,
get_violcode_data_rec.violation_code
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_inventory_item_id - '
|| v_inventory_item_id,
p_prog_unit => v_prog_unit
);
v_row_cnt := v_row_cnt + 1;
v_line_rec.trx_header_id := v_nov_trx_hdr_id;
v_line_rec.trx_line_id := v_nov_trx_line_id;
v_line_rec.line_number := v_row_cnt;
v_line_rec.inventory_item_id := v_inventory_item_id;
v_line_rec.quantity_invoiced := 1;
v_line_rec.unit_selling_price := 0;
v_line_rec.line_type := gc_line_type;
v_line_rec.interface_line_context := gc_trx_line_dff_ctxt;
v_line_rec.interface_line_attribute1 :=
'CN' || get_violcode_data_rec.legacy_id;
-- As amount is captured under standard memo line.
v_line_rec.amount := 0;
v_lines_tbl (v_row_cnt) := v_line_rec;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'After setting line table '
|| v_lines_tbl.COUNT (),
p_prog_unit => v_prog_unit
);
v_row_cnt := 0;
gv_return_status := NULL;
gv_return_msg := NULL;
create_invoice (p_batch_source_rec => v_batch_source_rec,
p_header_tbl => v_header_tbl,
p_lines_tbl => v_lines_tbl,
p_dist_tbl => v_dist_tbl,
p_salescredits_tbl => v_salescredits_tbl,
x_customer_trx_id => v_customer_trx_id,
x_return_status => gv_return_status,
x_return_msg => gv_return_msg
);
/*
* Continue creating the receipt only if the invoice is created successfully and
* trx_id is successfully created.
*/
IF (gv_return_status = fnd_api.g_ret_sts_success)
AND (v_customer_trx_id IS NOT NULL)
THEN
gv_process_msg :=
'Invoice created for NOV violation number: '
|| get_ticket_data_rec.violation_number
|| ' customer_trx_id : '
|| v_customer_trx_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
/*
* Checking if difference between original fine amount and total collected <> 0 and
* Total collected is 0 then adjustment has to be created not the receipt and is manual
* process.
*
* Checking if difference between original fine amount and total collected <> 0 and
* Total collected is not 0 then need a receipt equal to the collected amount and
* credit memo for the difference manually.
*
* So we are not creating receipts for these two conditions.
*/
IF NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
AND NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
THEN
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
create_receipt
(p_payment_amount => get_ticket_data_rec.original_fine_amount,
p_customer_id => v_cust_account_id,
p_site_use_id => v_site_use_id,
p_receipt_number => 'CN'
|| TO_CHAR (SYSDATE,
'DD-MON-YYYY'
)
|| get_ticket_data_rec.violation_number,
p_receipt_date => SYSDATE,
p_receipt_method_id => v_receipt_method_id,
p_receipt_currency_code => gc_currency_code,
x_cash_receipt_id => v_cash_receipt_id,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
/*
* Continue applying the receipt on invoice if the receipt is created successfully and
* cash_receipt_id is successfully created.
*/
IF (gv_return_status = fnd_api.g_ret_sts_success)
AND (v_cash_receipt_id IS NOT NULL)
THEN
gv_process_msg :=
'Receipt created for NOV violation number: '
|| get_ticket_data_rec.violation_number
|| ' cash_receipt_id : '
|| v_cash_receipt_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
apply_receipt
(p_customer_id => v_cust_account_id,
p_site_use_id => v_site_use_id,
p_cash_receipt_id => v_cash_receipt_id,
p_cust_trx_id => v_customer_trx_id,
p_trx_amt => get_ticket_data_rec.original_fine_amount,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
IF (gv_return_status <> fnd_api.g_ret_sts_success)
THEN
ROLLBACK TO conv_record;
retcode := '1';
IF (NVL (gv_msg_count, 0) > 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count,
gv_concat_msg
);
END IF;
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when applying receipt for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_concat_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
ELSE
/*
* Else loop for failure of apply_receipt procedure..
*/
gv_process_msg :=
'Receipt was successfully applied on invoice for NOV violation number: '
|| get_ticket_data_rec.violation_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
/*
* Invoice was Created, Receipt was created and it was applied successfully. So updating the record in staging tables to 'S'
*/
gv_process_status := NULL;
IF (gv_warning_flag = 'Y')
THEN
gv_process_status := gc_warning_status;
ELSE
gv_process_status := gc_success_status;
END IF;
UPDATE xxdc_nov_ticket
SET process_status = gv_process_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number =
get_ticket_data_rec.violation_number;
UPDATE xxdc_nov_violcode
SET process_status = gv_process_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number =
get_ticket_data_rec.violation_number;
END IF;
ELSE
/*
* Else loop for failure of create_receipt procedure..
*/
ROLLBACK TO conv_record;
retcode := '1';
IF (NVL (gv_msg_count, 0) > 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count,
gv_concat_msg
);
END IF;
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when creating receipt for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_concat_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
END IF;
ELSE
/*
* Else Loop for
IF NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
AND NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
*/
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'Created Invoice but user has to manually create adjustment',
p_prog_unit => v_prog_unit
);
IF ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0) <> 0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
THEN
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'Need an adjustment/Credit Memo created to reflect the adjustment in the amount of '
|| ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0)
)
);
ELSIF ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0) <> 0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
THEN
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'Need an receipt equal to the collected amount and credit memo created for the amount of '
|| ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0)
)
);
END IF;
/*
* Invoice was Created successfully but receipt was not created and applied.
* This is because the amounts are not same and it is a manual process.
* So updating the record in staging tables to 'W'
*/
UPDATE xxdc_nov_ticket
SET process_status = gc_warning_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number = get_ticket_data_rec.violation_number;
UPDATE xxdc_nov_violcode
SET process_status = gc_warning_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number = get_ticket_data_rec.violation_number;
END IF;
ELSE
/*
* Else loop for failure of create_invoice procedure..
*/
ROLLBACK TO conv_record;
retcode := '1';
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when creating invoice for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_return_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
--commit;
END IF;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO conv_record;
retcode := '2';
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_exp_rule_id,
p_request_id,
v_violation_number,
SQLCODE || ' - ' || SQLERRM
);
END convert_data;
PROCEDURE create_invoice (
p_batch_source_rec IN ar_invoice_api_pub.batch_source_rec_type
DEFAULT NULL,
p_header_tbl IN ar_invoice_api_pub.trx_header_tbl_type,
p_lines_tbl IN ar_invoice_api_pub.trx_line_tbl_type,
p_dist_tbl IN ar_invoice_api_pub.trx_dist_tbl_type,
p_salescredits_tbl IN ar_invoice_api_pub.trx_salescredits_tbl_type,
x_customer_trx_id OUT NUMBER,
x_return_status OUT VARCHAR2,
x_return_msg OUT VARCHAR2
)
IS
v_cnt INTEGER := 0;
v_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE := NULL;
v_trx_number ra_customer_trx_all.trx_number%TYPE := NULL;
v_api_ret_msg VARCHAR2 (2000) := NULL;
v_concat_msg VARCHAR2 (2000) := NULL;
v_table_err_msg VARCHAR2 (2000) := NULL;
v_err_msg VARCHAR2 (2000);
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_INVOICE';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
--fnd_msg_pub.initialize;
--fnd_msg_pub.delete_msg;
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
ar_invoice_api_pub.create_single_invoice
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_batch_source_rec => p_batch_source_rec,
p_trx_header_tbl => p_header_tbl,
p_trx_lines_tbl => p_lines_tbl,
p_trx_dist_tbl => p_dist_tbl,
p_trx_salescredits_tbl => p_salescredits_tbl,
--,p_trx_contingencies_tbl IN trx_contingencies_tbl_type,
x_customer_trx_id => v_customer_trx_id,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'After calling create_single_invoice api'
|| ' x_return_status - '
|| gv_return_status
|| ' x_msg_count - '
|| gv_msg_count
|| ' x_msg_data - '
|| gv_msg_data,
p_prog_unit => v_prog_unit
);
v_api_ret_msg :=
'API Return Status: ['
|| gv_return_status
|| ']'
|| ' Msg Count: ['
|| gv_msg_count
|| ']'
|| ' Msg Data: ['
|| gv_msg_data
|| ']'
|| ' CustTrxId: ['
|| v_customer_trx_id
|| ']';
IF (NVL (gv_return_status, fnd_api.g_ret_sts_error) !=
fnd_api.g_ret_sts_success
)
OR (NVL (gv_msg_count, 0) > 0)
OR (NVL (v_customer_trx_id, 0) = 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count, gv_concat_msg);
END IF;
FOR err IN (SELECT *
FROM ar_trx_errors_gt
WHERE trx_header_id = p_header_tbl (1).trx_header_id)
LOOP
v_cnt := v_cnt + 1;
IF err.trx_line_id IS NOT NULL
THEN
v_table_err_msg :=
NVL (v_table_err_msg, ' ')
|| 'trx_line_id=['
|| err.trx_line_id
|| ']';
END IF;
v_table_err_msg :=
NVL (v_table_err_msg, ' ')
|| 'errmsg=['
|| SUBSTR (err.error_message, 1, 200)
|| ']invval=['
|| err.invalid_value
|| ']';
END LOOP;
IF NVL (gv_return_status, fnd_api.g_ret_sts_error) =
fnd_api.g_ret_sts_success
AND v_cnt = 0
AND NVL (v_customer_trx_id, 0) > 0
THEN
BEGIN
SELECT trx_number
INTO v_trx_number
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = v_customer_trx_id;
EXCEPTION
WHEN OTHERS
THEN
v_err_msg :=
'Fatal error - Get trx_number from ra_customer_trx_all failed - Msg:'
|| SUBSTR (SQLERRM, 1, 100);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END;
x_customer_trx_id := v_customer_trx_id;
x_return_status := fnd_api.g_ret_sts_success;
ELSE
v_err_msg :=
SUBSTR ( 'Invoice API Error - <'
|| NVL (v_api_ret_msg, ' ')
|| '>'
|| ':Concat Msg: <'
|| NVL (v_concat_msg, ' ')
|| '>'
|| ':Ar_trx_errors_gt Msg:<'
|| NVL (v_table_err_msg, ' ')
|| '>',
1,
2000
);
v_customer_trx_id := NULL;
x_return_msg := v_err_msg;
x_return_status := gv_return_status;
END IF;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
v_customer_trx_id := NULL;
x_return_msg :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END create_invoice;
PROCEDURE create_receipt (
p_payment_amount IN NUMBER,
p_customer_id IN NUMBER,
p_site_use_id IN NUMBER,
p_receipt_number IN VARCHAR2,
p_receipt_date IN DATE DEFAULT TRUNC (SYSDATE),
p_receipt_method_id IN NUMBER,
p_receipt_currency_code IN VARCHAR2,
x_cash_receipt_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_RECEIPT';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_receipt_number => p_receipt_number,
p_amount => p_payment_amount,
p_receipt_method_id => p_receipt_method_id,
p_customer_id => p_customer_id,
p_customer_site_use_id => p_site_use_id,
p_currency_code => p_receipt_currency_code,
p_receipt_date => TRUNC
(p_receipt_date),
p_gl_date => TRUNC
(p_receipt_date),
p_cr_id => x_cash_receipt_id,
p_called_from => 'IREC'
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_cash_receipt_id := NULL;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END create_receipt;
PROCEDURE apply_receipt (
p_customer_id IN NUMBER,
p_site_use_id IN NUMBER DEFAULT NULL,
p_cash_receipt_id IN NUMBER,
p_cust_trx_id IN NUMBER,
p_trx_amt IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.APPLY_RECEIPT';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
--Assign default values
ar_receipt_api_pub.APPLY
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cash_receipt_id => p_cash_receipt_id,
p_customer_trx_id => p_cust_trx_id,
--p_applied_payment_schedule_id => ,
--p_amount_applied => ,
p_amount_applied => p_trx_amt,
--p_discount => ,
p_apply_date => SYSDATE,
p_called_from => 'ARI',
p_show_closed_invoices => 'Y'
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END apply_receipt;
END;
/
No comments:
Post a Comment