Package to Split/Update, Settlement (Previous open credit/Debit, Chargeback,Writ-off)

CREATE OR REPLACE PACKAGE apps.xx_claim_set_pkg AUTHID CURRENT_USER
IS
PROCEDURE xx_claim_split_prc (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2
);

PROCEDURE xx_cm_wo_ch_settl_prc (
p_claim_number VARCHAR2
);

PROCEDURE xx_split_settl_prc (
p_claim_number VARCHAR2
);

PROCEDURE xx_claim_settl_prc;
END xx_claim_set_pkg;
/

CREATE OR REPLACE PACKAGE BODY apps.xx_claim_set_pkg
IS
PROCEDURE xx_claim_split_prc (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2
)
IS
/* ***********************************************************************************************************************
* Module Name : Receivables
* Program Name : XX_CLAIM_SPLIT_PRC
* Item Category
* Description : Procedure is to create a split claim
* Created By : Hari Baskar I
* Created Date :
* Modification History :
************************************************************************************************************************** */
lv_return_status VARCHAR2 (1);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (20000);
lv_resp_name VARCHAR2 (100);
lv_user_name VARCHAR2 (100);
lv_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
lv_resp_id NUMBER := fnd_profile.VALUE ('RESP_ID');
lv_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
lv_org_id NUMBER := fnd_global.org_id;
lv_split_amount NUMBER;
lv_amount_remaining NUMBER;
lv_claim_id NUMBER;
lv_child_claim_tbl ozf_claim_pub_xx.split_claim_tbl_type;
-- lv_xx_cust_tbl_type xx_claim_tbl_type;
i INTEGER := 1;
lv_ar_bal_amt NUMBER;
lv_ar_count NUMBER := 0;

CURSOR cur_split_claim
IS
/* SELECT *
FROM apps.xx_sony_claim_int_stg
WHERE NVL (processed_status, 'NEW') = 'NEW'; */ --MJ
SELECT *
FROM xx_platform_claim_int_stgbk
WHERE NVL (process_flag, 'N') = 'N';
BEGIN
fnd_global.apps_initialize (lv_user_id,
lv_resp_id,
lv_appl_id
);
mo_global.init ('OZF');
mo_global.set_policy_context ('S', lv_org_id);
fnd_msg_pub.g_msg_level_threshold := 1;
fnd_file.put_line (fnd_file.LOG, 'USr_id '
|| TO_CHAR (lv_user_id));
fnd_file.put_line (fnd_file.LOG, 'Resp_id '
|| TO_CHAR (lv_resp_id));
fnd_file.put_line (fnd_file.LOG, 'Appl_id '
|| TO_CHAR (lv_appl_id));
fnd_file.put_line (fnd_file.LOG, 'Pkg xxea_claim_split_prc Start ------------------> 1');

FOR cur_claim_rec IN cur_split_claim
LOOP
fnd_file.put_line (fnd_file.LOG, 'Pkg loop Start');

BEGIN
SELECT oca.amount_remaining,
claim_id,
org_id
INTO lv_amount_remaining,
lv_claim_id,
lv_org_id
FROM ozf_claims_all oca
WHERE claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
lv_amount_remaining := 0;
fnd_file.put_line (fnd_file.LOG, 'Error while getting actual claim amount '
|| SQLERRM);
END;

fnd_file.put_line (fnd_file.LOG, 'lv_org_id '
|| TO_CHAR (lv_org_id));

IF cur_claim_rec.settlement_type = 'CREDIT' --'CREDIT_MEMO'
THEN
BEGIN
SELECT COUNT (DISTINCT rcta.trx_number)
INTO lv_ar_count
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND interface_line_attribute6 = cur_claim_rec.claim_number
AND rctla.line_type = 'LINE'
AND rcta.org_id = rctla.org_id
AND rcta.org_id = lv_org_id;
EXCEPTION
WHEN OTHERS
THEN
lv_amount_remaining := 0;
fnd_file.put_line (fnd_file.LOG, 'Error While getting total AR count'
|| SQLERRM);
END;

IF lv_amount_remaining = ABS (cur_claim_rec.claim_amount)
AND lv_ar_count = 1
THEN
fnd_file.put_line (fnd_file.LOG, 'No Split Requried');
ELSIF ABS (lv_amount_remaining) < ABS (cur_claim_rec.claim_amount)
THEN
fnd_file.put_line (fnd_file.LOG, 'Platform Claim Amount is greatet than actual claim amount');
/* UPDATE xxea_sony_claim_int_stg
SET processed_status = 'REJECTED',
error_message = 'Platform Claim Amount is greater than actual claim amount'
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number; */ --MJ
fnd_file.put_line
(fnd_file.LOG,
'Pkg xxea_claim_split_prc lv_amount_remaining = ABS (cur_claim_rec.claim_amount) ------------------> 2');

UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'R',
error_message = 'Platform Claim Amount is greater than actual claim amount'
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;
ELSIF lv_ar_count = 1
THEN
fnd_file.put_line (fnd_file.LOG, 'Pkg xxea_claim_split_prc == Else api Start=====================> 3');
lv_split_amount := lv_amount_remaining
+ cur_claim_rec.claim_amount;
lv_child_claim_tbl (i).amount := lv_split_amount;
ozf_claim_pub_xx.create_split_claims (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => lv_msg_count,
p_parent_claim_id => lv_claim_id,
px_child_claim_tbl => lv_child_claim_tbl
);
fnd_file.put_line (fnd_file.LOG, 'lv_return_status Main:'
|| lv_return_status);

IF lv_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Split Claims Created============> 4 ');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);
ELSE
fnd_file.put_line (fnd_file.LOG, 'API Execution failed ============> 5');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);
fnd_file.put_line (fnd_file.LOG, ' Message'
|| lv_msg_data);

FOR i IN 1 .. lv_msg_count
LOOP
fnd_file.put_line (fnd_file.LOG,
SUBSTR (fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F'),
1,
254
));
END LOOP;
END IF;
ELSE
fnd_file.put_line (fnd_file.LOG, 'Pkg Else multiple ar api Start==========> 6 ');
lv_split_amount := 0;

FOR cur_mul_ar_rec IN (SELECT DISTINCT amount_line_items_remaining,
a.customer_trx_id,
a.trx_number
FROM ra_customer_trx_all a,
ar_payment_schedules_all c,
ra_customer_trx_lines_all d
WHERE 1 = 1
AND a.customer_trx_id = c.customer_trx_id
AND a.customer_trx_id = d.customer_trx_id
AND d.interface_line_attribute6 = cur_claim_rec.claim_number
AND d.line_type = 'LINE'
AND a.org_id = d.org_id
AND a.org_id = lv_org_id
-- and BILL_TO_CUSTOMER_ID = 2974
AND a.cust_trx_type_id IN (SELECT cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE org_id = lv_org_id
AND TYPE = 'CM')
AND a.org_id = c.org_id
AND amount_line_items_remaining < 0
ORDER BY amount_line_items_remaining DESC)
LOOP
IF lv_split_amount = 0 ---- - ABS (cur_claim_rec.claim_amount);
THEN
lv_split_amount := ABS (cur_mul_ar_rec.amount_line_items_remaining);
ELSE
lv_split_amount := ABS (lv_split_amount)
+ ABS (cur_mul_ar_rec.amount_line_items_remaining);
END IF;

IF lv_split_amount < ABS (cur_claim_rec.claim_amount)
THEN
lv_child_claim_tbl (i).amount := ABS (cur_mul_ar_rec.amount_line_items_remaining);
-- lv_claim_id := lv_claim_id;
-- lv_child_claim_tbl (i).amount := lv_split_amount;
-- lv_xx_cust_tbl_type (i).cs_line_id := cur_claim_rec.cs_line_id;
-- lv_xx_cust_tbl_type (i).split_amount := lv_split_amount;
ozf_claim_pub_xx.create_split_claims (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => lv_msg_count,
p_parent_claim_id => lv_claim_id,
px_child_claim_tbl => lv_child_claim_tbl
);
fnd_file.put_line (fnd_file.LOG, 'lv_return_status Main: Start==========> 7'
|| lv_return_status);

IF lv_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Split Claims Created');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);

FOR j IN 1 .. lv_child_claim_tbl.COUNT
LOOP
fnd_file.put_line (fnd_file.LOG,
'Split Claim ID : '
|| lv_child_claim_tbl (j).split_claim_id);

BEGIN
INSERT INTO xxea_sony_split_ar_claim
(split_claim_id,
trx_id,
amount,
org_id,
claim_number,
trx_number
)
VALUES (lv_child_claim_tbl (j).split_claim_id,
cur_mul_ar_rec.customer_trx_id,
cur_mul_ar_rec.amount_line_items_remaining,
lv_org_id,
cur_claim_rec.claim_number,
cur_mul_ar_rec.trx_number
);
END;
END LOOP;
ELSE
fnd_file.put_line (fnd_file.LOG, 'API Execution failed ==========> 8');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);
fnd_file.put_line (fnd_file.LOG, 'Message'
|| lv_msg_data);

FOR i IN 1 .. lv_msg_count
LOOP
fnd_file.put_line (fnd_file.LOG,
SUBSTR (fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F'),
1,
254
));
END LOOP;
END IF;
END IF;
END LOOP;
END IF;
ELSIF ( cur_claim_rec.settlement_type = 'WRITE_OFF' --'WRITE_OFF'
OR cur_claim_rec.settlement_type = 'CHARGEBACK'
)
AND lv_amount_remaining <> ABS (cur_claim_rec.claim_amount)
THEN
--lv_amount_remaining := ABS (cur_claim_rec.claim_amount);
/* UPDATE xxea_sony_claim_int_stg
SET processed_status = 'REJECTED',
error_message = 'Platform Claim Amount is greater/Lesser than actual claim amount ==========> 9'
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number; */ --MJ
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'R',
error_message = 'Platform Claim Amount is greater/Lesser than actual claim amount==========> 10'
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;
END IF;

lv_child_claim_tbl.DELETE;
-- lv_xx_cust_tbl_type.DELETE;
END LOOP;

COMMIT;
-- lv_child_claim_tbl.DELETE;
-- lv_xx_cust_tbl_type.DELETE;

