-- Script : XYKAARDRNEW_SH.sql
-- Name : AR NEW DEBTOR RECEIPTS BATCH
-- Author : ABHISHEK VAITLA
-- Date : JUNE 09,2011
-- Description :
--
--
-- Modification history :
--
--
/* $header: XYKAARDRNEW_SH.sql 1.1 2011/06/09 11:17:00 $ */
SET serveroutput on size 1000000;
--
--
DECLARE
/* to get ar debtor receipts */
CURSOR debtor_receipts
IS
SELECT hdr.x_header_id header_id, x_date x_date, x_outlet_code outlet_code,
x_division_code division_code,
x_receipt_number
|| '-'
|| (SELECT short_code
FROM hr_operating_units
WHERE organization_id = lns.x_inv_org_id) receipt_number,
x_cheque_number cheque_number, x_bank_name bank_name,
x_receipt_source receipt_source, x_payment_type payment_type,
lns.x_inv_customer_id customer_id,
customer_memo_line customer_memo_line,
lns.x_inv_cust_bill_to_id bill_to_site_use_id,
x_set_of_books_id set_of_books_id, hdr.x_org_id org_id,
hdr.x_gl_cc_id memo_cc_id, x_memo_line_id memo_line_id,
SUM (lns.x_applied_amount) amount, x_comments comments,
lns.x_inv_org_id
FROM xyka_cash_debtor_recpt_hdr_v hdr, xyka_cash_debtor_recpt_lines_v lns
WHERE hdr.x_header_id = lns.x_header_id
-- AND hdr.x_org_id = lns.x_org_id
AND NVL (x_ar_transferred_flag, 'N') = 'N'
AND x_receipt_source = ('Customer')
AND NVL (x_gl_transferred_flag, 'N') = 'Y'
AND x_set_of_books_id = '&1'
AND hdr.x_org_id = NVL ('&2', hdr.x_org_id)
AND (x_date >= TO_DATE ('&3', 'RRRR/MM/DD HH24:MI:SS') OR '&3' IS NULL)
AND (x_date <= TO_DATE ('&4', 'RRRR/MM/DD HH24:MI:SS') OR '&4' IS NULL)
/* 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'))
)
*/
GROUP BY hdr.x_header_id,
x_date,
x_outlet_code,
x_division_code,
x_receipt_number,
x_inv_ou,
x_cheque_number,
x_bank_name,
x_receipt_source,
x_payment_type,
x_inv_customer_id,
customer_memo_line,
x_inv_cust_bill_to_id,
x_set_of_books_id,
hdr.x_org_id,
hdr.x_gl_cc_id,
x_memo_line_id,
x_comments,
lns.x_inv_org_id;
/* 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, p_org_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_inv_customer_id,
x_inv_cust_bill_to_id, x_payment_schedule_id payment_schedule_id,
x_applied_amount applied_amount, x_due_date due_date,
x_inv_org_id org_id
FROM xyka_cash_debtor_inv_lines
WHERE x_header_id = p_header_id AND x_inv_org_id = p_org_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.x_inv_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.x_inv_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.x_inv_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, j.x_inv_org_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.x_inv_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;
UPDATE xyka_cash_debtor_inv_lines
SET x_ar_flag = 'Y',ar_cash_receipt_id = l_cash_receipt_id
WHERE x_header_id = j.header_id AND x_inv_org_id = j.x_inv_org_id;
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 XYKAARDRNEW_SH (Open Financials Receipts batch) ... :'
|| TO_CHAR (tot_rec)
);
END IF;
DBMS_OUTPUT.put_line
( 'Program XYKAARDRNEW_SH (Open Financials Receipts batch) '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
);
DBMS_OUTPUT.put_line (t_message);
END;
/
-- ----------------------- end of script -----------------
--
-- --------------------------------------------------------------------
-- script : xykaardr_sh.sql
-- name : ar debtor receipts batch
-- author : anjireddy chanti
-- date : august 13,2005
-- description :
--
--
-- modification history :
--
--
/* $header: xykaarri.sql 1.1 2005/07/28 11:11:00 $ *
set serveroutput on size 1000000;
--
--
declare
--
-- define cursor
--
/* to get the ar debtor receipt batch *
cursor dr_batch is
select distinct x_date,x_receipt_source
from xyka_cash_debtor_recpt_hdr_v
where x_ar_transferred_flag='n'
and x_gl_transferred_flag <>'c'
and x_receipt_source not in ('non - ar - yka','non - ar - property','non - ar - uet','non - ar - get','non - ar - aeo')
and x_date between trunc(to_date(sysdate, 'dd-mon-rrrr')) and trunc(to_date(sysdate, 'dd-mon-rrrr'))
----and (x_date between to_date(sysdate,'rrrr/mm/dd hh24:mi:ss') and to_date(sysdate,'rrrr/mm/dd hh24:mi:ss'))
and x_org_id = '&1';
/* get receipt batch source based on receipt source */
/* --(property payment method(1121) yka payment method(1120)) *
cursor batch_source(p_batch_source_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.bank_account_id,
aaa.bank_branch_id,
aaa.set_of_books_id
from ar_batch_sources_all abs,
ar_receipt_method_accounts_all arm,
ap_bank_accounts_all aaa
where abs.default_receipt_method_id=arm.receipt_method_id
and arm.bank_account_id=aaa.bank_account_id
and abs.batch_source_id=p_batch_source_id;
/* to get ar debtor receipts *
cursor debtor_receipts(p_receipt_date date,p_receipt_source varchar2) 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_source_id source_id,
customer_memo_line customer_memo_line,
x_bill_to_site_id bill_to_site_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
from xyka_cash_debtor_recpt_hdr_v
where x_ar_transferred_flag='n'
and x_receipt_source not in ('non - ar - yka','non - ar - property','non - ar - uet','non - ar - get','non - ar - aeo') --<>'non - ar - yka'
and x_gl_transferred_flag <>'c'
and x_date=p_receipt_date
and x_receipt_source=p_receipt_source;
/* 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; -- concurrent request id
t_date date := null;
t_message varchar2 (250) := 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;
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_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); --added by rohit
-- ---------------------------------------------------------------------
begin
--
-- get user id and login id
--
select fnd_global.user_id, fnd_global.login_id,fnd_profile.value('org_id')
into t_user_id, t_login_id,t_org_id
from dual;
--
--
dbms_output.put_line
( 'program xykaardr (ar debtor receipts batch) started at .....'
|| to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss')
);
--
--
for i in dr_batch
loop
/* to get batch sequence id *
select ar_batches_s.nextval
into l_batch_id
from dual;
/* to get batch control amount and count *
select sum(x_amount),
count(*)
into l_control_amt,
l_control_count
from xyka_cash_debtor_recpt_hdr_v
where x_ar_transferred_flag='n'
and x_gl_transferred_flag <>'c'
and x_receipt_source =i.x_receipt_source
and x_date=i.x_date;
if i.x_receipt_source='ar - yka' then
l_source_id:=1002;
elsif i.x_receipt_source = 'ar - uet' then -- added by rohit
l_source_id := 1062;
elsif i.x_receipt_source = 'ar - get' then -- added by rohit
l_source_id := 1064; -- as per prod
elsif i.x_receipt_source = 'ar - aeo' then -- added by rohit
l_source_id := 1065; -- as per prod
else
l_source_id:=1001;
end if;
for s in batch_source(l_source_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_branch_id:=s.bank_branch_id;
l_bank_account_id:=s.bank_account_id;
l_set_of_books_id:=s.set_of_books_id;
l_org_id:=s.org_id;
l_batch_number:=s.last_batch_num ;
end loop;
/* added by rohit *
if (l_source_id = 1062 or l_source_id = 1064) then
l_currency_code:= 'aed';
elsif l_source_id = 1065 then
l_currency_code:= 'omr';
else
l_currency_code:= 'bhd';
end if;
/* added by rohit *
insert into ar_batches_all
(batch_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
name,
batch_date,
gl_date,
status,
deposit_date,
type,
batch_source_id,
control_count,
control_amount,
batch_applied_status,
currency_code,
receipt_method_id,
remittance_bank_account_id,
receipt_class_id,
remittance_bank_branch_id,
set_of_books_id,
org_id)
values (l_batch_id,
t_user_id,
sysdate,
t_login_id,
t_user_id,
sysdate,
l_batch_number,
trunc(sysdate),
i.x_date,
'op',
i.x_date,
'manual',
l_batch_source_id,
l_control_count,
l_control_amt,
'postbatch_waiting',
l_currency_code, -- added by rohit
l_receipt_method_id,
l_bank_account_id,
l_receipt_class_id,
l_bank_branch_id,
l_set_of_books_id,
l_org_id);
for j in debtor_receipts(i.x_date,i.x_receipt_source)
loop
select ar_cash_receipts_s.nextval
into l_cash_receipt_id
from dual;
insert into ar_interim_cash_receipts_all
(cash_receipt_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
amount,
currency_code,
batch_id,
pay_from_customer,
status,
type,
receipt_number,
receipt_date,
gl_date,
special_type,
receipt_method_id,
remittance_bank_account_id,
site_use_id,
comments,
org_id)
values (l_cash_receipt_id,
t_user_id,
sysdate,
t_login_id,
t_user_id,
sysdate,
j.amount,
l_currency_code, -- added by rohit
l_batch_id,
j.source_id,
'unapp',
'cash',
j.receipt_number,
j.x_date,
j.x_date,
'multiple',
l_receipt_method_id,
l_bank_account_id,
j.bill_to_site_id,
j.comments,
l_org_id);
for l in ar_invoices(j.header_id)
loop
if l.trx_number='on account' then
l_customer_id:=-1;
l_amt_applied_from:=l.applied_amount;
l_discount_taken :=null;
else
l_customer_id:=j.source_id;
l_amt_applied_from:= null;
l_discount_taken:=0;
end if;
insert into ar_interim_cash_rcpt_lines_all
(cash_receipt_id,
cash_receipt_line_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
payment_amount,
payment_schedule_id,
customer_trx_id,
batch_id,
sold_to_customer,
discount_taken,
due_date,
amount_applied_from,
org_id)
values (l_cash_receipt_id,
l_receipt_line_id,
t_user_id,
sysdate,
t_login_id,
t_user_id,
sysdate,
l.applied_amount,
l.payment_schedule_id,
l.customer_trx_id,
l_batch_id,
l_customer_id,
l_discount_taken,
l.due_date,
l_amt_applied_from,
l_org_id);
l_receipt_line_id:= l_receipt_line_id + 1;
end loop;
update xyka_cash_debtor_recpt_hdr
set x_ar_transferred_flag='y'
where x_header_id=j.header_id;
commit;
end loop;
tot_rec := tot_rec +1;
update ar_batch_sources_all
set last_batch_num=l_batch_number+1
where batch_source_id=l_batch_source_id; --need to changed
end loop;
commit;
--
-- ---------------------------------------------------------------
--
<
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 xykaarri (open financials receipts batch) ... :'
|| to_char (tot_rec)
);
end if;
goto end_of_job;
<
rollback;
dbms_output.put_line ( 'program xykaarri (open financials receipts batch) '
|| to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss')
);
dbms_output.put_line (t_message);
select to_date ('abc') -- just to simulate a fatal error
into t_date
from dual;
goto end_of_job;
<
null; -- all over
end;
/
-- exit;
-- ----------------------- end of script -----------------
--