AP invoice created and as usual the AP payment process will be taken care for customer refund.
CREATE OR REPLACE PACKAGE APPS.xxeur_ge_neg_receipt_app
IS
gc_trx_date_from VARCHAR2(11);
gc_trx_date_to VARCHAR2(11);
gn_transmission_id NUMBER;
gn_org_id NUMBER := fnd_profile.value(‘ORG_ID’);
gc_lockbox_num VARCHAR2(30);
PROCEDURE main( errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_customer_name_from IN VARCHAR2
,p_customer_name_to IN VARCHAR2
,p_customer_number_from IN VARCHAR2
,p_customer_number_to IN VARCHAR2
,p_trx_date_from IN VARCHAR2
,p_trx_date_to IN VARCHAR2
,p_min_balance_due IN NUMBER
,p_receipt_method_from IN VARCHAR2
,p_receipt_method_to IN VARCHAR2
,p_cust_class_from IN VARCHAR2
,p_cust_class_to IN VARCHAR2
,p_cust_numbers IN VARCHAR2
,p_option IN VARCHAR2
,p_dummy IN VARCHAR2
–,p_transmission_id IN VARCHAR2
,p_account IN VARCHAR2
);
PROCEDURE create_refund(errbuf OUT VARCHAR2
,retcode OUT NUMBER
–,p_transmission_name IN VARCHAR2
);
PROCEDURE insert_lockbox_rec (p_record_type IN VARCHAR2
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice1 IN VARCHAR2
,p_invoice2 IN VARCHAR2
,p_invoice3 IN VARCHAR2
,p_invoice4 IN VARCHAR2
,p_invoice5 IN VARCHAR2
,p_invoice6 IN VARCHAR2
,p_invoice7 IN VARCHAR2
,p_invoice8 IN VARCHAR2
,p_amount_applied1 IN NUMBER
,p_amount_applied2 IN NUMBER
,p_amount_applied3 IN NUMBER
,p_amount_applied4 IN NUMBER
,p_amount_applied5 IN NUMBER
,p_amount_applied6 IN NUMBER
,p_amount_applied7 IN NUMBER
,p_amount_applied8 IN NUMBER
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
);
PROCEDURE insert_inv_data (p_check_number IN VARCHAR2
,p_customer_site_use_id IN NUMBER
,p_invoice IN VARCHAR2
,p_amount_applied IN NUMBER
,p_transmission_id IN NUMBER
,p_account IN VARCHAR2
,p_customer_trx_id IN NUMBER
);
END;
CREATE OR REPLACE PACKAGE BODY APPS.XXEUR_GE_NEG_RECEIPT_APP
IS
PROCEDURE main( errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_customer_name_from IN VARCHAR2
,p_customer_name_to IN VARCHAR2
,p_customer_number_from IN VARCHAR2
,p_customer_number_to IN VARCHAR2
,p_trx_date_from IN VARCHAR2
,p_trx_date_to IN VARCHAR2
,p_min_balance_due IN NUMBER
,p_receipt_method_from IN VARCHAR2
,p_receipt_method_to IN VARCHAR2
,p_cust_class_from IN VARCHAR2
,p_cust_class_to IN VARCHAR2
,p_cust_numbers IN VARCHAR2
,p_option IN VARCHAR2
,p_dummy IN VARCHAR2
–,p_transmission_id IN VARCHAR2
,p_account IN VARCHAR2
)
AS
ld_trx_date_from DATE;
ld_trx_date_to DATE;
ln_request_id NUMBER;
lb_complete BOOLEAN;
lc_phase VARCHAR2 (100);
lc_status VARCHAR2 (100);
lc_dev_phase VARCHAR2 (100);
lc_dev_status VARCHAR2 (100);
lc_message VARCHAR2 (100);
lb_set_layout_option BOOLEAN;
ln_site_use_id NUMBER;
ln_rec_count NUMBER;
lc_invoice1 VARCHAR2(50);
lc_invoice2 VARCHAR2(50);
lc_invoice3 VARCHAR2(50);
lc_invoice4 VARCHAR2(50);
lc_invoice5 VARCHAR2(50);
lc_invoice6 VARCHAR2(50);
lc_invoice7 VARCHAR2(50);
lc_invoice8 VARCHAR2(50);
ln_amount_applied1 NUMBER;
ln_amount_applied2 NUMBER;
ln_amount_applied3 NUMBER;
ln_amount_applied4 NUMBER;
ln_amount_applied5 NUMBER;
ln_amount_applied6 NUMBER;
ln_amount_applied7 NUMBER;
ln_amount_applied8 NUMBER;
ln_transmission_id NUMBER;
ln_inv_cnt NUMBER;
ln_receipt_number VARCHAR2(30);
ln_item_number NUMBER;
ln_batch_cnt NUMBER;
ln_overflow_cnt NUMBER;
lc_record_type VARCHAR2(1);
lc_org_name VARCHAR2(100);
CURSOR lcu_bill_to_customer
IS
SELECT customer_number,customer_id,site_use_id, count(1) inv_count, customer_name, location
FROM (
SELECT trx.trx_number
,CUST.customer_id
,CUST.customer_name
,CUST.customer_number
,DECODE(APS.class,’CM’,’Credit Memo’,’INV’,’Invoice’) class
,APS.amount_due_remaining balance_due
,SUM(APS.amount_due_remaining)
OVER (PARTITION BY TRX.bill_to_site_use_id) AS total_due
,COUNT(APS.amount_due_remaining)
OVER (PARTITION BY TRX.bill_to_site_use_id) AS cust_rec_count
,APS.invoice_currency_code currency
,TO_CHAR(TRX.trx_date,’DD-MON-YYYY’) trx_date
,TO_CHAR(APS.gl_date,’DD-MON-YYYY’) gl_date
,ARM.name receipt_method
,HCSU.location
,HCSU.site_use_id
FROM ra_customer_trx_all TRX
,ar_payment_schedules_all APS
,ar_customers CUST
,ar_receipt_methods ARM
,hz_cust_site_uses_all HCSU
WHERE TRX.customer_trx_id = APS.customer_trx_id
AND TRX.bill_to_customer_id = CUST.customer_id
AND TRX.receipt_method_id = ARM.receipt_method_id (+)
AND TRX.bill_to_site_use_id = HCSU.site_use_id
AND TRX.status_trx = ‘OP’
AND APS.status = ‘OP’
AND NVL(APS.amount_in_dispute, 0) = 0
AND TRX.org_id = gn_org_id
AND APS.org_id = gn_org_id
AND NOT EXISTS (SELECT 1 FROM xxeur_ge_customer_refund_data DATA
WHERE TRX.customer_trx_id = DATA.customer_trx_id
AND TRX.org_id = DATA.org_id
AND DATA.refund_status_flag = ‘N’)
AND CUST.customer_number BETWEEN NVL(P_CUSTOMER_NUMBER_FROM,CUST.customer_number) AND NVL(P_CUSTOMER_NUMBER_TO,CUST.customer_number)
AND CUST.customer_name BETWEEN NVL(P_CUSTOMER_NAME_FROM,CUST.customer_name) AND NVL(P_CUSTOMER_NAME_TO,CUST.customer_name)
AND NVL(CUST.customer_class_code,’X’) BETWEEN NVL(p_cust_class_from, NVL(CUST.customer_class_code,’X’)) AND
NVL(p_cust_class_to, NVL(CUST.customer_class_code,’X’))
AND TRX.trx_date BETWEEN NVL(TO_DATE(GC_TRX_DATE_FROM,’DD-MON-YYYY’),TRX.trx_date)
AND NVL(TO_DATE(GC_TRX_DATE_TO,’DD-MON-YYYY’),TRX.trx_date)
AND NVL(ARM.name,’X’) BETWEEN NVL(P_RECEIPT_METHOD_FROM,NVL(ARM.name,’X’)) AND NVL(P_RECEIPT_METHOD_TO,NVL(ARM.name,’X’))
AND ((p_cust_numbers IS NULL
AND 1=1
)
OR (p_cust_numbers IS NOT NULL
AND CUST.customer_number IN (SELECT REGEXP_SUBSTR(p_cust_numbers,'[^,]+’, 1, LEVEL) FROM dual
CONNECT BY REGEXP_SUBSTR(p_cust_numbers, ‘[^,]+’, 1, LEVEL) IS NOT NULL)
)
)
)
WHERE total_due <= -5
AND total_due <= NVL((ABS(P_MIN_BALANCE_DUE)*-1),-5)
GROUP BY site_use_id,customer_number,customer_id,customer_name, location;
CURSOR lcu_data (p_site_use_id NUMBER)
IS
SELECT *
FROM (
SELECT TRX.customer_trx_id
,trx.trx_number
,CUST.customer_name
,CUST.customer_number
,DECODE(APS.class,’CM’,’Credit Memo’,’INV’,’Invoice’) class
–,DECODE(APS.class,’CM’,APS.amount_due_original,’INV’,APS.amount_due_remaining) balance_due
,APS.amount_due_remaining balance_due
,SUM(APS.amount_due_remaining)
OVER (PARTITION BY TRX.bill_to_site_use_id) AS total_due
,COUNT(APS.amount_due_remaining)
OVER (PARTITION BY TRX.bill_to_site_use_id) AS cust_rec_count
,APS.invoice_currency_code currency
,TO_CHAR(TRX.trx_date,’DD-MON-YYYY’) trx_date
,TO_CHAR(APS.gl_date,’DD-MON-YYYY’) gl_date
,ARM.name receipt_method
,HCSU.location
,HCSU.site_use_id
FROM ra_customer_trx_all TRX
,ar_payment_schedules_all APS
,ar_customers CUST
,ar_receipt_methods ARM
,hz_cust_site_uses_all HCSU
WHERE TRX.customer_trx_id = APS.customer_trx_id
AND TRX.bill_to_customer_id = CUST.customer_id
–AND TRX.bill_to_customer_id = 76863
AND TRX.receipt_method_id = ARM.receipt_method_id (+)
AND TRX.bill_to_site_use_id = HCSU.site_use_id
AND TRX.status_trx = ‘OP’
AND APS.status = ‘OP’
AND NVL(APS.amount_in_dispute, 0) = 0
AND TRX.org_id = gn_org_id
AND APS.org_id = gn_org_id
AND NOT EXISTS (SELECT 1 FROM xxeur_ge_customer_refund_data DATA
WHERE TRX.customer_trx_id = DATA.customer_trx_id
AND TRX.org_id = DATA.org_id
AND DATA.refund_status_flag = ‘N’)
AND TRX.trx_date BETWEEN NVL(TO_DATE(GC_TRX_DATE_FROM,’DD-MON-YYYY’),TRX.trx_date)
AND NVL(TO_DATE(GC_TRX_DATE_TO,’DD-MON-YYYY’),TRX.trx_date)
AND NVL(ARM.name,’X’) BETWEEN NVL(P_RECEIPT_METHOD_FROM,NVL(ARM.name,’X’)) AND NVL(P_RECEIPT_METHOD_TO,NVL(ARM.name,’X’))
AND HCSU.site_use_id = p_site_use_id
)
WHERE total_due <= -5
AND total_due <= NVL((ABS(P_MIN_BALANCE_DUE)*-1), -5);
CURSOR lcu_org_name (p_org_id IN NUMBER)
IS
SELECT name
FROM hr_operating_units
WHERE organization_id = p_org_id;
BEGIN
gc_trx_date_from := TO_CHAR(TO_DATE(p_trx_date_from,’YYYY/MM/DD HH24:MI:SS’),’DD-MON-YYYY’);
gc_trx_date_to := TO_CHAR(TO_DATE(p_trx_date_to,’YYYY/MM/DD HH24:MI:SS’),’DD-MON-YYYY’);
DBMS_OUTPUT.PUT_LINE(‘TEST’);
fnd_file.put_line (fnd_file.log,RPAD(‘*’,80,’*’));
fnd_file.put_line (fnd_file.log,’ ——— Calling Report Program ———‘);
fnd_file.put_line (fnd_file.log,RPAD(‘ ‘,80,’ ‘));
OPEN lcu_org_name(gn_org_id);
FETCH lcu_org_name INTO lc_org_name;
CLOSE lcu_org_name;
BEGIN
SELECT transmission_id
INTO gn_transmission_id
FROM ar_transmissions_all
WHERE transmission_name = ‘Customer Refund’
AND org_id = gn_org_id;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,’**** ERROR -> Transmission Name – Customer Refund – is not defined in the system ‘||SQLERRM);
END;
gc_lockbox_num := ‘Customer Refund’;
IF UPPER(p_option) = ‘REPORT’ THEN
fnd_file.put_line (fnd_file.log,RPAD(‘*’,80,’*’));
fnd_file.put_line (fnd_file.log,’ ——— Calling Report Program ———‘);
fnd_file.put_line (fnd_file.log,RPAD(‘ ‘,80,’ ‘));
lb_set_layout_option := apps.fnd_request.add_layout
(template_appl_name => ‘APL’
,template_code => ‘XXEURGENEGRECAPPXML’ — template_name from XML_Publisher
,template_language => ‘en’
,template_territory => ‘US’
,output_format => ‘EXCEL’);
ln_request_id := apps.fnd_request.submit_request
(
application => ‘APL’
,PROGRAM => ‘XXEURGENEGRECAPPXML’
,description => NULL
,start_time => SYSDATE
,sub_request => FALSE
,argument1 => p_customer_name_from
,argument2 => p_customer_name_to
,argument3 => p_customer_number_from
,argument4 => p_customer_number_to
,argument5 => gc_trx_date_from –TO_CHAR(ld_trx_date_from,’DD-MON-YYYY’)
,argument6 => gc_trx_date_to –TO_CHAR(ld_trx_date_to,’DD-MON-YYYY’)
,argument7 => p_min_balance_due
,argument8 => p_receipt_method_from
,argument9 => p_receipt_method_to
,argument10 => p_cust_class_from
,argument11 => p_cust_class_to
,argument12 => p_cust_numbers
);
fnd_file.put_line(fnd_file.LOG,’Request Id- ‘||ln_request_id);
IF ln_request_id > 0 THEN
COMMIT;
lb_complete :=
fnd_concurrent.wait_for_request (request_id => ln_request_id
,interval => 2
,max_wait => 9999
,phase => lc_phase
,status => lc_status
,dev_phase => lc_dev_phase
,dev_status => lc_dev_status
,message => lc_message
);
COMMIT;
IF UPPER (lc_dev_phase) IN (‘COMPLETE’) THEN
fnd_file.put_line(fnd_file.log,(‘Concurrent request completed successfully :’||ln_request_id));
END IF;
END IF;
fnd_file.put_line (fnd_file.log,RPAD(‘*’,80,’*’));
fnd_file.put_line (fnd_file.log,’ ——— Report Program Completed ———‘);
fnd_file.put_line (fnd_file.log,RPAD(‘ ‘,80,’ ‘));
ELSIF UPPER(p_option) = ‘CREATE’ THEN
fnd_file.put_line (fnd_file.output,RPAD(‘*’,150,’*’));
fnd_file.put_line(fnd_file.output,
‘================================================================= Interface Summary =========================================================’
);
fnd_file.put_line (fnd_file.output,RPAD(‘*’,150,’*’));
fnd_file.put_line (fnd_file.output,”);
fnd_file.put_line (fnd_file.output,”);
fnd_file.put_line (fnd_file.output,RPAD(‘=’,150,’=’));
fnd_file.put_line (fnd_file.output,
RPAD (‘Receipt Number’, 17)
|| RPAD (‘No.Of Invoices’, 20)
|| RPAD (‘Customer Name’, 50)
|| RPAD (‘Customer Number’, 20)
|| RPAD (‘Bill To Location’, 25)
);
fnd_file.put_line (fnd_file.output,RPAD(‘=’,150,’=’));
IF gn_transmission_id IS NOT NULL
THEN
BEGIN
UPDATE ar_transmissions_all
SET requested_trans_format_id = (SELECT transmission_format_id FROM ar_transmission_formats
WHERE format_name = ‘XXEUR Transmission Format’)
WHERE transmission_id = gn_transmission_id;
END;
fnd_file.put_line (fnd_file.log,’*** Stage 1′);
FOR j in lcu_bill_to_customer
LOOP
lc_invoice1 := NULL;
lc_invoice2 := NULL;
lc_invoice3 := NULL;
lc_invoice4 := NULL;
lc_invoice5 := NULL;
lc_invoice6 := NULL;
lc_invoice7 := NULL;
lc_invoice8 := NULL;
ln_amount_applied1 := NULL;
ln_amount_applied2 := NULL;
ln_amount_applied3 := NULL;
ln_amount_applied4 := NULL;
ln_amount_applied5 := NULL;
ln_amount_applied6 := NULL;
ln_amount_applied7 := NULL;
ln_amount_applied8 := NULL;
ln_receipt_number := xxeur_ge_receipt_number_s.NEXTVAL;
ln_item_number := xxeur_ge_item_number_s.NEXTVAL;
IF j.inv_count < 9 THEN
ln_inv_cnt := 0;
ln_batch_cnt := 1;
fnd_file.put_line (fnd_file.log,’*** Stage 2′);
FOR inv_rec IN lcu_data(j.site_use_id)
LOOP
ln_inv_cnt := ln_inv_cnt + 1;
IF ln_inv_cnt = 1 THEN
lc_invoice1 := inv_rec.trx_number;
ln_amount_applied1 := inv_rec.balance_due;
fnd_file.put_line (fnd_file.log,’*** Stage 3′);
ELSIF ln_inv_cnt = 2 THEN
lc_invoice2 := inv_rec.trx_number;
ln_amount_applied2 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 3 THEN
lc_invoice3 := inv_rec.trx_number;
ln_amount_applied3 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 4 THEN
lc_invoice4 := inv_rec.trx_number;
ln_amount_applied4 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 5 THEN
lc_invoice5 := inv_rec.trx_number;
ln_amount_applied5 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 6 THEN
lc_invoice6 := inv_rec.trx_number;
ln_amount_applied6 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 7 THEN
lc_invoice7 := inv_rec.trx_number;
ln_amount_applied7 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 8 THEN
lc_invoice8 := inv_rec.trx_number;
ln_amount_applied8 := inv_rec.balance_due;
END IF;
xxeur_ge_NEG_RECEIPT_APP.insert_inv_data (p_check_number => ln_receipt_number
,p_customer_site_use_id => j.site_use_id
,p_invoice => inv_rec.trx_number
,p_amount_applied => inv_rec.balance_due
,p_transmission_id => gn_transmission_id
,p_account => NVL(p_account, ‘1754420’)
,p_customer_trx_id => inv_rec.customer_trx_id
);
END LOOP;
fnd_file.put_line (fnd_file.log,’*** Stage 4′);
insert_lockbox_rec (
p_record_type => ‘5’
,p_batch_cnt => ln_batch_cnt
,p_receipt_number => ln_receipt_number
,p_customer_number => j.customer_number
,p_invoice1 => lc_invoice1
,p_invoice2 => lc_invoice2
,p_invoice3 => lc_invoice3
,p_invoice4 => lc_invoice4
,p_invoice5 => lc_invoice5
,p_invoice6 => lc_invoice6
,p_invoice7 => lc_invoice7
,p_invoice8 => lc_invoice8
,p_amount_applied1 => ln_amount_applied1
,p_amount_applied2 => ln_amount_applied2
,p_amount_applied3 => ln_amount_applied3
,p_amount_applied4 => ln_amount_applied4
,p_amount_applied5 => ln_amount_applied5
,p_amount_applied6 => ln_amount_applied6
,p_amount_applied7 => ln_amount_applied7
,p_amount_applied8 => ln_amount_applied8
,p_customer_id => j.customer_id
,p_site_use_id => j.site_use_id
,p_transmission_id => gn_transmission_id
,p_item_number => ln_item_number
,p_overflow_seq => ln_overflow_cnt
,p_account_num => NVL(p_account,’1234567890′)
);
ELSE
ln_inv_cnt := 0;
ln_batch_cnt := ceil(j.inv_count/8);
fnd_file.put_line (fnd_file.log,’*** Stage 2′);
ln_overflow_cnt := 0;
FOR inv_rec IN lcu_data(j.site_use_id)
LOOP
ln_inv_cnt := ln_inv_cnt + 1;
IF ln_overflow_cnt = 0 THEN
lc_record_type := ‘5’;
ELSE
lc_record_type := ‘3’;
END IF;
IF ln_inv_cnt = 1 THEN
lc_invoice1 := inv_rec.trx_number;
ln_amount_applied1 := inv_rec.balance_due;
fnd_file.put_line (fnd_file.log,’*** Stage 3′);
ELSIF ln_inv_cnt = 2 THEN
lc_invoice2 := inv_rec.trx_number;
ln_amount_applied2 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 3 THEN
lc_invoice3 := inv_rec.trx_number;
ln_amount_applied3 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 4 THEN
lc_invoice4 := inv_rec.trx_number;
ln_amount_applied4 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 5 THEN
lc_invoice5 := inv_rec.trx_number;
ln_amount_applied5 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 6 THEN
lc_invoice6 := inv_rec.trx_number;
ln_amount_applied6 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 7 THEN
lc_invoice7 := inv_rec.trx_number;
ln_amount_applied7 := inv_rec.balance_due;
ELSIF ln_inv_cnt = 8 THEN
lc_invoice8 := inv_rec.trx_number;
ln_amount_applied8 := inv_rec.balance_due;
insert_lockbox_rec (
p_record_type => lc_record_type
,p_batch_cnt => ln_batch_cnt
,p_receipt_number => ln_receipt_number
,p_customer_number => j.customer_number
,p_invoice1 => lc_invoice1
,p_invoice2 => lc_invoice2
,p_invoice3 => lc_invoice3
,p_invoice4 => lc_invoice4
,p_invoice5 => lc_invoice5
,p_invoice6 => lc_invoice6
,p_invoice7 => lc_invoice7
,p_invoice8 => lc_invoice8
,p_amount_applied1 => ln_amount_applied1
,p_amount_applied2 => ln_amount_applied2
,p_amount_applied3 => ln_amount_applied3
,p_amount_applied4 => ln_amount_applied4
,p_amount_applied5 => ln_amount_applied5
,p_amount_applied6 => ln_amount_applied6
,p_amount_applied7 => ln_amount_applied7
,p_amount_applied8 => ln_amount_applied8
,p_customer_id => j.customer_id
,p_site_use_id => j.site_use_id
,p_transmission_id => gn_transmission_id
,p_item_number => ln_item_number
,p_overflow_seq => ln_overflow_cnt
,p_account_num => NVL(p_account, ‘1234567890’)
);
fnd_file.put_line (fnd_file.log,’*** Stage 4′);
lc_invoice1 := NULL;
lc_invoice2 := NULL;
lc_invoice3 := NULL;
lc_invoice4 := NULL;
lc_invoice5 := NULL;
lc_invoice6 := NULL;
lc_invoice7 := NULL;
lc_invoice8 := NULL;
ln_amount_applied1 := NULL;
ln_amount_applied2 := NULL;
ln_amount_applied3 := NULL;
ln_amount_applied4 := NULL;
ln_amount_applied5 := NULL;
ln_amount_applied6 := NULL;
ln_amount_applied7 := NULL;
ln_amount_applied8 := NULL;
ln_inv_cnt := 0;
ln_overflow_cnt := ln_overflow_cnt + 1;
END IF;
xxeur_ge_NEG_RECEIPT_APP.insert_inv_data (p_check_number => ln_receipt_number
,p_customer_site_use_id => j.site_use_id
,p_invoice => inv_rec.trx_number
,p_amount_applied => inv_rec.balance_due
,p_transmission_id => gn_transmission_id
,p_account => NVL(p_account, ‘1234567890’)
,p_customer_trx_id => inv_rec.customer_trx_id
);
END LOOP;
IF ln_inv_cnt < 8 THEN
insert_lockbox_rec (
p_record_type => lc_record_type
,p_batch_cnt => ln_batch_cnt
,p_receipt_number => ln_receipt_number
,p_customer_number => j.customer_number
,p_invoice1 => lc_invoice1
,p_invoice2 => lc_invoice2
,p_invoice3 => lc_invoice3
,p_invoice4 => lc_invoice4
,p_invoice5 => lc_invoice5
,p_invoice6 => lc_invoice6
,p_invoice7 => lc_invoice7
,p_invoice8 => lc_invoice8
,p_amount_applied1 => ln_amount_applied1
,p_amount_applied2 => ln_amount_applied2
,p_amount_applied3 => ln_amount_applied3
,p_amount_applied4 => ln_amount_applied4
,p_amount_applied5 => ln_amount_applied5
,p_amount_applied6 => ln_amount_applied6
,p_amount_applied7 => ln_amount_applied7
,p_amount_applied8 => ln_amount_applied8
,p_customer_id => j.customer_id
,p_site_use_id => j.site_use_id
,p_transmission_id => gn_transmission_id
,p_item_number => ln_item_number
,p_overflow_seq => ln_overflow_cnt
,p_account_num => NVL(p_account, ‘1234567890’)
);
fnd_file.put_line (fnd_file.log,’*** Stage 4′);
END IF;
END IF;
UPDATE ar_payments_interface_all
SET overflow_indicator = ‘9’
WHERE item_number = ln_item_number
AND OVERFLOW_SEQUENCE IN (SELECT MAX(OVERFLOW_SEQUENCE) FROM ar_payments_interface_all
WHERE item_number = ln_item_number
)
AND overflow_indicator IS NOT NULL;
COMMIT;
BEGIN
UPDATE ar_transmissions_all
SET validated_count = 0
,VALIDATED_AMOUNT = 0
,ORIGIN = NULL
,status = ‘NB’
,requested_trans_format_id = (SELECT transmission_format_id FROM ar_transmission_formats
WHERE format_name = ‘XXEUR Transmission Format’)
,REQUESTED_LOCKBOX_ID = NULL
,REQUESTED_GL_DATE = NULL
WHERE transmission_id = gn_transmission_id;
END;
COMMIT;
fnd_file.put_line (fnd_file.output,
RPAD (ln_item_number, 17)
|| RPAD (j.inv_count, 20)
|| RPAD (j.customer_name, 50)
|| RPAD (j.customer_number, 20)
|| RPAD (j.location, 25)
);
END LOOP;
fnd_file.put_line (fnd_file.output,RPAD(‘=’,150,’=’));
END IF;
END IF;
END main;
PROCEDURE create_refund(errbuf OUT VARCHAR2
,retcode OUT NUMBER
)
IS
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (4000);
l_msg_data_temp VARCHAR2 (4000);
l_cash_receipt_id NUMBER;
p_count NUMBER;
l_status_flag VARCHAR2 (100);
l_error_msg VARCHAR2 (100);
l_application_ref_type ar_receivable_applications.application_ref_type%TYPE;
l_application_ref_id ar_receivable_applications.application_ref_id%TYPE;
l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
l_secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE;
l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
l_trx_id NUMBER;
l_org_id NUMBER;
lc_receipt_number VARCHAR2(100);
ln_cash_receipt_id NUMBER;
ln_term_id NUMBER;
ln_ext_bank_account_id NUMBER;
lc_payment_method VARCHAR2(5);
lc_error_msg VARCHAR2(4000);
lc_paygroup VARCHAR2(30);
lc_remittance_message1 VARCHAR2(150);
lc_remittance_message2 VARCHAR2(150);
lc_remittance_message3 VARCHAR2(150);
CURSOR lcu_main
IS
SELECT DISTINCT REC.cash_receipt_id, DATA.check_number,DATA.transmission_id, REC.org_id, REC.receipt_number,DATA.customer_site_use_id
FROM ar_cash_receipts_all REC
,xxeur_ge_customer_refund_data DATA
WHERE REC.receipt_number = DATA.check_number
AND REC.org_id = DATA.org_id
AND REC.receipt_date = DATA.receipt_date
AND REC.customer_site_use_id = DATA.customer_site_use_id
AND DATA.refund_status_flag IN (‘N’,’E’)
AND REC.org_id = gn_org_id;
CURSOR lcu_apply_trx (p_cash_receipt_id NUMBER)
IS
SELECT RCT.trx_number, APP.apply_date, ROWNUM rec_num
FROM ra_customer_trx_all RCT
,ar_receivable_applications_all APP
WHERE RCT.customer_trx_id = APP.applied_customer_trx_id
AND APP.cash_receipt_id = p_cash_receipt_id
AND APP.display = ‘Y’
AND APP.applied_customer_trx_id IS NOT NULL
AND ROWNUM < 5
ORDER BY APP.apply_date;
BEGIN
mo_global.init (‘AR’);
–fnd_global.apps_initialize (7683, 54312, 222);
fnd_global.apps_initialize (fnd_profile.VALUE (‘USER_ID’),
fnd_profile.VALUE (‘RESP_ID’),
fnd_profile.VALUE (‘RESP_APPL_ID’),
NULL,
NULL
);
BEGIN
SELECT term_id
INTO ln_term_id
FROM ap_terms
WHERE UPPER(name) like ‘EU-IMMEDIATE’;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.LOG,
‘Payment Term EU-IMMEDIATE is not defined in the system. ‘
);
END;
BEGIN
SELECT RECEIVABLES_TRX_ID
INTO l_trx_id
FROM AR_RECEIVABLES_TRX_ALL
WHERE org_id = gn_org_id AND TYPE = ‘CM_REFUND’;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG,’ Receivable trx id for CM_REFUND does not exists for org_id : ‘
|| gn_org_id);
l_status_flag := ‘E’;
lc_error_msg := lc_error_msg||’ Receivable trx id for CM_REFUND does not exists,’;
END;
BEGIN
SELECT lookup_code
INTO lc_paygroup
FROM apps.fnd_lookup_values_vl
WHERE lookup_type = ‘PAY GROUP’
AND (UPPER(lookup_code) = ‘CUSTOMER REFUND’
OR UPPER(meaning) = ‘CUSTOMER REFUND’);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG,’ Pay Group CUSTOMER REFUND is not exists in Oracle : ‘
|| gn_org_id);
l_status_flag := ‘E’;
lc_error_msg := lc_error_msg||’ Pay Group CUSTOMER REFUND is not exists in Oracle,’;
END;
IF ln_term_id IS NOT NULL
THEN
fnd_file.put_line (fnd_file.output,’*********** Refund Summary ***********’);
fnd_file.put_line (fnd_file.output,(”));
fnd_file.put_line (fnd_file.output,RPAD(‘=’,40,’=’));
fnd_file.put_line (fnd_file.output,
RPAD (‘Receipt Number’, 20)
|| RPAD (‘Refund Status’, 20)
|| ‘Error Message’
);
fnd_file.put_line (fnd_file.output,RPAD(‘=’,40,’=’));
mo_global.set_policy_context (‘S’, gn_org_id);
FOR i in lcu_main
LOOP
l_return_status := NULL;
l_msg_count := NULL;
l_msg_data := NULL;
l_msg_data_temp := NULL;
l_cash_receipt_id := NULL;
l_status_flag := ‘S’;
l_error_msg := NULL;
l_application_ref_type := NULL;
l_application_ref_id := NULL;
l_application_ref_num := NULL;
l_secondary_application_ref_id := NULL;
l_receivable_application_id := NULL;
–l_trx_id := NULL;
–l_org_id := i.org_id;
–lc_receipt_number := ‘158’;
ln_cash_receipt_id := NULL;
ln_ext_bank_account_id := NULL;
lc_payment_method := NULL;
lc_error_msg := NULL;
BEGIN
SELECT eb.ext_bank_account_id
INTO ln_ext_bank_account_id
FROM iby_external_payers_all ie
,iby_pmt_instr_uses_all ip
,iby_ext_bank_accounts eb
,hz_parties hp
WHERE 1 = 1
AND ip.ext_pmt_party_id = ie.ext_payer_id
AND eb.ext_bank_account_id = ip.instrument_id
AND hp.party_id = ie.party_id
AND ip.instrument_type = ‘BANKACCOUNT’
AND ie.acct_site_use_id = i.customer_site_use_id
AND eb.end_date IS NULL
AND ip.end_date IS NULL
and ip.ORDER_OF_PREFERENCE = ‘1’
AND ip.payment_function = ‘CUSTOMER_PAYMENT’;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG,’ Customer Bank Account Does not exists : ‘
||gn_org_id);
l_status_flag := ‘E’;
lc_error_msg := lc_error_msg||’ Customer Bank Account Does not exists,’;
END;
lc_remittance_message1 := NULL;
lc_remittance_message2 := NULL;
lc_remittance_message3 := NULL;
FOR j IN lcu_apply_trx(i.cash_receipt_id)
LOOP
IF j.rec_num = 1
THEN
lc_remittance_message1 := j.trx_number;
ELSIF j.rec_num = 2
THEN
lc_remittance_message1 := lc_remittance_message1||’,’||j.trx_number;
ELSIF j.rec_num = 3
THEN
lc_remittance_message1 := lc_remittance_message1||’,’||j.trx_number;
ELSIF j.rec_num = 3
THEN
lc_remittance_message2 := ‘More Than 3 Invoices’;
END IF;
END LOOP;
/*BEGIN
SELECT ‘TRF’
INTO lc_payment_method
FROM ar_cust_receipt_methods_v
WHERE site_use_id =128815
AND UPPER(RECEIPT_METHOD_NAME) LIKE ‘DIRECT%DEBIT%’;
EXCEPTION
WHEN OTHERS
THEN
lc_payment_method := ‘WIRE’;
END;*/
IF l_status_flag = ‘S’
THEN
ar_receipt_api_pub.activity_application (
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_cash_receipt_id => i.cash_receipt_id
,p_applied_payment_schedule_id => -8
,p_receivables_trx_id => l_trx_id
,p_apply_date => TRUNC(SYSDATE)
,p_apply_gl_date => TRUNC(SYSDATE) –G_GL_DATE,
,p_receivable_application_id => l_receivable_application_id
,p_application_ref_type => l_application_ref_type
,p_application_ref_id => l_application_ref_id
,p_application_ref_num => l_application_ref_num
,p_secondary_application_ref_id => l_secondary_application_ref_id
,p_payment_method_code => ‘TRF’ –lc_payment_method, — Modified by Anand on 10-Sep-2015 by Varun comments
,p_bank_account_id => ln_ext_bank_account_id
,p_terms_id => ln_term_id
,p_pay_group_lookup_code => lc_paygroup
,p_remittance_message1 => lc_remittance_message1
,p_remittance_message2 => lc_remittance_message2
–,p_remittance_message3 => lc_remittance_message3
);
DBMS_OUTPUT.PUT_LINE (
‘l_return_status -> ‘ || l_return_status
);
fnd_file.put_line(fnd_file.LOG,’l_return_status -> ‘ || l_return_status
);
— 3) Review the API output
IF l_return_status <> ‘S’
THEN
l_status_flag := ‘E’;
IF l_msg_count = 1
THEN
l_msg_data_temp := l_msg_data;
DBMS_OUTPUT.PUT_LINE (
‘Message ‘ || p_count || ‘. ‘ || l_msg_data
);
fnd_file.put_line(fnd_file.LOG,
‘Message ‘ || p_count || ‘. ‘ || l_msg_data
);
ELSIF l_msg_count > 1
THEN
FOR i IN 1 .. l_msg_count
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
l_msg_data_temp := l_msg_data_temp || ‘ – ‘ || l_msg_data;
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE (
‘Message ‘ || p_count || ‘. ‘
|| l_msg_data
);
fnd_file.put_line(fnd_file.LOG,
‘Message ‘ || p_count || ‘. ‘
|| l_msg_data
);
END LOOP;
END IF;
fnd_file.put_line (fnd_file.output,
RPAD (i.receipt_number, 20)
|| RPAD (‘Error’, 20)
|| l_msg_data
);
UPDATE xxeur_ge_customer_refund_data
SET refund_status_flag = ‘E’
,error_message = l_msg_data
WHERE transmission_id = i.transmission_id
AND check_number = i.receipt_number
AND org_id = i.org_id;
ELSE
fnd_file.put_line (fnd_file.output,
RPAD (i.receipt_number, 20)
|| RPAD (‘Success’, 20)
);
UPDATE xxeur_ge_customer_refund_data
SET refund_status_flag = ‘P’
WHERE transmission_id = i.transmission_id
AND check_number = i.receipt_number
AND org_id = i.org_id;
END IF;
ELSE
UPDATE xxeur_ge_customer_refund_data
SET refund_status_flag = ‘E’
,error_message = lc_error_msg
WHERE transmission_id = i.transmission_id
AND check_number = i.receipt_number
AND org_id = i.org_id;
END IF;
END LOOP;
fnd_file.put_line (fnd_file.output,RPAD(‘=’,40,’=’));
END IF;
COMMIT;
END create_refund;
PROCEDURE insert_lockbox_rec (p_record_type IN VARCHAR2
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice1 IN VARCHAR2
,p_invoice2 IN VARCHAR2
,p_invoice3 IN VARCHAR2
,p_invoice4 IN VARCHAR2
,p_invoice5 IN VARCHAR2
,p_invoice6 IN VARCHAR2
,p_invoice7 IN VARCHAR2
,p_invoice8 IN VARCHAR2
,p_amount_applied1 IN NUMBER
,p_amount_applied2 IN NUMBER
,p_amount_applied3 IN NUMBER
,p_amount_applied4 IN NUMBER
,p_amount_applied5 IN NUMBER
,p_amount_applied6 IN NUMBER
,p_amount_applied7 IN NUMBER
,p_amount_applied8 IN NUMBER
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
)
AS
lc_overflow_indicator VARCHAR2(1);
ln_batch_amount NUMBER;
ln_remittance_amount NUMBER;
ld_receipt_date DATE;
lc_receipt_method VARCHAR2(50);
lc_receipt_number VARCHAR2(100);
lc_customer_number VARCHAR2(100);
ln_customer_id NUMBER;
ln_site_use_id NUMBER;
ln_overflow_seq NUMBER;
BEGIN
IF p_record_type = 3
THEN
lc_overflow_indicator := 0;
ln_batch_amount := NULL;
ln_remittance_amount := NULL;
lc_receipt_number := NULL;
lc_customer_number := NULL;
ln_customer_id := NULL;
ln_site_use_id := NULL;
ld_receipt_date := NULL;
ln_overflow_seq := p_overflow_seq;
ELSE
lc_overflow_indicator := NULL;
ln_batch_amount := 0;
ln_remittance_amount := 0;
ln_overflow_seq := 1;
lc_receipt_number := p_receipt_number;
lc_customer_number := p_customer_number;
ln_customer_id := p_customer_id;
ln_site_use_id := p_site_use_id ;
ld_receipt_date := TRUNC(SYSDATE);
lc_receipt_method := ‘Customer Refund – DE’;
END IF;
BEGIN
INSERT INTO ar_payments_interface_all(TRANSMISSION_RECORD_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECORD_TYPE
,STATUS
,LOCKBOX_NUMBER
–,BATCH_NAME
,BATCH_AMOUNT
,BATCH_RECORD_COUNT
,REMITTANCE_AMOUNT
,CHECK_NUMBER
,CUSTOMER_NUMBER
,INVOICE1
,INVOICE2
,INVOICE3
,INVOICE4
,INVOICE5
,INVOICE6
,INVOICE7
,INVOICE8
,AMOUNT_APPLIED1
,AMOUNT_APPLIED2
,AMOUNT_APPLIED3
,AMOUNT_APPLIED4
,AMOUNT_APPLIED5
,AMOUNT_APPLIED6
,AMOUNT_APPLIED7
,AMOUNT_APPLIED8
,GL_DATE
,COMMENTS
,CUSTOMER_ID
,RECEIPT_METHOD
,CUSTOMER_SITE_USE_ID
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
,ITEM_NUMBER
,ACCOUNT
,overflow_sequence
,OVERFLOW_INDICATOR
)
values(ar_payments_interface_s.NEXTVAL — TRANSMISSION_RECORD_ID
,TRUNC(SYSDATE) — CREATION_DATE
,fnd_profile.value(‘USER_ID’) — CREATED_BY
,fnd_profile.value(‘USER_ID’) — LAST_UPDATE_LOGIN
,fnd_profile.value(‘USER_ID’) — LAST_UPDATED_BY
,TRUNC(SYSDATE) — LAST_UPDATE_DATE
,p_record_type — RECORD_TYPE
,’AR_PLB_NEW_RECORD’ — STATUS
,gc_lockbox_num — LOCKBOX_NUMBER
–,’V1′ — BATCH_NAME
,ln_batch_amount — BATCH_AMOUNT
,p_batch_cnt — BATCH_RECORD_COUNT
,ln_remittance_amount — REMITTANCE_AMOUNT
,lc_receipt_number — CHECK_NUMBER
,lc_customer_number — CUSTOMER_NUMBER
,p_invoice1 — INVOICE1
,p_invoice2 — INVOICE2
,p_invoice3 — INVOICE3
,p_invoice4 — INVOICE4
,p_invoice5 — INVOICE5
,p_invoice6 — INVOICE6
,p_invoice7 — INVOICE7
,p_invoice8 — INVOICE8
,p_amount_applied1 — AMOUNT_APPLIED1
,p_amount_applied2 — AMOUNT_APPLIED2
,p_amount_applied3 — AMOUNT_APPLIED3
,p_amount_applied4 — AMOUNT_APPLIED4
,p_amount_applied5 — AMOUNT_APPLIED5
,p_amount_applied6 — AMOUNT_APPLIED6
,p_amount_applied7 — AMOUNT_APPLIED7
,p_amount_applied8 — AMOUNT_APPLIED8
,TRUNC(SYSDATE) — GL_DATE
,’Initiated from Refund Process’ — COMMENTS
,ln_customer_id — CUSTOMER_ID
,lc_RECEIPT_METHOD — RECEIPT_METHOD
,ln_site_use_id — CUSTOMER_SITE_USE_ID
,ld_receipt_date — RECEIPT_DATE
,gn_org_id –1163 — ORG_ID
,gn_transmission_id — TRANSMISSION_ID
,p_item_number — ITEM_NUMBER
,p_account_num — ACCOUNT
,ln_overflow_seq
,lc_overflow_indicator
);
END;
COMMIT;
END insert_lockbox_rec;
PROCEDURE insert_inv_data (p_check_number IN VARCHAR2
,p_customer_site_use_id IN NUMBER
,p_invoice IN VARCHAR2
,p_amount_applied IN NUMBER
,p_transmission_id IN NUMBER
,p_account IN VARCHAR2
,p_customer_trx_id IN NUMBER
)
AS
BEGIN
INSERT INTO xxeur_ge_CUSTOMER_REFUND_DATA(RECORD_ID
–,TRANSMISSION_RECORD_ID
–,RECORD_TYPE
–,BATCH_NAME
–,BATCH_AMOUNT
–,BATCH_RECORD_COUNT
–,REMITTANCE_AMOUNT
,CHECK_NUMBER
–,CUSTOMER_NUMBER
–,CUSTOMER_ID
,CUSTOMER_SITE_USE_ID
,CUSTOMER_TRX_ID
,INVOICE
,AMOUNT_APPLIED
,ACCOUNT
,GL_DATE
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
–,ITEM_NUMBER
–,RECEIPT_STATUS_FLAG
,REFUND_STATUS_FLAG
,CREATION_DATE
,CREATED_BY
)
VALUES (xxeur_ge_customer_ref_data_s.nextval — RECORD_ID
–,TRANSMISSION_RECORD_ID
–,RECORD_TYPE
–,BATCH_NAME
–,BATCH_AMOUNT
–,BATCH_RECORD_COUNT
–,REMITTANCE_AMOUNT
,p_CHECK_NUMBER — CHECK_NUMBER
–,CUSTOMER_NUMBER
–,CUSTOMER_ID
,p_CUSTOMER_SITE_USE_ID — CUSTOMER_SITE_USE_ID
,p_customer_trx_id
,p_INVOICE — INVOICE
,p_AMOUNT_APPLIED — AMOUNT_APPLIED
,p_ACCOUNT — p_ACCOUNT
,TRUNC(SYSDATE) — GL_DATE
,TRUNC(SYSDATE) –RECEIPT_DATE
,gn_org_id –ORG_ID
,gn_transmission_id — TRANSMISSION_ID
–,ITEM_NUMBER
–,RECEIPT_STATUS_FLAG
,’N’ –REFUND_STATUS_FLAG
,TRUNC(SYSDATE) — CREATION_DATE
,fnd_profile.value(‘USER_ID’) — CREATED_BY
);
COMMIT;
END insert_inv_data;
END XXEUR_GE_NEG_RECEIPT_APP;
/