--------*******************Call Update claim settlement API Program *******************-------------
fnd_file.put_line (fnd_file.LOG, 'IN xxea_claim_settl_prc Ending and calling ====> xxea_claim_settl_prc 11');
xxea_claim_settl_prc;
------------------------------*********************------------------------------------------------
END xxea_claim_split_prc;

PROCEDURE xx_claim_settl_prc
IS
lv_return_status VARCHAR2 (1);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (20000);
lv_resp_name VARCHAR2 (100);
lv_user_name VARCHAR2 (100);
lv_claim_number VARCHAR2 (25);
lv_transaction_no VARCHAR2 (100) := '0';
lv_transaction_id NUMBER;
lv_related_customer VARCHAR2 (10);
lv_rel_site_use_id NUMBER;
lv_rel_cust_acct_id NUMBER;
lv_rel_cust_type VARCHAR2 (10);
lv_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
lv_resp_id NUMBER := fnd_profile.VALUE ('RESP_ID');
lv_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
lv_org_id NUMBER := fnd_global.org_id;
lv_claim_pub_rec ozf_claim_pub.claim_rec_type;
lv_claim_line_pub_tbl ozf_claim_pub.claim_line_tbl_type;
lv_x_claim_id NUMBER;
lv_claim_id NUMBER;
lv_api_version CONSTANT NUMBER := 1.0;
lv_object_version_number NUMBER := 1.0;
lv_amount_remaining NUMBER := 0;
lv_acctd_amount NUMBER := 0;
lv_approved_by NUMBER;
lv_trx_number VARCHAR2 (100);
i INTEGER := 1;
lv_object_version_num VARCHAR2 (5);
lv_receipt_id NUMBER;
lv_receipt_number VARCHAR2 (100);
lv_custom_setup_id NUMBER;
lv_ar_bal_amt NUMBER;
lv_count NUMBER;
lv_split_total NUMBER;

CURSOR cur_claim_num
IS
/*SELECT *
FROM xxea_sony_claim_int_stg
WHERE NVL (processed_status, 'NEW') = 'NEW';*/--MJ
SELECT *
FROM xxea_platform_claim_int_stgbk
WHERE NVL (process_flag, 'N') = 'N';

CURSOR cur_split_num (
p_claim_num VARCHAR2
)
IS
SELECT *
FROM xxea_sony_split_ar_claim
WHERE claim_number = p_claim_num;
BEGIN
------------------------------------------
-- Initialization --
------------------------------------------
fnd_global.apps_initialize (lv_user_id,
lv_resp_id,
lv_appl_id
);
mo_global.init ('OZF');
mo_global.set_policy_context ('S', lv_org_id);
fnd_msg_pub.g_msg_level_threshold := 1;

FOR cur_claim_rec IN cur_claim_num
LOOP
IF cur_claim_rec.settlement_type = 'CREDIT'
THEN
BEGIN
SELECT COUNT (*)
INTO lv_count
FROM xxea_sony_split_ar_claim
WHERE claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While Getting split claim details'
|| '-'
|| SQLERRM);
fnd_file.put_line (fnd_file.LOG,
'IN xxea_claim_settl_prc Starting =============================> 12');
END;

IF lv_count > 0
THEN
BEGIN
SELECT SUM (amount)
INTO lv_split_total
FROM xxea_sony_split_ar_claim
WHERE claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error While Getting split total claim details'
|| '-'
|| SQLERRM);
END;
END IF;

IF lv_count > 0
AND ABS (cur_claim_rec.claim_amount) > ABS (lv_split_total)
THEN
BEGIN
SELECT claim_id,
object_version_number,
oca.receipt_id,
oca.receipt_number,
oca.org_id,
custom_setup_id
INTO lv_claim_id,
lv_object_version_num,
lv_receipt_id,
lv_receipt_number,
lv_org_id,
lv_custom_setup_id
FROM ozf_claims_all oca
WHERE claim_number = cur_claim_rec.claim_number;

fnd_file.put_line (fnd_file.LOG,
'IN xxea_claim_settl_prc Starting =============================> 13');

--- Update settle from in ozf_claims_all as 'RULEBASED' to Skip approval ---
UPDATE apps.ozf_claims_all
SET settled_from = 'RULEBASED'
WHERE claim_id = lv_claim_id
AND org_id = lv_org_id;
------- END ---------------
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While Getting claim details'
|| '-'
|| SQLERRM);
END;

BEGIN
SELECT DISTINCT rcta.trx_number,
rcta.customer_trx_id
INTO lv_transaction_no,
lv_transaction_id
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
xxea_sony_split_ar_claim xxss
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND interface_line_attribute6 = cur_claim_rec.claim_number
AND rctla.line_type = 'LINE'
AND rcta.org_id = rctla.org_id
AND rcta.org_id = lv_org_id
AND NOT EXISTS (
SELECT xxss.trx_id customer_trx_id
FROM xxea_sony_split_ar_claim xxss
WHERE claim_number = cur_claim_rec.claim_number
AND xxss.trx_id = rcta.customer_trx_id);
EXCEPTION
WHEN OTHERS
THEN
lv_transaction_no := '0';
fnd_file.put_line (fnd_file.LOG, 'Error While Getting Transaction Numbers'
|| '-'
|| SQLERRM);
END;

