declare
l_errbuf VARCHAR2(100);
l_retcode NUMBER;
begin
XXEXPD_CREATE_SORDER_PKG.XXEXPD_CREATE_AR_TRANSACTION(l_errbuf,l_retcode,NULL,5);
end;
The above procedure creates 5 AR Invoices
----------------------------------------------------------------------------
create or replace package XXEXPD_CREATE_SORDER_PKG IS
PROCEDURE XXEXPD_CREATE_AR_TRANSACTION(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_trx_number IN VARCHAR2 DEFAULT NULL,
p_total_invoices IN NUMBER DEFAULT 1);
END;
create or replace package body XXEXPD_CREATE_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 XXEXPD_CREATE_AR_TRANSACTION(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_trx_number IN VARCHAR2,
p_total_invoices IN NUMBER)
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);
l_TRX_NUMBER VARCHAR2(100);
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
for i in 1..p_total_invoices loop
mo_global.set_policy_context('S',111);
SELECT RA_CUSTOMER_TRX_S.nextval
INTO trx_header_id_v
FROM DUAL;
SELECT RA_CUSTOMER_TRX_LINES_S.NEXTVAL
INTO trx_line_id_v
FROM DUAL;
SELECT RA_CUST_TRX_LINE_GL_DIST_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 ='111'
AND b.NAME = 'CH_DIR_MANUAL'
AND c.name = 'CH_DIR_INVOICE_USD'
AND c.END_DATE IS NULL;
BEGIN
SELECT customer_id, customer_number
INTO l_customer_id, l_customer_number
FROM ar_customers
WHERE customer_name IN 'EVERGREEN LAUREL HOTEL SHANGHAI';
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 := 'CNY';
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 := 'DIRECT AGENCY';
l_trx_header_tbl (1).attribute1 := 'CNY';
l_trx_header_tbl (1).attribute5 := 'INDEPENDENTS';
--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).INVENTORY_ITEM_ID := '160011';
l_trx_lines_tbl (1).line_number := 1;
l_trx_lines_tbl (1).description := 'ACCELERATOR COMPENSATION';
l_trx_lines_tbl (1).quantity_invoiced := 1;
--l_trx_lines_tbl
(1).UOM_UNIT_OF_MEASURE_NAME := 'EA';
l_trx_lines_tbl (1).unit_selling_price := '1260';
l_trx_lines_tbl (1).line_type := 'LINE';
l_trx_lines_tbl (1).attribute_category := 'DIRECT AGENCY';
l_trx_lines_tbl (1).attribute1 := '1175';
l_trx_lines_tbl (1).attribute2 := '16400036260';
l_trx_lines_tbl (1).attribute3 := 'CNY';
l_trx_lines_tbl (1).attribute4 := '1250';
l_trx_lines_tbl (1).attribute7 := '31-MAR-2020';
l_trx_lines_tbl (1).attribute8 := '31-MAR-2020';
l_trx_lines_tbl (1).attribute10 := '31/03/2020';
l_trx_lines_tbl (1).attribute12 := '659213445';
l_trx_lines_tbl (1).attribute13 := '659213445';
--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 := 1285664;
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, '');
dbms_output.put_line( 'Return Status' || l_return_status);
dbms_output.put_line( '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
dbms_output.put_line('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
dbms_output.put_line('Invoice(s) suceessfully
created!');
dbms_output.put_line( '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
dbms_output.put_line( '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;
select TRX_NUMBER INTO l_TRX_NUMBER from ra_customer_trx_all where
customer_trx_id = v_trx_Id;
dbms_output.put_line( 'Cust
Trx Number '
|| l_TRX_NUMBER
);
END LOOP;
ELSE
dbms_output.put_line('Errors found! at API');
END IF;
END LOOP;
END IF;
END Loop;
END XXEXPD_CREATE_AR_TRANSACTION;
end XXEXPD_CREATE_SORDER_PKG;
No comments:
Post a Comment