DECLARE
/* to get ar debtor receipts */
CURSOR debtor_receipts
IS
SELECT x_header_id header_id,
x_date x_date,
x_outlet_code outlet_code,
x_division_code division_code,
x_receipt_number receipt_number,
x_cheque_number cheque_number,
x_bank_name bank_name,
x_receipt_source receipt_source,
x_payment_type payment_type,
x_customer_id customer_id,
customer_memo_line customer_memo_line,
x_bill_to_site_use_id bill_to_site_use_id,
x_set_of_books_id set_of_books_id,
x_org_id org_id,
x_gl_cc_id memo_cc_id,
x_memo_line_id memo_line_id,
x_amount amount,
x_comments comments,
x_org_id
FROM xyka_cash_debtor_recpt_hdr_v
WHERE x_ar_transferred_flag = 'N'
AND x_receipt_source = ('Customer')
AND x_gl_transferred_flag <> 'C'
AND x_set_of_books_id = '&1'
AND x_org_id = NVL ('&2', x_org_id)
AND x_date BETWEEN NVL (
TO_DATE ('&3', 'RRRR/MM/DD HH24:MI:SS'),
TRUNC (TO_DATE (SYSDATE, 'DD-MON-RRRR'))
)
AND NVL (
TO_DATE ('&4', 'RRRR/MM/DD HH24:MI:SS'),
TRUNC (
TO_DATE (SYSDATE, 'DD-MON-RRRR')
)
);
/* get receipt batch source based on receipt source */
CURSOR batch_source (
p_org_id NUMBER
)
IS
SELECT ABS.batch_source_id,
ABS.last_batch_num,
ABS.default_receipt_class_id,
ABS.default_receipt_method_id,
ABS.org_id,
arm.REMIT_BANK_ACCT_USE_ID,
aaa.bank_account_id
FROM ar_batch_sources_all ABS,
ar_receipt_method_accounts_all arm,
ce_bank_acct_uses_all aaa
WHERE ABS.default_receipt_method_id = arm.receipt_method_id
AND arm.remit_bank_acct_use_id = aaa.BANK_ACCT_USE_ID
AND UPPER (name) LIKE '%DEBTOR%RECEIPT%'
AND ABS.org_id = p_org_id;
/* to get applied invoices against receipts from debtor receipt */
CURSOR ar_invoices (p_header_id NUMBER)
IS
SELECT x_line_id line_id,
x_header_id header_id,
x_customer_trx_number trx_number,
x_customer_trx_id customer_trx_id,
x_payment_schedule_id payment_schedule_id,
x_applied_amount applied_amount,
x_due_date due_date,
x_org_id org_id
FROM xyka_cash_debtor_inv_lines
WHERE x_header_id = p_header_id;
-- define variables
--
t_user_id NUMBER := 0;
t_org_id NUMBER := 0;
t_login_id NUMBER := 0;
t_req_id NUMBER := 0;
t_date DATE := NULL;
t_message VARCHAR2 (32000) := NULL;
tot_rec NUMBER := 0;
tot_err_rec NUMBER := 0;
--
-- define user specified parameters
--
lv_rec_count NUMBER := 1;
lv_org_id NUMBER (15) := 0;
lv_valid_cnt NUMBER (15) := 0;
lv_null_cnt NUMBER (15) := 0;
lv_chart_ok BOOLEAN := FALSE;
lv_curr VARCHAR2 (15) := NULL;
lv_err_flag VARCHAR2 (1) := 'N';
l_batch_id NUMBER := 0;
l_cash_receipt_id NUMBER := 0;
l_batch_number NUMBER;
l_control_amt NUMBER := 0;
l_control_count NUMBER := 0;
l_set_of_books_id NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
l_org_id NUMBER;
l_site_use_id NUMBER;
l_receipt_line_id NUMBER := 1;
l_batch_source_id NUMBER;
l_receipt_class_id NUMBER;
l_receipt_method_id NUMBER;
l_bank_branch_id NUMBER;
l_bank_account_use_id NUMBER;
l_customer_id NUMBER;
l_invoice_count NUMBER := 0;
l_source_id NUMBER;
l_amt_applied_from NUMBER;
l_discount_taken NUMBER;
l_currency_code VARCHAR2 (100);
--
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_count NUMBER;
l_msg_data_out VARCHAR2 (1000);
l_mesg VARCHAR2 (1000);
l_err_msg VARCHAR2 (1000);
p_count NUMBER;
--
l_receipt_number VARCHAR2 (40);
l_dr_amount NUMBER;
l_cr_amount NUMBER;
l_app_attribute_rec ar_receipt_api_pub.attribute_rec_type;
l_payment_number VARCHAR2 (20);
l_cr_id NUMBER;
-- ---------------------------------------------------------------------
BEGIN
-- Initializes the required variables
--fnd_global.apps_initialize (1111, 50680, 222);
DBMS_OUTPUT.put_line('Program XYKAARDR (AR Debtor Receipts batch) started at .....'
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
BEGIN
SELECT currency_code
INTO l_currency_code
FROM gl_ledgers
WHERE ledger_id = l_set_of_books_id;
EXCEPTION
WHEN OTHERS
THEN
l_currency_code := NULL;
END;
FOR j IN debtor_receipts
LOOP
FOR s IN batch_source (j.org_id)
LOOP
l_batch_source_id := s.batch_source_id;
l_receipt_class_id := s.default_receipt_class_id;
l_receipt_method_id := s.default_receipt_method_id;
l_bank_account_use_id := s.REMIT_BANK_ACCT_USE_ID;
l_batch_number := s.last_batch_num;
END LOOP;
mo_global.set_policy_context ('S', j.org_id);
ar_receipt_api_pub.create_cash (
p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => l_currency_code,
p_amount => j.amount,
p_receipt_number => j.receipt_number,
p_receipt_date => j.x_date,
p_gl_date => j.x_date,
p_customer_id => j.customer_id,
p_customer_site_use_id => j.bill_to_site_use_id,
p_org_id => j.org_id,
p_remittance_bank_account_id => l_bank_account_use_id,
p_receipt_method_id => l_receipt_method_id,
p_cr_id => l_cash_receipt_id,
p_comments => j.comments
);
IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' || p_count || '.' || l_msg_data);
l_err_msg := l_err_msg || l_msg_data;
END LOOP;
END IF;
/* validates the result */
IF l_cash_receipt_id IS NULL
THEN
t_message :=
t_message
|| ' Receipt creation failed. Error message : '
|| l_err_msg;
ELSE
FOR l IN ar_invoices (j.header_id)
LOOP
IF l.trx_number <> 'On Account'
THEN
--l_amt_applied_from := NULL;
--l_discount_taken := 0;
/* calls the api to apply receipt against the invoice*/
ar_receipt_api_pub.apply (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_cash_receipt_id => l_cash_receipt_id,
p_customer_trx_id => l.customer_trx_id,
p_applied_payment_schedule_id => l.payment_schedule_id,
--p_discount => l_discount_taken,
p_amount_applied => l.applied_amount,
--p_amount_applied_from => l_amt_applied_from,
p_org_id => j.org_id,
p_apply_date => j.x_date,
p_apply_gl_date => j.x_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
t_message := p_count || '.' || l_msg_data;
END LOOP;
END IF;
END IF;
END LOOP;
END IF;
UPDATE XYKA_CASH_DEBTOR_RECPT_HDR
SET AR_CASH_RECEIPT_ID = l_cash_receipt_id,
x_ar_transferred_flag = 'Y'
WHERE x_header_id = j.header_id;
tot_rec := tot_rec + 1;
END LOOP;
COMMIT;
IF tot_rec = 0
THEN
DBMS_OUTPUT.put_line ('There is no record for Transfer.');
ELSE
DBMS_OUTPUT.put_line('Total records inserted for Program XYKAARDR_SH (Open Financials Receipts batch) ... :'
|| TO_CHAR (tot_rec));
END IF;
DBMS_OUTPUT.put_line('Program XYKAARDR_SH (Open Financials Receipts batch) '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
DBMS_OUTPUT.put_line (t_message);
END;
/