BEGIN
SELECT (amount_line_items_remaining)
INTO lv_ar_bal_amt
FROM ra_customer_trx_all a,
ar_payment_schedules_all c
WHERE 1 = 1
AND a.customer_trx_id = c.customer_trx_id
AND a.org_id = lv_org_id
-- and BILL_TO_CUSTOMER_ID = 2974
AND a.cust_trx_type_id IN (SELECT cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE org_id = lv_org_id
AND TYPE = 'CM')
AND a.org_id = c.org_id
AND a.trx_number = lv_transaction_no
AND amount_line_items_remaining < 0;
EXCEPTION
WHEN OTHERS
THEN
lv_transaction_no := '0';
fnd_file.put_line (fnd_file.LOG, 'Error While Getting AR CM balance'
|| '-'
|| SQLERRM);
END;

fnd_file.put_line (fnd_file.LOG, 'IN xxea_claim_settl_prc Starting =============================> 14');

/* BEGIN
SELECT resource_id
INTO lv_approved_by
FROM jtf_rs_resource_extns
WHERE user_id = lv_user_id; -- needs to be changed as correct user_id
EXCEPTION
WHEN OTHERS
THEN
lv_transaction_no := '0';
fnd_file.put_line (fnd_file.LOG, 'Error While Getting Approver ID'
|| '-'
|| SQLERRM);
END;*/ -- commented by MJ to run from Sys admin
IF cur_claim_rec.settlement_type = 'CREDIT'
AND lv_transaction_no <> '0'
THEN
/* SELECT xocla.transaction_number,
xocla.transaction_id,
xocla.related_customer,
xocla.related_site_use_id,
xocla.rel_cust_account_id,
xocla.related_customer_type
INTO lv_transaction_no,
lv_transaction_id,
lv_related_customer,
lv_rel_site_use_id,
lv_rel_cust_acct_id,
lv_rel_cust_type
FROM xxea_ozf_cs_lines_all xocla
WHERE 1 = 1
AND xocla.cs_header_id = cur_claim_rec.cs_header_id
AND xocla.cs_line_id = cur_claim_rec.ar_cs_line_id
AND xocla.SOURCE = 'AR'; */
IF ABS (lv_ar_bal_amt) >= ABS (cur_claim_rec.claim_amount)
THEN
fnd_file.put_line (fnd_file.LOG,
'IN xxea_claim_settl_prc Starting =============================> 15');
fnd_file.put_line (fnd_file.LOG, '========= Start Update Claim =========');
lv_claim_pub_rec.claim_id := lv_claim_id;
lv_claim_pub_rec.object_version_number := lv_object_version_num;
lv_claim_pub_rec.payment_method := 'PREV_OPEN_CREDIT';
lv_claim_pub_rec.payment_reference_number := lv_transaction_no;
lv_claim_pub_rec.custom_setup_id := lv_custom_setup_id;
lv_claim_pub_rec.payment_reference_id := lv_transaction_id;
lv_claim_pub_rec.applied_receipt_id := lv_receipt_id;
lv_claim_pub_rec.applied_receipt_number := lv_receipt_number;
lv_claim_pub_rec.approved_flag := 'T'; --l_claim_rec.approved_flag;
lv_claim_pub_rec.approved_date := SYSDATE; --l_claim_rec.approved_date;
lv_claim_pub_rec.approved_by := 100000084; --l_claim_rec.approved_by;
-- lv_claim_pub_rec.pay_related_account_flag := lv_related_customer;
-- lv_claim_pub_rec.related_cust_account_id := lv_rel_cust_acct_id;
-- lv_claim_pub_rec.related_site_use_id := lv_rel_site_use_id;
-- lv_claim_pub_rec.relationship_type := lv_rel_cust_type;
lv_claim_pub_rec.comments := 'SETTLED FOR ARGONAUT POC';
lv_claim_pub_rec.status_code := 'CLOSED';
----------------------------------------- Update Claim -------------------------------------------------
ozf_claim_pub.update_claim (p_api_version_number => lv_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data,
p_claim_rec => lv_claim_pub_rec,
p_claim_line_tbl => lv_claim_line_pub_tbl,
x_object_version_number => lv_object_version_number
);
----------------------------------------------------------------------------------------------------------
fnd_file.put_line (fnd_file.LOG, 'Success ? '
|| lv_return_status);
fnd_file.put_line (fnd_file.LOG,
'IN xxea_claim_settl_prc Starting =============================> 16');

IF lv_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.put_line (fnd_file.LOG, '----- Update of Claim sucessfull-----');
END IF;

IF lv_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Record Inserted Successfully '
|| lv_return_status);

BEGIN
/* UPDATE xxea_sony_claim_int_stg
SET processed_status = 'SUCCESS'
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number;*/ --MJ
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'S'
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;

fnd_file.put_line
(fnd_file.LOG,
'IN xxea_claim_settl_prc Starting =============================> if ==17');
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error While update Error record status'
|| '-'
|| SQLERRM);
END;
ELSE
fnd_file.put_line (fnd_file.LOG,
'IN xxea_claim_settl_prc Starting =============================> Else 18');
fnd_file.put_line (fnd_file.LOG, 'API Execution failed ');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);
fnd_file.put_line (fnd_file.LOG, ' Message'
|| lv_msg_data);

