create or replace package body XBOL_SORDER_PKG is
p_batch_process VARCHAR2 (1);
gc_user_name VARCHAR2 (100);
gc_responsibility_name VARCHAR2 (100);
gc_application_short_name VARCHAR2 (100);
gc_org_id NUMBER;
-- This procedure will CREATE customer invoices in ra_customer_trx_table.
-------------------------------------------------------------------------
PROCEDURE XBOL_CREATE_AR_TRANSACTION(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_wonum IN VARCHAR2)
IS
ip_trx_number NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_batch_id NUMBER;
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_trx_contingencies_tbl ar_invoice_api_pub.trx_contingencies_tbl_type;
trx_header_id_v NUMBER;
trx_line_id_v NUMBER;
trx_dist_id_v NUMBER;
ip_code_combination_id VARCHAR2 (80);
ip_org_id NUMBER;
lv_msg_data VARCHAR2 (1000);
lv_ret_code VARCHAR2 (1);
lv_msg_data_receipt VARCHAR2 (1000);
lv_ret_code_receipt VARCHAR2 (1);
lp_receipt_id NUMBER;
lp_org_id NUMBER;
v_trx_Id NUMBER;
lv_msg_data_misc VARCHAR2 (1000);
l_batch_source_id NUMBER;
l_default_term_id NUMBER;
l_cust_trx_type_id NUMBER;
l_customer_id NUMBER;
l_customer_number VARCHAR2 (30);
l_project_code VARCHAR2 (240);
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_error_flag VARCHAR2 (1):='S';
l_error_msg VARCHAR2 (1000);
l_bank_name VARCHAR2 (100);
l_bank_account_id NUMBER (20);
l_trx_header_id NUMBER;
l_trx_error_header_id NUMBER;
l_desc VARCHAR2 (100);
l_wonum VARCHAR2 (100);
l_bankrefnum VARCHAR2 (50);
CURSOR c_data1
IS
SELECT *
FROM XBOL.XXAR_INV_REC_EXTERNAL
WHERE NVL(STATUS,'A') NOT IN ('C')
AND paymentmode IN ('ONLINE', 'CASH')
AND ( (wonum = p_wonum) OR (p_wonum IS NULL));
CURSOR cBatch IS
select customer_trx_id
from ra_customer_trx_all
where batch_id = l_batch_id;
CURSOR cValidTxn IS
SELECT trx_header_id
From ar_trx_header_gt
WHERE trx_header_id not in (
SELECT trx_header_id
FROM ar_trx_errors_gt);
Begin
mo_global.set_policy_context('S',140);
FOR c_dt IN c_data1
LOOP
l_bankrefnum := c_dt.bankrefnum;
IF c_dt.description IS NULL
THEN
l_desc := c_dt.paymentmode;
ELSE
l_desc := c_dt.description;
END IF;
l_wonum := NVL (p_wonum, c_dt.wonum);
SELECT XBOL_AR_TRX_ID_S.NEXTVAL
INTO trx_header_id_v
FROM DUAL;
SELECT XBOL_AR_TRX_LINEID_S.NEXTVAL
INTO trx_line_id_v
FROM DUAL;
SELECT XBOL_AR_TRX_DISTID_S.NEXTVAL
INTO trx_dist_id_v
FROM DUAL;
SELECT b.BATCH_SOURCE_ID,
c.DEFAULT_TERM Term_id,
c.CUST_TRX_TYPE_ID
INTO l_batch_source_id, l_default_term_id, l_cust_trx_type_id
FROM hr_operating_units a,
ra_batch_sources_all b,
ra_cust_trx_types_all c
WHERE a.organization_id = b.org_id
AND a.organization_id = c.org_id
AND a.organization_id ='140'
AND b.NAME = 'MANUAL'
AND c.name IN
(SELECT meaning
FROM ar_lookups arl
WHERE lookup_type = 'XX_EPAY_TRX_TYPES'
AND lookup_code ='140')
AND c.END_DATE IS NULL;
fnd_file.put_line (fnd_file.LOG, 'FOR LOOP :'|| ' BANK REFE' ||l_bankrefnum);
BEGIN
SELECT
description
INTO l_project_code
FROM fnd_lookup_values_vl
WHERE enabled_flag = 'Y'
AND lookup_code = '140'
AND lookup_type = 'XX_EPAY_ACC_DET';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_project_code := '000';
WHEN OTHERS
THEN
l_error_flag:='E';
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Error Finding Project Code ' || SQLERRM);
END;
BEGIN
SELECT bank_account_id
INTO L_bank_account_id
FROM apps.ce_bank_accounts cba
WHERE bank_account_name IN
(SELECT tag
FROM fnd_lookup_values_vl
WHERE enabled_flag = 'Y'
AND lookup_code = '140'
AND lookup_type = 'XX_EPAY_ACC_DET');
EXCEPTION
WHEN OTHERS
THEN
l_error_flag:='E';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Error Finding Bank Id - Please check and rerun' || SQLERRM
);
L_bank_account_id := NULL;
RAISE;
END;
BEGIN
SELECT code_combination_id
INTO ip_code_combination_id
FROM gl_code_combinations
WHERE segment1=1013 and segment2=1630 and segment3=11620 and segment4=0000 and segment5=000 and segment6=0000;
EXCEPTION
when no_data_found then
ip_code_combination_id:=10075;
WHEN OTHERS
THEN
l_error_flag:='E';
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Error Finding code_combination_id ' || SQLERRM);
END;
fnd_file.put_line (
fnd_file.LOG,
'CCID'
|| ip_code_combination_id
|| 'ProjectCode'
|| l_project_code
);
fnd_file.put_line (
fnd_file.LOG,
' ip_code_combination_id:' || ip_code_combination_id
);
BEGIN
SELECT customer_id, customer_number
INTO l_customer_id, l_customer_number
FROM ar_customers
WHERE customer_name IN 'Cash Customer';
EXCEPTION
when no_data_found then
l_customer_id:=769734;
l_customer_number:=20074;
WHEN OTHERS
THEN
l_error_flag:='E';
fnd_file.put_line (fnd_file.LOG,
'Error Finding Customer ' || SQLERRM);
END;
DBMS_OUTPUT.put_line ('c_dt.siteid:' || c_dt.siteid);
--FOR CODE COMBINATION ID FOR CREATE INVOICES
l_batch_source_rec.batch_source_id := l_batch_source_id;
l_trx_header_tbl (1).trx_header_id := trx_header_id_v;
l_trx_header_tbl (1).bill_to_customer_id := l_customer_id;
l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;
l_trx_header_tbl (1).trx_date := SYSDATE;
l_trx_header_tbl (1).trx_currency := 'AED';
l_trx_header_tbl (1).term_id := l_default_term_id;
l_trx_header_tbl (1).reference_number := l_bankrefnum;
l_trx_header_tbl (1).finance_charges := NULL;
l_trx_header_tbl (1).status_trx := 'OP';
l_trx_header_tbl (1).printing_option := 'PRI';
l_trx_header_tbl (1).attribute_category :=
'Non_Property_Transactions';
l_trx_header_tbl (1).attribute11 := l_project_code;
l_trx_header_tbl (1).attribute14 := l_bankrefnum;
l_trx_header_tbl (1).attribute9 := l_bank_account_id;
l_trx_lines_tbl (1).trx_header_id := trx_header_id_v;
l_trx_lines_tbl (1).trx_line_id := trx_line_id_v;
l_trx_lines_tbl (1).line_number := 1;
l_trx_lines_tbl (1).description := l_desc;
l_trx_lines_tbl (1).quantity_invoiced := 1;
l_trx_lines_tbl (1).unit_selling_price := c_dt.paidamount;
l_trx_lines_tbl (1).line_type := 'LINE';
l_trx_lines_tbl (1).sales_order := l_wonum;
l_trx_dist_tbl (1).trx_dist_id := trx_dist_id_v;
l_trx_dist_tbl (1).trx_line_id := trx_line_id_v;
l_trx_dist_tbl (1).account_class := 'REV';
l_trx_dist_tbl (1).PERCENT := 100;
l_trx_dist_tbl (1).code_combination_id :=
TO_NUMBER (ip_code_combination_id);
ar_invoice_api_pub.CREATE_invoice (
p_api_version => 1.0,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
COMMIT;
fnd_file.put_line (
fnd_file.output,
'Request ID: '
|| fnd_global.conc_request_id
|| ' Date: '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (
fnd_file.output,
' Importing To Oracle EBS Interface '
);
fnd_file.put_line (fnd_file.output,
' ---------------------------------------------');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.LOG, 'Return Status' || l_return_status);
fnd_file.put_line (fnd_file.LOG, 'Err Message' || l_msg_data);
fnd_file.put_line (
fnd_file.LOG,'ip_code_combination_id ' || ip_code_combination_id);
IF l_return_status = fnd_api.g_ret_sts_error
OR l_return_status = fnd_api.g_ret_sts_unexp_error
THEN
fnd_file.put_line (fnd_file.LOG,'unexpected errors found! at API ');
ELSE
FOR cvalidtxnrec IN cvalidtxn
LOOP
IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
THEN
fnd_file.put_line (fnd_file.LOG,'Invoice(s) suceessfully created!');
fnd_file.put_line (fnd_file.LOG, 'Batch ID: '
|| ar_invoice_api_pub.g_api_outputs.batch_id
);
l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
FOR cbatchrec IN cbatch
LOOP
fnd_file.put_line (fnd_file.LOG, 'Cust Trx Id '
|| cbatchrec.customer_trx_id
);
fnd_file.put_line (fnd_file.output, 'Cust Trx Id '
|| cbatchrec.customer_trx_id
);
v_trx_Id := cbatchrec.customer_trx_id;
END LOOP;
ELSE
fnd_file.put_line (fnd_file.LOG,'Errors found! at API');
END IF;
END LOOP;
END IF;
END LOOP;
END XBOL_CREATE_AR_TRANSACTION;
-- END FOR INVOICE CREATION PROCEDURE
end XBOL_SORDER_PKG;
/
No comments:
Post a Comment
Text Message