BEGIN
/* UPDATE xxea_sony_claim_int_stg
SET processed_status = 'REJECTED',
error_message =
'API Error View Request Log for Detailed Error'
|| '-'
|| fnd_global.conc_request_id
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number; */
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'R',
error_message =
'API Error View Request Log for Detailed Error'
|| '-'
|| fnd_global.conc_request_id
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error While update Error record status'
|| '-'
|| SQLERRM);
END;

FOR i IN 1 .. lv_msg_count
LOOP
fnd_file.put_line (fnd_file.LOG,
SUBSTR (fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F'),
1,
254
));
fnd_file.put_line (fnd_file.LOG,
'IN xxea_claim_settl_prc Starting =============================> 19');
END LOOP;
END IF;
END IF;
END IF;
END IF;
ELSE
lv_count := 0;
END IF;

IF lv_count > 0
THEN
xxea_split_settl_prc (cur_claim_rec.claim_number);
ELSE
xxea_cm_wo_ch_settl_prc (cur_claim_rec.claim_number);
END IF;
END LOOP;

fnd_file.put_line (fnd_file.LOG, 'IN xxea_claim_settl_prc Ending =============================> 20');
COMMIT;
END xxea_claim_settl_prc;

PROCEDURE xx_cm_wo_ch_settl_prc (
p_claim_number VARCHAR2
)
IS
lv_return_status VARCHAR2 (1);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (20000);
lv_resp_name VARCHAR2 (100);
lv_user_name VARCHAR2 (100);
lv_claim_number VARCHAR2 (25);
lv_transaction_no VARCHAR2 (100) := '0';
lv_transaction_id NUMBER;
lv_related_customer VARCHAR2 (10);
lv_rel_site_use_id NUMBER;
lv_rel_cust_acct_id NUMBER;
lv_rel_cust_type VARCHAR2 (10);
lv_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
lv_resp_id NUMBER := fnd_profile.VALUE ('RESP_ID');
lv_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
lv_org_id NUMBER := fnd_global.org_id;
lv_claim_pub_rec ozf_claim_pub.claim_rec_type;
lv_claim_line_pub_tbl ozf_claim_pub.claim_line_tbl_type;
lv_x_claim_id NUMBER;
lv_claim_id NUMBER;
lv_api_version CONSTANT NUMBER := 1.0;
lv_object_version_number NUMBER := 1.0;
lv_amount_remaining NUMBER := 0;
lv_acctd_amount NUMBER := 0;
lv_approved_by NUMBER;
lv_trx_number VARCHAR2 (100);
i INTEGER := 1;
lv_object_version_num VARCHAR2 (5);
lv_receipt_id NUMBER;
lv_receipt_number VARCHAR2 (100);
lv_custom_setup_id NUMBER;
lv_ar_bal_amt NUMBER;

CURSOR cur_claim_num
IS
/*SELECT *
FROM xxea_sony_claim_int_stg
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = p_claim_number;*/--MJ
SELECT *
FROM xxea_platform_claim_int_stgbk
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = p_claim_number;
BEGIN
------------------------------------------
-- Initialization --
------------------------------------------
fnd_global.apps_initialize (lv_user_id,
lv_resp_id,
lv_appl_id
);
mo_global.init ('OZF');
mo_global.set_policy_context ('S', lv_org_id);
fnd_msg_pub.g_msg_level_threshold := 1;

FOR cur_claim_rec IN cur_claim_num
LOOP
BEGIN
SELECT claim_id,
object_version_number,
oca.receipt_id,
oca.receipt_number,
oca.org_id,
custom_setup_id
INTO lv_claim_id,
lv_object_version_num,
lv_receipt_id,
lv_receipt_number,
lv_org_id,
lv_custom_setup_id
FROM ozf_claims_all oca
WHERE claim_number = cur_claim_rec.claim_number;

--- Update settle from in ozf_claims_all as 'RULEBASED' to Skip approval ---
UPDATE apps.ozf_claims_all
SET settled_from = 'RULEBASED'
WHERE claim_id = lv_claim_id
AND org_id = lv_org_id;
------- END ---------------
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While Getting claim details'
|| '-'
|| SQLERRM);
END;

fnd_file.put_line (fnd_file.LOG, 'Settlement Type :'
|| cur_claim_rec.settlement_type);

IF cur_claim_rec.settlement_type = 'CREDIT'
THEN
BEGIN
SELECT DISTINCT rcta.trx_number,
rcta.customer_trx_id
INTO lv_transaction_no,
lv_transaction_id
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND interface_line_attribute6 = cur_claim_rec.claim_number
AND rctla.line_type = 'LINE'
AND rcta.org_id = rctla.org_id
AND rcta.org_id = lv_org_id;
EXCEPTION
WHEN OTHERS
THEN
lv_transaction_no := '0';
fnd_file.put_line (fnd_file.LOG, 'Error While Getting Transaction Numbers'
|| '-'
|| SQLERRM);
END;

BEGIN
SELECT (amount_line_items_remaining)
INTO lv_ar_bal_amt
FROM ra_customer_trx_all a,
ar_payment_schedules_all c
WHERE 1 = 1
AND a.customer_trx_id = c.customer_trx_id
AND a.org_id = lv_org_id
-- and BILL_TO_CUSTOMER_ID = 2974
AND a.cust_trx_type_id IN (SELECT cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE org_id = lv_org_id
AND TYPE = 'CM')
AND a.org_id = c.org_id
AND a.trx_number = lv_transaction_no
AND amount_line_items_remaining < 0;
EXCEPTION
WHEN OTHERS
THEN
lv_transaction_no := '0';
fnd_file.put_line (fnd_file.LOG, 'Error While Getting AR CM balance'
|| '-'
|| SQLERRM);
END;

/* BEGIN
SELECT resource_id
INTO lv_approved_by
FROM jtf_rs_resource_extns
WHERE user_id = lv_user_id;
EXCEPTION
WHEN OTHERS
THEN
lv_transaction_no := '0';
fnd_file.put_line (fnd_file.LOG, 'Error While Getting Approver ID'
|| '-'
|| SQLERRM);
END;*/ -- commented by MJ to run from sys admin
IF lv_transaction_no <> '0'
THEN
/* SELECT xocla.transaction_number,
xocla.transaction_id,
xocla.related_customer,
xocla.related_site_use_id,
xocla.rel_cust_account_id,
xocla.related_customer_type
INTO lv_transaction_no,
lv_transaction_id,
lv_related_customer,
lv_rel_site_use_id,
lv_rel_cust_acct_id,
lv_rel_cust_type
FROM xxea_ozf_cs_lines_all xocla
WHERE 1 = 1
AND xocla.cs_header_id = cur_claim_rec.cs_header_id
AND xocla.cs_line_id = cur_claim_rec.ar_cs_line_id
AND xocla.SOURCE = 'AR'; */
IF ABS (lv_ar_bal_amt) >= ABS (cur_claim_rec.claim_amount)
THEN
fnd_file.put_line (fnd_file.LOG, '========= Start Update Claim =========');
lv_claim_pub_rec.claim_id := lv_claim_id;
lv_claim_pub_rec.object_version_number := lv_object_version_num;
lv_claim_pub_rec.payment_method := 'PREV_OPEN_CREDIT';
lv_claim_pub_rec.payment_reference_number := lv_transaction_no;
lv_claim_pub_rec.custom_setup_id := lv_custom_setup_id;
lv_claim_pub_rec.payment_reference_id := lv_transaction_id;
lv_claim_pub_rec.applied_receipt_id := lv_receipt_id;
lv_claim_pub_rec.applied_receipt_number := lv_receipt_number;
lv_claim_pub_rec.approved_flag := 'T'; --l_claim_rec.approved_flag;
lv_claim_pub_rec.approved_date := SYSDATE; --l_claim_rec.approved_date;
lv_claim_pub_rec.approved_by := 100000084; --l_claim_rec.approved_by;
-- lv_claim_pub_rec.pay_related_account_flag := lv_related_customer;
-- lv_claim_pub_rec.related_cust_account_id := lv_rel_cust_acct_id;
-- lv_claim_pub_rec.related_site_use_id := lv_rel_site_use_id;
-- lv_claim_pub_rec.relationship_type := lv_rel_cust_type;
lv_claim_pub_rec.comments := 'SETTLED FOR ARGONAUT POC';
lv_claim_pub_rec.status_code := 'CLOSED';
----------------------------------------- Update Claim -------------------------------------------------
ozf_claim_pub.update_claim (p_api_version_number => lv_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data,
p_claim_rec => lv_claim_pub_rec,
p_claim_line_tbl => lv_claim_line_pub_tbl,
x_object_version_number => lv_object_version_number
);
----------------------------------------------------------------------------------------------------------
fnd_file.put_line (fnd_file.LOG, 'Success ? '
|| lv_return_status);

IF lv_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.put_line (fnd_file.LOG, '----- Update of Claim sucessfull-----');
END IF;

IF lv_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Record Inserted Successfully '
|| lv_return_status);

BEGIN
/* UPDATE xxea_sony_claim_int_stg
SET processed_status = 'SUCCESS'
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number;*/ --MJ
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'S'
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While update Error record status'
|| '-'
|| SQLERRM);
END;
ELSE
fnd_file.put_line (fnd_file.LOG, 'API Execution failed ');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);
fnd_file.put_line (fnd_file.LOG, ' Message'
|| lv_msg_data);

BEGIN
/* UPDATE xxea_sony_claim_int_stg
SET processed_status = 'ERROR',
error_message = lv_msg_data
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number;*/ --MJ
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'E',
error_message = lv_msg_data
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While update Error record status'
|| '-'
|| SQLERRM);
END;

FOR i IN 1 .. lv_msg_count
LOOP
fnd_file.put_line (fnd_file.LOG,
SUBSTR (fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F'),
1,
254
));
END LOOP;
END IF;
ELSE
/* UPDATE xxea_sony_claim_int_stg
SET processed_status = 'REJECTED',
error_message =
'Settlement cannot proceed because the balance of Previous Open Credit Memo is less than amount settled'
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number;*/--MJ
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'R',
error_message =
'Settlement cannot proceed because the balance of Previous Open Credit Memo is less than amount settled'
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;

fnd_file.put_line
(fnd_file.LOG,
'Settlement cannot proceed because the balance of Previous Open Credit Memo is less than amount settled');
END IF;
END IF;
ELSIF cur_claim_rec.settlement_type = 'WRITE_OFF' --'WRITE_OFF'
OR cur_claim_rec.settlement_type = 'CHARGEBACK'
THEN
IF cur_claim_rec.settlement_type = 'WRITE_OFF'
THEN
lv_claim_pub_rec.payment_method := 'WRITE_OFF';
ELSE
lv_claim_pub_rec.payment_method := cur_claim_rec.settlement_type;
END IF;

fnd_file.put_line (fnd_file.LOG, 'Processing Claim id ===>'
|| lv_claim_id);
lv_claim_pub_rec.claim_id := lv_claim_id;
lv_claim_pub_rec.object_version_number := lv_object_version_num;
lv_claim_pub_rec.custom_setup_id := lv_custom_setup_id;
lv_claim_pub_rec.comments := 'SETTLED FOR ARGONAUT POC';
lv_claim_pub_rec.status_code := 'Platform Closed';
------------------------------- Start Update Claim ---------------------------------------
ozf_claim_pub.update_claim (p_api_version_number => lv_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data,
p_claim_rec => lv_claim_pub_rec,
p_claim_line_tbl => lv_claim_line_pub_tbl,
x_object_version_number => lv_object_version_number
);
----------------------------------- End Update Claim -------------------------------------------------
fnd_file.put_line (fnd_file.LOG, 'Success ? '
|| lv_return_status);

IF lv_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.put_line (fnd_file.LOG, '----- Update of Claim sucessfull-----');
END IF;

IF lv_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Record Inserted Successfully '
|| lv_return_status);

BEGIN
/*UPDATE xxea_sony_claim_int_stg
SET processed_status = 'SUCCESS'
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number;*/ --MJ
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'S'
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While update Error record status'
|| '-'
|| SQLERRM);
END;
ELSE
fnd_file.put_line (fnd_file.LOG, 'API Execution failed ');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);
fnd_file.put_line (fnd_file.LOG, ' Message'
|| lv_msg_data);

BEGIN
/*UPDATE xxea_sony_claim_int_stg
SET processed_status = 'ERROR',
error_message = lv_msg_data
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND claim_number = cur_claim_rec.claim_number;*/
UPDATE xxea_platform_claim_int_stgbk
SET process_flag = 'E',
error_message = lv_msg_data
WHERE NVL (process_flag, 'N') = 'N'
AND claim_number = cur_claim_rec.claim_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While update Error record status'
|| '-'
|| SQLERRM);
END;

FOR i IN 1 .. lv_msg_count
LOOP
fnd_file.put_line (fnd_file.LOG,
SUBSTR (fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F'),
1,
254
));
END LOOP;
END IF;
ELSE
fnd_file.put_line (fnd_file.LOG,
'----- No Transaction Number/ No Settlement Method Available to settle-----');
END IF;
END LOOP;

COMMIT;
END xxea_cm_wo_ch_settl_prc;

PROCEDURE xx_split_settl_prc (
p_claim_number VARCHAR2
)
IS
lv_return_status VARCHAR2 (1);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (20000);
lv_resp_name VARCHAR2 (100);
lv_user_name VARCHAR2 (100);
lv_claim_number VARCHAR2 (25);
lv_transaction_no VARCHAR2 (100) := '0';
lv_transaction_id NUMBER;
lv_related_customer VARCHAR2 (10);
lv_rel_site_use_id NUMBER;
lv_rel_cust_acct_id NUMBER;
lv_rel_cust_type VARCHAR2 (10);
lv_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
lv_resp_id NUMBER := fnd_profile.VALUE ('RESP_ID');
lv_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
lv_org_id NUMBER := fnd_global.org_id;
lv_claim_pub_rec ozf_claim_pub.claim_rec_type;
lv_claim_line_pub_tbl ozf_claim_pub.claim_line_tbl_type;
lv_x_claim_id NUMBER;
lv_claim_id NUMBER;
lv_api_version CONSTANT NUMBER := 1.0;
lv_object_version_number NUMBER := 1.0;
lv_amount_remaining NUMBER := 0;
lv_acctd_amount NUMBER := 0;
lv_approved_by NUMBER;
lv_trx_number VARCHAR2 (100);
i INTEGER := 1;
lv_object_version_num VARCHAR2 (5);
lv_receipt_id NUMBER;
lv_receipt_number VARCHAR2 (100);
lv_custom_setup_id NUMBER;
lv_ar_bal_amt NUMBER;

CURSOR cur_split_num
IS
SELECT *
FROM xxea_sony_split_ar_claim
WHERE claim_number = p_claim_number;
BEGIN
FOR cur_split_rec IN cur_split_num
LOOP
BEGIN
SELECT claim_id,
object_version_number,
oca.receipt_id,
oca.receipt_number,
oca.org_id,
custom_setup_id
INTO lv_claim_id,
lv_object_version_num,
lv_receipt_id,
lv_receipt_number,
lv_org_id,
lv_custom_setup_id
FROM ozf_claims_all oca
WHERE claim_id = cur_split_rec.split_claim_id;

--- Update settle from in ozf_claims_all as 'RULEBASED' to avoid approval ---
UPDATE apps.ozf_claims_all
SET settled_from = 'RULEBASED'
WHERE claim_id = lv_claim_id
AND org_id = lv_org_id;
------- END ---------------
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While Getting claim details'
|| '-'
|| SQLERRM);
END;

/* BEGIN
SELECT resource_id
INTO lv_approved_by
FROM jtf_rs_resource_extns
WHERE user_id = lv_user_id;
EXCEPTION
WHEN OTHERS
THEN
lv_transaction_no := '0';
fnd_file.put_line (fnd_file.LOG, 'Error While Getting Approver ID'
|| '-'
|| SQLERRM);
END;*/ -- Commented by MJ to run from Sys Admin .

/* SELECT xocla.transaction_number,
xocla.transaction_id,
xocla.related_customer,
xocla.related_site_use_id,
xocla.rel_cust_account_id,
xocla.related_customer_type
INTO lv_transaction_no,
lv_transaction_id,
lv_related_customer,
lv_rel_site_use_id,
lv_rel_cust_acct_id,
lv_rel_cust_type
FROM xxea_ozf_cs_lines_all xocla
WHERE 1 = 1
AND xocla.cs_header_id = cur_claim_rec.cs_header_id
AND xocla.cs_line_id = cur_claim_rec.ar_cs_line_id
AND xocla.SOURCE = 'AR'; */
fnd_file.put_line (fnd_file.LOG, '========= Start Update Claim =========');
lv_claim_pub_rec.claim_id := lv_claim_id;
lv_claim_pub_rec.object_version_number := lv_object_version_num;
lv_claim_pub_rec.payment_method := 'PREV_OPEN_CREDIT';
lv_claim_pub_rec.payment_reference_number := cur_split_rec.trx_number;
lv_claim_pub_rec.custom_setup_id := lv_custom_setup_id;
lv_claim_pub_rec.payment_reference_id := cur_split_rec.trx_id;
lv_claim_pub_rec.applied_receipt_id := lv_receipt_id;
lv_claim_pub_rec.applied_receipt_number := lv_receipt_number;
lv_claim_pub_rec.approved_flag := 'T'; --l_claim_rec.approved_flag;
lv_claim_pub_rec.approved_date := SYSDATE; --l_claim_rec.approved_date;
lv_claim_pub_rec.approved_by := lv_approved_by; --l_claim_rec.approved_by;
-- lv_claim_pub_rec.pay_related_account_flag := lv_related_customer;
-- lv_claim_pub_rec.related_cust_account_id := lv_rel_cust_acct_id;
-- lv_claim_pub_rec.related_site_use_id := lv_rel_site_use_id;
-- lv_claim_pub_rec.relationship_type := lv_rel_cust_type;
lv_claim_pub_rec.comments := 'SETTLED FOR ARGONAUT POC';
lv_claim_pub_rec.status_code := 'CLOSED';
----------------------------------------- Update Claim -------------------------------------------------
ozf_claim_pub.update_claim (p_api_version_number => lv_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data,
p_claim_rec => lv_claim_pub_rec,
p_claim_line_tbl => lv_claim_line_pub_tbl,
x_object_version_number => lv_object_version_number
);
----------------------------------------------------------------------------------------------------------
fnd_file.put_line (fnd_file.LOG, 'Success ? '
|| lv_return_status);

IF lv_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.put_line (fnd_file.LOG, '----- Update of Claim sucessfull-----');
END IF;

IF lv_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Record Inserted Successfully '
|| lv_return_status);

BEGIN
UPDATE xxea_sony_split_ar_claim
SET processed_status = 'SUCCESS'
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND split_claim_id = cur_split_rec.split_claim_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While update Error record status'
|| '-'
|| SQLERRM);
END;
ELSE
fnd_file.put_line (fnd_file.LOG, 'API Execution failed ');
fnd_file.put_line (fnd_file.LOG, 'lv_return_status :'
|| lv_return_status);
fnd_file.put_line (fnd_file.LOG, ' Message'
|| lv_msg_data);

BEGIN
UPDATE xx_split_ar_claim
SET processed_status = 'ERROR',
error_message = lv_msg_data
WHERE NVL (processed_status, 'NEW') = 'NEW'
AND split_claim_id = cur_split_rec.split_claim_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error While update Error record status'
|| '-'
|| SQLERRM);
END;

FOR i IN 1 .. lv_msg_count
LOOP
fnd_file.put_line (fnd_file.LOG, SUBSTR (fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F'),
1,
254
));
END LOOP;
END IF;
END LOOP;

COMMIT;
END xx_split_settl_prc;
END xx_claim_set_pkg;
/

Oracle - eBIZ

ORACLE

Wednesday, May 21, 2014

Package to Split/Update, Settlement (Previous open credit/Debit, Chargeback,Writ-off)

No comments:

Post a Comment

Total Pageviews