oracle ebs webadi,R12 WEBADI 开发实例 (EBS 版本：12.1.3)

最新推荐文章于 2024-06-09 18:40:04 发布

Keraun

最新推荐文章于 2024-06-09 18:40:04 发布

阅读量945

点赞数

文章标签： oracle ebs webadi

--Created by Tony Liu.

这两天做了一个R12 WEBADI的开发任务，任务描述如下：

1. 根据用户的data file，导入数据至XLA, 生成Journals, 然后再调用标准请求，生成GL Journals

2. 建立staging table, 用来存放WEBADI导入的数据，表名为c_xla_je_load_staging

3. 调用API 来生成XLA Journals

xla_journal_entries_pub_pkg.create_journal_entry_header 生成Journal header

xla_journal_entries_pub_pkg.create_journal_entry_line 生成Journal lines

4. 调用标准请求生成GL Journals

5. 建立Package ：c_xla_je_load_pkg, 完成数据校验/数据导入到XLA/调用标准请求

6. 注册请求CUST XLA Journal Entry Load，调用上述package.

7. 在WEBADI 中调用上述请求.

如果上述工作在11i里做的话，估计得把我做吐血，因为11i的WEBADI还不是很完备，很多功能ORACLE没有提供开放的接口，只能用insert table来做。但是现在在R12中，做起来就简单多了。

R12 WEBADI比11i的强大了许多，反映在如下几方面：

1. 多了一个新的职责: Desktop integration manager, 在此职责下可完成所有的WEBADI的开发工作。

2. 现在可以在Excel 模板中定义Lov值验证

3. 增加了Interface 的定义，可以灵活定义Layout的数据源。

4. Layout中的定义多了一些选项，使得Layout的定义更加完善。

5. 增加了uploader的定义，可以灵活定义导入参数

6. 增加了importer的定义，可以灵活定义数据导入后的工作。

7. FNDLOAD 更加完善，可通过bneintegrator.lct 导出大多数的WEBADI定义。

所以这个Task 还真没花多少时间，就完成了。

---------------------------------以下是Table定义-------------------------------------create table C_APPS.C_XLA_JE_LOAD_STAGING

(

HEADER_ID NUMBER not null,

BALANCE_TYPE VARCHAR2(1) not null,

LEDGER_NAME VARCHAR2(100) not null,

LEDGER_ID NUMBER,

LEGAL_ENTITY_NAME VARCHAR2(100),

LEGAL_ENTITY_ID NUMBER,

REFERENCE_DATE DATE,

GL_DATE DATE not null,

CATEGORY VARCHAR2(20) not null,

H_DESCRIPTION VARCHAR2(200) not null,

LINE_NUMBER NUMBER not null,

ACCOUNTING_FF VARCHAR2(100) not null,

CODE_COMBINATION_ID NUMBER,

ACCOUNTING_CLASS VARCHAR2(20) not null,

ENTERED_CURRENCY VARCHAR2(10) not null,

ENTERED_DR NUMBER,

ENTERED_CR NUMBER,

THIRD_PARTY_TYPE VARCHAR2(1),

CUSTOMER_NUMBER VARCHAR2(30),

PARTY_ID NUMBER,

CUSTOMER_SITE_NUMBER VARCHAR2(30),

PARTY_SITE_ID NUMBER,

L_DESCRIPTION VARCHAR2(200),

RECONCILIATION_REFERENCE VARCHAR2(200),

PROCESS_FLAG VARCHAR2(1),

MESSAGE VARCHAR2(1000),

GLB_QUANTITY VARCHAR2(30),

OID_AC1 VARCHAR2(30),

OID_AC2 VARCHAR2(30),

OID_AC3 VARCHAR2(30),

OID_AC4 VARCHAR2(30),

OID_AC5 VARCHAR2(30),

AE_HEADER_ID NUMBER

)

---------------------------以下是Package定义------------------------------------

Process: 校验数据->调用API导入XLA->调用标准请求导入GL0->清空staging table

CREATE OR REPLACE PACKAGE BODY c_xla_je_load_pkg IS

-- Author : Tony Liu

c_gl_application_id CONSTANT INTEGER := 101;

c_ar_application_id CONSTANT INTEGER := 222;

PROCEDURE validation(p_ledger_name IN VARCHAR2,

p_legal_entity_name IN VARCHAR2,

p_reference_date IN DATE,

p_gl_date IN DATE,

p_category IN VARCHAR2,

p_accounting_ff IN VARCHAR2,

p_cust_num IN VARCHAR2,

p_cust_site_num IN VARCHAR2,

p_currency IN VARCHAR2,

p_ledger_id OUT NUMBER,

p_legal_entity_id OUT NUMBER,

p_ccid OUT NUMBER,

p_party_id OUT NUMBER,

p_party_site_id OUT NUMBER,

p_error_flag OUT VARCHAR2,

p_error_msg OUT VARCHAR2) IS

vl_n_seg NUMBER;

vl_result BOOLEAN;

vl_currency gl_currencies.currency_code%TYPE;

vl_gd_closing_status gl_period_statuses.closing_status%TYPE;

vl_rd_closing_status gl_period_statuses.closing_status%TYPE;

vl_category gl_je_categories.je_category_name%TYPE;

vl_seg fnd_flex_ext.segmentarray;

vl_structure_number NUMBER;

BEGIN

--Validate Ledger

BEGIN

SELECT ledger_id

INTO p_ledger_id

FROM gl_ledgers

WHERE NAME = p_ledger_name;

EXCEPTION

WHEN no_data_found THEN

p_ledger_id := NULL;

p_error_msg := 'Invalid Ledger Name. ';

p_error_flag := 'Y';

END;

--Validate Entity

BEGIN

IF (p_legal_entity_name IS NOT NULL) AND (p_ledger_id IS NOT NULL) THEN

SELECT legal_entity_id

INTO p_legal_entity_id

FROM gl_ledger_le_v

WHERE ledger_id = p_ledger_id

AND legal_entity_name = p_legal_entity_name;

END IF;

EXCEPTION

WHEN no_data_found THEN

p_legal_entity_id := NULL;

p_error_msg := p_error_msg || 'Invalid Legal Entity Name. ';

p_error_flag := 'Y';

END;

--Validate GL Date

BEGIN

IF p_ledger_id IS NOT NULL THEN

SELECT closing_status

INTO vl_gd_closing_status

FROM gl_period_statuses

WHERE p_gl_date BETWEEN start_date AND end_date

AND ledger_id = p_ledger_id

AND application_id = c_gl_application_id;

IF vl_gd_closing_status <> 'O' AND vl_gd_closing_status <> 'F' THEN

p_error_msg := p_error_msg ||

'GL Date is not in an open period. ';

p_error_flag := 'Y';

END IF;

EXCEPTION

WHEN no_data_found THEN

p_error_msg := 'Invalid GL Date.';

p_error_flag := 'Y';

END;

--Validate Reference Date

IF p_reference_date IS NOT NULL THEN

BEGIN

IF p_ledger_id IS NOT NULL THEN

SELECT closing_status

INTO vl_rd_closing_status

FROM gl_period_statuses

WHERE p_reference_date BETWEEN start_date AND end_date

AND ledger_id = p_ledger_id

AND application_id = c_gl_application_id;

IF vl_rd_closing_status <> 'O' AND vl_rd_closing_status <> 'F' THEN

p_error_msg := p_error_msg ||

'Reference Date is not in an open period. ';

p_error_flag := 'Y';

END IF;

EXCEPTION

WHEN no_data_found THEN

p_error_msg := 'Invalid GL Date.';

p_error_flag := 'Y';

END;

END IF;

--Validate Category Name

BEGIN

SELECT je_category_name

INTO vl_category

FROM gl_je_categories

WHERE je_category_name = p_category;

EXCEPTION

WHEN no_data_found THEN

p_error_msg := p_error_msg || 'Invalid JE Category. ';

p_error_flag := 'Y';

END;

--Validate Code Combinations

BEGIN

SELECT chart_of_accounts_id

INTO vl_structure_number

FROM gl_ledgers

WHERE ledger_id = p_ledger_id;

vl_n_seg := fnd_flex_ext.breakup_segments(p_accounting_ff,

'-',

vl_seg);

vl_result := fnd_flex_ext.get_combination_id(application_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => vl_structure_number,

validation_date => SYSDATE,

n_segments => vl_n_seg,

segments => vl_seg,

combination_id => p_ccid);

IF vl_result = FALSE THEN

p_error_msg := p_error_msg || 'Invalid Accounting Flexfield. ';

p_error_flag := 'Y';

END IF;

EXCEPTION

WHEN no_data_found THEN

vl_structure_number := 0;

p_error_msg := p_error_msg || 'Invalid Ledger. ';

p_error_flag := 'Y';

END;

--Validate Customer

IF p_cust_num IS NOT NULL THEN

BEGIN

SELECT hca.cust_account_id party_id,

MIN(hcsu.site_use_id) site_use_id

INTO p_party_id, p_party_site_id

FROM hz_cust_site_uses_all hcsu,

hz_cust_accounts hca,

hz_cust_acct_sites_all hcas,

hz_parties hp,

hz_party_sites hps

WHERE hp.party_id = hca.party_id

AND hps.party_site_id = hcas.party_site_id

AND hca.cust_account_id = hcas.cust_account_id

AND hcas.status = 'A'

AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

AND hca.account_number = p_cust_num

AND hps.party_site_number = p_cust_site_num

GROUP BY hca.cust_account_id;

EXCEPTION

WHEN no_data_found THEN

p_party_site_id := NULL;

p_error_msg := p_error_msg ||

'Invalid Customer or Customer Site Number. ';

p_error_flag := 'Y';

END;

END IF;

--Validate Currency

BEGIN

SELECT currency_code

INTO vl_currency

FROM gl_currencies

WHERE currency_code = p_currency

AND enabled_flag = 'Y'

AND nvl(end_date_active, SYSDATE) >= SYSDATE;

EXCEPTION

WHEN no_data_found THEN

p_error_msg := p_error_msg || 'Invalid Currency Code. ';

p_error_flag := 'Y';

END;

END validation;

PROCEDURE main(po_errbuf OUT VARCHAR2, po_retcode OUT VARCHAR2) IS

vc_api_version CONSTANT NUMBER := 1.0;

vc_balance_type_code CONSTANT xla_ae_headers.balance_type_code%TYPE := 'A';

vc_init_msg_list CONSTANT VARCHAR2(1) := fnd_api.g_true;

vc_gl_transfer_mode CONSTANT VARCHAR(1) := 'S';

vc_party_type_code CONSTANT VARCHAR(1) := 'C';

vc_sla_je_complete_option CONSTANT VARCHAR2(1) := 'F';

vc_retcode_normal CONSTANT NUMBER := 0;

vc_retcode_warning CONSTANT NUMBER := 1;

vc_retcode_error CONSTANT NUMBER := 2;

vc_support_ref1 CONSTANT VARCHAR2(15) := 'C_GBL_QUANTITY';

vc_support_ref2 CONSTANT VARCHAR2(15) := 'C_OID';

vc_period_set CONSTANT VARCHAR2(11) := 'MM CALENDAR';

vc_primary_ledger CONSTANT VARCHAR2(7) := 'PRIMARY';

CURSOR c_data IS

SELECT * FROM c_apps.c_xla_je_load_staging ORDER BY header_id;

CURSOR c_error_data IS

SELECT *

FROM c_apps.c_xla_je_load_staging

WHERE process_flag = 'E'

ORDER BY header_id;

CURSOR c_valid_data_h IS

SELECT DISTINCT header_id

FROM c_apps.c_xla_je_load_staging

ORDER BY header_id;

CURSOR c_valid_data_l(p_header_id NUMBER) IS

SELECT *

FROM c_apps.c_xla_je_load_staging

WHERE header_id = p_header_id;

CURSOR c_period_name IS

SELECT DISTINCT gp.period_name

FROM gl_periods gp, c_xla_je_load_staging cxjls

WHERE cxjls.gl_date BETWEEN gp.start_date AND gp.end_date

AND gp.period_set_name = vc_period_set;

--For "Transfer Journal Entries to GL"

CURSOR c_ledger IS

SELECT DISTINCT cxjls.ledger_id, cxjls.ledger_name

FROM c_apps.c_xla_je_load_staging cxjls, gl.gl_ledgers gls

WHERE cxjls.ledger_id = gls.ledger_id

AND gls.ledger_category_code = vc_primary_ledger;

vl_error_flag VARCHAR2(1) := NULL;

vl_error_msg VARCHAR2(1000) := NULL;

vl_ledger_id NUMBER := 0;

vl_legal_entity_id NUMBER := 0;

vl_ccid NUMBER := 0;

vl_party_id NUMBER := 0;

vl_party_site_id NUMBER := 0;

vl_count_error NUMBER := 0;

vl_tol_dr NUMBER := 0;

vl_tol_cr NUMBER := 0;

pi_ledger_id gl_ledgers.ledger_id%TYPE;

pi_legal_entity_id NUMBER;

pi_reference_date DATE;

pi_gl_date DATE;

pi_category gl_je_categories.je_category_name%TYPE;

pi_description xla_ae_headers.description%TYPE;

vl_return_status VARCHAR2(5);

vl_msg_count NUMBER;

vl_msg_data VARCHAR2(4000);

vl_l_return_status VARCHAR2(5);

vl_l_msg_count NUMBER;

vl_l_msg_data VARCHAR2(4000);

vl_ae_header_id xla_ae_headers.ae_header_id%TYPE;

vl_event_id xla_ae_headers.event_id%TYPE;

vl_ae_line_num xla_ae_lines.ae_line_num%TYPE;

vl_c_return_status VARCHAR2(5);

vl_c_msg_count NUMBER;

vl_c_msg_data VARCHAR2(4000);

vl_complete_retcode VARCHAR2(5);

po_ret_code NUMBER;

vl_i NUMBER;

vl_err_msg VARCHAR2(2000);

vl_request_id NUMBER;

vl_gl_date VARCHAR2(20);

BEGIN

--Call Validation process to validate each lines

fnd_file.put_line(fnd_file.log, 'Start validation...');

FOR c1 IN c_data LOOP

fnd_file.put_line(fnd_file.log,

'Validating...' || c1.header_id || '-' ||

c1.line_number);

validation(c1.ledger_name,

c1.legal_entity_name,

c1.gl_date,

c1.reference_date,

c1.category,

c1.accounting_ff,

c1.customer_number,

c1.customer_site_number,

c1.entered_currency,

vl_ledger_id,

vl_legal_entity_id,

vl_ccid,

vl_party_id,

vl_party_site_id,

vl_error_flag,

vl_error_msg);

UPDATE c_apps.c_xla_je_load_staging cxjs

SET ledger_id = vl_ledger_id,

legal_entity_id = vl_legal_entity_id,

code_combination_id = vl_ccid,

party_id = vl_party_id,

party_site_id = vl_party_site_id,

process_flag = decode(vl_error_flag, 'Y', 'E', 'N'),

message = vl_error_msg

WHERE cxjs.header_id = c1.header_id

AND cxjs.line_number = c1.line_number;

END LOOP;

COMMIT;

--Validate balance

fnd_file.put_line(fnd_file.log, 'Validating balance ');

FOR c_tol IN c_valid_data_h LOOP

SELECT SUM(nvl(entered_dr, 0)), SUM(nvl(entered_cr, 0))

INTO vl_tol_dr, vl_tol_cr

FROM c_apps.c_xla_je_load_staging

WHERE header_id = c_tol.header_id;

IF vl_tol_dr <> vl_tol_cr THEN

UPDATE c_apps.c_xla_je_load_staging

SET process_flag = 'E',

message = message || 'Unbalanced Journal.'

WHERE header_id = c_tol.header_id;

END IF;

END LOOP;

COMMIT;

fnd_file.put_line(fnd_file.log, 'Validation completed. ');

SELECT COUNT(1)

INTO vl_count_error

FROM c_apps.c_xla_je_load_staging

WHERE process_flag = 'E';

IF vl_count_error = 0 THEN

fnd_file.put_line(fnd_file.log, 'Starting API ');

--Call API load Header

FOR c3 IN c_valid_data_h LOOP

SELECT ledger_id,

legal_entity_id,

reference_date,

gl_date,

category,

h_description

INTO pi_ledger_id,

pi_legal_entity_id,

pi_reference_date,

pi_gl_date,

pi_category,

pi_description

FROM c_apps.c_xla_je_load_staging

WHERE header_id = c3.header_id

AND rownum = 1;

xla_journal_entries_pub_pkg.create_journal_entry_header(p_api_version => vc_api_version,

p_init_msg_list => vc_init_msg_list,

p_application_id => c_ar_application_id,

p_ledger_id => pi_ledger_id,

p_legal_entity_id => pi_legal_entity_id,

p_gl_date => pi_gl_date,

p_description => pi_description,

p_je_category_name => pi_category,

p_balance_type_code => vc_balance_type_code,

p_budget_version_id => NULL,

p_reference_date => pi_reference_date,

p_budgetary_control_flag => NULL,

p_attribute_category => NULL,

p_attribute1 => NULL,

p_attribute2 => NULL,

p_attribute3 => NULL,

p_attribute4 => NULL,

p_attribute5 => NULL,

p_attribute6 => NULL,

p_attribute7 => NULL,

p_attribute8 => NULL,

p_attribute9 => NULL,

p_attribute10 => NULL,

p_attribute11 => NULL,

p_attribute12 => NULL,

p_attribute13 => NULL,

p_attribute14 => NULL,

p_attribute15 => NULL,

x_return_status => vl_return_status,

x_msg_count => vl_msg_count,

x_msg_data => vl_msg_data,

x_ae_header_id => vl_ae_header_id,

x_event_id => vl_event_id);

IF vl_return_status = fnd_api.g_ret_sts_success THEN

--Call API load lines

FOR c4 IN c_valid_data_l(c3.header_id) LOOP

xla_journal_entries_pub_pkg.create_journal_entry_line(p_api_version => vc_api_version,

p_init_msg_list => vc_init_msg_list,

p_application_id => c_ar_application_id,

p_ae_header_id => vl_ae_header_id,

p_displayed_line_number => c4.line_number,

p_code_combination_id => c4.code_combination_id,

p_gl_transfer_mode => vc_gl_transfer_mode,

p_accounting_class_code => c4.accounting_class,

p_currency_code => c4.entered_currency,

p_entered_dr => c4.entered_dr,

p_entered_cr => c4.entered_cr,

p_accounted_dr => NULL,

p_accounted_cr => NULL,

p_conversion_type => 'Corporate',

p_conversion_date => pi_gl_date,

p_conversion_rate => NULL,

p_party_type_code => vc_party_type_code,

p_party_id => c4.party_id,

p_party_site_id => c4.party_site_id,

p_description => c4.l_description,

p_statistical_amount => NULL,

p_jgzz_recon_ref => NULL,

p_attribute_category => NULL,

p_encumbrance_type_id => NULL,

p_attribute1 => NULL,

p_attribute2 => NULL,

p_attribute3 => NULL,

p_attribute4 => NULL,

p_attribute5 => NULL,

p_attribute6 => NULL,

p_attribute7 => NULL,

p_attribute8 => NULL,

p_attribute9 => NULL,

p_attribute10 => NULL,

p_attribute11 => NULL,

p_attribute12 => NULL,

p_attribute13 => NULL,

p_attribute14 => NULL,

p_attribute15 => NULL,

x_return_status => vl_l_return_status,

x_msg_count => vl_l_msg_count,

x_msg_data => vl_l_msg_data,

x_ae_line_num => vl_ae_line_num);

IF vl_l_return_status <> fnd_api.g_ret_sts_success THEN

ROLLBACK;

fnd_file.put_line(fnd_file.log,

'Record Header ' || c3.header_id ||

' failed!');

fnd_file.put_line(fnd_file.log,

'Error occurred in create_journal_entry_line API');

po_retcode := vc_retcode_error;

IF (nvl(vl_l_msg_count, 0) = 0) THEN

fnd_file.put_line(fnd_file.log, 'No message return');

ELSE

FOR vl_i IN 1 .. vl_l_msg_count LOOP

vl_err_msg := fnd_msg_pub.get(vl_i, 'F');

fnd_file.put_line(fnd_file.log,

substr(vl_err_msg, 1, 2000));

END LOOP;

END IF;

EXIT;

END IF;

--Update Supporting Reference

IF (c4.glb_quantity IS NOT NULL) OR (c4.oid_ac1 IS NOT NULL) OR

(c4.oid_ac2 IS NOT NULL) OR (c4.oid_ac3 IS NOT NULL) OR

(c4.oid_ac4 IS NOT NULL) OR (c4.oid_ac5 IS NOT NULL) THEN

UPDATE xla_ae_lines

SET analytical_balance_flag = 'Y'

WHERE ae_line_num = vl_ae_line_num

AND ae_header_id = vl_ae_header_id;

IF c4.glb_quantity IS NOT NULL THEN

INSERT INTO xla_ae_line_acs

VALUES

(vl_ae_header_id,

vl_ae_line_num,

vc_support_ref1,

'C',

'DEFAULT',

c4.glb_quantity,

NULL,

NULL);

IF (c4.oid_ac1 IS NOT NULL) OR (c4.oid_ac2 IS NOT NULL) OR

(c4.oid_ac3 IS NOT NULL) OR (c4.oid_ac4 IS NOT NULL) OR

(c4.oid_ac5 IS NOT NULL) THEN

INSERT INTO xla_ae_line_acs

VALUES

(vl_ae_header_id,

vl_ae_line_num,

vc_support_ref2,

'C',

'DEFAULT',

c4.oid_ac1,

c4.oid_ac2,

c4.oid_ac3,

c4.oid_ac4,

c4.oid_ac5);

END IF;

ELSE

INSERT INTO xla_ae_line_acs

VALUES

(vl_ae_header_id,

vl_ae_line_num,

vc_support_ref2,

'C',

'DEFAULT',

c4.oid_ac1,

c4.oid_ac2,

c4.oid_ac3,

c4.oid_ac4,

c4.oid_ac5);

END IF;

END LOOP;

--Call API complete the journals

xla_journal_entries_pub_pkg.complete_journal_entry(p_api_version => vc_api_version,

p_init_msg_list => vc_init_msg_list,

p_application_id => c_ar_application_id,

p_ae_header_id => vl_ae_header_id,

p_completion_option => vc_sla_je_complete_option,

x_return_status => vl_c_return_status,

x_msg_count => vl_c_msg_count,

x_msg_data => vl_c_msg_data,

x_completion_retcode => vl_complete_retcode);

IF vl_c_return_status = fnd_api.g_ret_sts_success THEN

COMMIT;

UPDATE c_apps.c_xla_je_load_staging

SET process_flag = 'P', ae_header_id = vl_ae_header_id --Changed on Jul 22

WHERE header_id = c3.header_id;

COMMIT;

fnd_file.put_line(fnd_file.log,

'Record Header ' || c3.header_id ||

' was processed successfully.');

ELSE

ROLLBACK;

fnd_file.put_line(fnd_file.log,

'Record Header ' || c3.header_id ||

' failed!');

fnd_file.put_line(fnd_file.log,

'Error occurred in complete_journal_entry API');

po_retcode := vc_retcode_error;

IF (nvl(vl_c_msg_count, 0) = 0) THEN

fnd_file.put_line(fnd_file.log, 'No message return');

ELSE

FOR vl_i IN 1 .. vl_c_msg_count LOOP

vl_err_msg := fnd_msg_pub.get(vl_i, 'F');

fnd_file.put_line(fnd_file.log,

substr(vl_err_msg, 1, 2000));

END LOOP;

END IF;

ELSE

ROLLBACK;

fnd_file.put_line(fnd_file.log,

'Record Header ' || c3.header_id || ' failed!');

fnd_file.put_line(fnd_file.log,

'Error occurred in create_journal_entry_header API');

po_retcode := vc_retcode_error;

IF (nvl(vl_msg_count, 0) = 0) THEN

fnd_file.put_line(fnd_file.log, 'No message return');

ELSE

FOR vl_i IN 1 .. vl_msg_count LOOP

vl_err_msg := fnd_msg_pub.get(vl_i, 'F');

fnd_file.put_line(fnd_file.log, substr(vl_err_msg, 1, 2000));

END LOOP;

END IF;

END LOOP;

IF po_retcode = vc_retcode_error THEN

fnd_file.put_line(fnd_file.log,

'The file was processed with failed records, please check! ');

ELSE

fnd_file.put_line(fnd_file.log,

'The file was processed successfully. ');

--Run report; changed on Jul 22

FOR rec_period IN c_period_name LOOP

sla_report(rec_period.period_name);

END LOOP;

--Submit "Transfer Journal Entries to GL"

apps.fnd_global.apps_initialize(user_id => apps.fnd_global.user_id,

resp_id => apps.fnd_global.resp_id,

resp_appl_id => apps.fnd_global.resp_appl_id);

FOR rec_ledger IN c_ledger LOOP

SELECT to_char(MAX(gl_date), 'yyyy/mm/dd hh24:mi:ss')

INTO vl_gl_date

FROM c_apps.c_xla_je_load_staging

WHERE ledger_id = rec_ledger.ledger_id;

vl_request_id := fnd_request.submit_request(application => 'XLA',

program => 'XLAGLTRN',

description => NULL,

start_time => NULL, -- To start immediately

sub_request => FALSE,

argument1 => 222,

argument2 => 222,

argument3 => 'Y',

argument4 => rec_ledger.ledger_id,

argument5 => 'MANUAL',

argument6 => vl_gl_date,

argument7 => 'N',

argument8 => '',

argument9 => '',

argument10 => 'Y',

argument11 => '',

argument12 => '',

argument13 => 'Y',

argument14 => 'Y',

argument15 => 'N',

argument16 => '',

argument17 => '',

argument18 => '',

argument19 => '',

argument20 => 'Receivables',

argument21 => 'Receivables',

argument22 => rec_ledger.ledger_name,

argument23 => 'Manual',

argument24 => 'No',

argument25 => '',

argument26 => '',

argument27 => '',

argument28 => 'Yes',

argument29 => 'No',

argument30 => '',

argument31 => '',

argument32 => '',

argument33 => '',

argument34 => '',

argument35 => '',

argument36 => '',

argument37 => '',

argument38 => 'N',

argument39 => chr(0) -- end with chr(0)as end of parameters

);

fnd_file.put_line(fnd_file.log,

'''Transfer Journal Entries to GL''' ||

' Request:' || vl_request_id ||

' submitted for Ledger ' ||

rec_ledger.ledger_name);

END LOOP;

END IF;

ELSE

po_retcode := vc_retcode_warning;

fnd_file.put_line(fnd_file.log, 'Invalid data, please correct!');

fnd_file.put_line(fnd_file.log,

'Header Identifier, ' || 'Ledger Name, ' ||

'Legal Entity Name, ' || 'GL Date, ' ||

'Category, ' || 'Line number, ' ||

'Accounting Flexfield, ' || 'Accounnting Class, ' ||

'Customer Number, ' || 'Customer Site Number, ' ||

'Entered Currency, ' || 'Error Messsage');

FOR c2 IN c_error_data LOOP

fnd_file.put_line(fnd_file.log,

c2.header_id || ',' || c2.ledger_name || ',' ||

c2.legal_entity_name || ',' || c2.gl_date || ',' ||

c2.category || ',' || c2.line_number || ',' ||

c2.accounting_ff || ',' || c2.accounting_class || ',' ||

c2.customer_number || ',' ||

c2.customer_site_number || ',' ||

c2.entered_currency || ',' || c2.message);

END LOOP;

END IF;

--delete all data from staging table

BEGIN

fnd_file.put_line(fnd_file.log,

'Delete all data from staging table!');

DELETE FROM c_apps.c_xla_je_load_staging;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,

'Failed to delete data from staging table!');

po_retcode := vc_retcode_warning;

END;

END main;

PROCEDURE sla_report(pi_period_name IN VARCHAR2) IS

vl_quantity VARCHAR2(30);

vl_discount_type VARCHAR2(30);

vl_prod_code VARCHAR2(30);

vl_deal_num VARCHAR2(30);

vl_units VARCHAR2(30);

vl_comments VARCHAR2(30);

CURSOR c_staging IS

SELECT DISTINCT header_id, ae_header_id

FROM c_xla_je_load_staging

ORDER BY header_id;

CURSOR c_xla_je(pi_ae_header_id IN NUMBER) IS

SELECT xah.ae_header_id,

gl.name,

xah.je_category_name,

xah.accounting_date,

xah.reference_date,

xah.description description,

xal.ae_line_num,

gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||

gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||

gcc.segment7 code_combination,

xal.accounting_class_code,

xal.currency_code,

xal.entered_dr,

xal.accounted_dr,

xal.entered_cr,

xal.accounted_cr,

xal.description line_description,

hp.party_name customer_name,

hca.account_number customer_number,

hps.party_site_number site_number

FROM apps.xla_ae_headers xah,

apps.xla_ae_lines xal,

apps.gl_code_combinations gcc,

apps.gl_ledgers gl,

apps.hz_cust_accounts hca,

apps.hz_cust_acct_sites_all hcas,

apps.hz_parties hp,

apps.hz_party_sites hps,

apps.hz_cust_site_uses_all hcsu

WHERE xah.ledger_id = gl.ledger_id

AND xah.ae_header_id = xal.ae_header_id

AND xal.code_combination_id = gcc.code_combination_id

AND xal.party_id = hca.cust_account_id

AND xal.party_site_id = hcsu.site_use_id

AND hp.party_id = hca.party_id

AND hps.party_site_id = hcas.party_site_id

AND hca.cust_account_id = hcas.cust_account_id

AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

AND hcas.status = 'A'

AND xah.event_type_code = 'MANUAL'

AND xah.je_category_name = 'Discounts'

AND xah.ae_header_id = pi_ae_header_id;

CURSOR c_xla_ac(pi_ae_header_id IN NUMBER, pi_ae_line_num IN NUMBER) IS

SELECT xala.analytical_criterion_code,

xala.ac1,

xala.ac2,

xala.ac3,

xala.ac4,

xala.ac5

FROM xla_ae_line_acs xala

WHERE xala.ae_header_id = pi_ae_header_id

AND xala.ae_line_num = pi_ae_line_num;

CURSOR c_xla_je_period IS

SELECT ae_header_id

FROM xla_ae_headers

WHERE event_type_code = 'MANUAL'

AND je_category_name = 'Discounts'

ORDER BY ae_header_id;

BEGIN

fnd_file.put_line(fnd_file.log,

'Run report for period: ' || pi_period_name);

--Generate report for the file loaded

fnd_file.put_line(fnd_file.output, 'SLA Journals Creation Report');

fnd_file.put_line(fnd_file.output,

FOR rec_staging IN c_staging LOOP

FOR rec_xla_je IN c_xla_je(rec_staging.ae_header_id) LOOP

vl_quantity := NULL;

vl_discount_type := NULL;

vl_prod_code := NULL;

vl_deal_num := NULL;

vl_units := NULL;

vl_comments := NULL;

FOR rec_xla_ac IN c_xla_ac(rec_xla_je.ae_header_id,

rec_xla_je.ae_line_num) LOOP

IF rec_xla_ac.analytical_criterion_code = 'C_GBL_QUANTITY' THEN

vl_quantity := rec_xla_ac.ac1;

ELSIF rec_xla_ac.analytical_criterion_code = 'C_OID' THEN

vl_discount_type := rec_xla_ac.ac1;

vl_prod_code := rec_xla_ac.ac2;

vl_deal_num := rec_xla_ac.ac3;

vl_units := rec_xla_ac.ac4;

vl_comments := rec_xla_ac.ac5;

END IF;

END LOOP;

fnd_file.put_line(fnd_file.output,

rec_xla_je.ae_header_id || '|' || rec_xla_je.name || '|' ||

rec_xla_je.je_category_name || '|' ||

rec_xla_je.accounting_date || '|' ||

rec_xla_je.reference_date || '|' ||

rec_xla_je.description || '|' ||

rec_xla_je.ae_line_num || '|' ||

rec_xla_je.code_combination || '|' ||

rec_xla_je.accounting_class_code || '|' ||

rec_xla_je.currency_code || '|' ||

rec_xla_je.entered_dr || '|' ||

rec_xla_je.accounted_dr || '|' ||

rec_xla_je.entered_cr || '|' ||

rec_xla_je.accounted_cr || '|' ||

rec_xla_je.line_description || '|' ||

rec_xla_je.customer_name || '|' ||

rec_xla_je.customer_number || '|' ||

rec_xla_je.site_number || '|' || vl_quantity || '|' ||

vl_discount_type || '|' || vl_prod_code || '|' ||

vl_deal_num || '|' || vl_units || '|' ||

vl_comments);

END LOOP;

fnd_file.put_line(fnd_file.output, ' ');

fnd_file.put_line(fnd_file.output,

'SLA Journals Creation Report for Period: ' ||

pi_period_name);

fnd_file.put_line(fnd_file.output,

FOR rec_xla_je_period IN c_xla_je_period LOOP

FOR rec_xla_je IN c_xla_je(rec_xla_je_period.ae_header_id) LOOP

vl_quantity := NULL;

vl_discount_type := NULL;

vl_prod_code := NULL;

vl_deal_num := NULL;

vl_units := NULL;

vl_comments := NULL;

FOR rec_xla_ac IN c_xla_ac(rec_xla_je.ae_header_id,

rec_xla_je.ae_line_num) LOOP

IF rec_xla_ac.analytical_criterion_code = 'C_GBL_QUANTITY' THEN

vl_quantity := rec_xla_ac.ac1;

ELSIF rec_xla_ac.analytical_criterion_code = 'C_OID' THEN

vl_discount_type := rec_xla_ac.ac1;

vl_prod_code := rec_xla_ac.ac2;

vl_deal_num := rec_xla_ac.ac3;

vl_units := rec_xla_ac.ac4;

vl_comments := rec_xla_ac.ac5;

END IF;

END LOOP;

fnd_file.put_line(fnd_file.output,

rec_xla_je.ae_header_id || '|' || rec_xla_je.name || '|' ||

rec_xla_je.je_category_name || '|' ||

rec_xla_je.accounting_date || '|' ||

rec_xla_je.reference_date || '|' ||

rec_xla_je.description || '|' ||

rec_xla_je.ae_line_num || '|' ||

rec_xla_je.code_combination || '|' ||

rec_xla_je.accounting_class_code || '|' ||

rec_xla_je.currency_code || '|' ||

rec_xla_je.entered_dr || '|' ||

rec_xla_je.accounted_dr || '|' ||

rec_xla_je.entered_cr || '|' ||

rec_xla_je.accounted_cr || '|' ||

rec_xla_je.line_description || '|' ||

rec_xla_je.customer_name || '|' ||

rec_xla_je.customer_number || '|' ||

rec_xla_je.site_number || '|' || vl_quantity || '|' ||

vl_discount_type || '|' || vl_prod_code || '|' ||

vl_deal_num || '|' || vl_units || '|' ||

vl_comments);

END LOOP;

END sla_report;

END c_xla_je_load_pkg;

---------------------------------以下是WEBADI 定义-----------------------------------Navigater: Desktop Integration Manager/Manager Integrators

1. Integrator

Name: CUST XLA Journal Entry Load

Code: C_XLA_JE_LOAD

Application: Custom Application

Enabled: Y

Display in Create Document Page: Y

Security ruls: function: C_XLA_JE_LOAD

2. Interfaces

Name: C_XLA_JE_LOAD_STAGING

Type: Table

Entity name: C_XLA_JE_LOAD_STAGING (实际表名，注意，需在数据库中注册此表，否则找不到)

无任何default value and LOV validation

3. Contents

此步根据需要设置，如果希望用户导出的是空的模板文件，则无需设置，如果希望用户在导出的同时把数据文件加进去，则需设置，我这里设了一下：

Name: C_XLA_JE_LOAD_CONTENT

Type: TEXT

Reporting: N

做了这个设置后，后面还要做Mapping的设置，设置数据文件列与Interface列的对应关系

4. Uploader

Select " Copy from Template "

Uplaoder name: do not change

Title: do not change

Header: do not change

修改bne: import (用来启动后面的importer 的运行)的定义

Description: Automatically Submit CUST XLA Journal Import

Prompt left: Automatically Submit CUST XLA Journal Import

Default value : Y

Enabled: N (必选且用户无法修改，因为如用户不选的话，后面的Request无法调用,需要人为手工调用)

5. Importer

Importer type: Asynchronous concurrent request (导步请求，WEBADI无需等待请求执行结果)

Importer name: C_XLA_JE_LOAD_IMPORTER

Concurrent Program Request Submission: Program: CUST XLA Journal Entry Load

viewspace-2122864 (注意：使用此WEBADI的职责需要能够调用此请求)

Success Message Definition: CUST XLA Journal Import Request ID $import$.requestid

保存生成Integrator

----------------以下是Layout (必须) 及 Mapping(不必须) 的定义-------------------

最后需要定义Layout 及 Mapping (如无Contents定义，无需定义Mapping)

1. Layout

Layout name: C_XLA_JE_LOAD_LAYOUT

Set all columns to LINES

Protect sheet: Y (Excel模板是否保护)

Style. sheet: Default

Apply filter: Y

Data entry rows: 2000(此项是很大的一个改进，可以定义模板的初始行数，不再受10行的困扰了)

定义字段顺序(此项定义本人觉得设计的很垃圾，直接用sequence number就行了，非要搞两个按钮去点，麻烦死了)

定义显示宽度(此项也是很好的一个改进，增加了用户体验)

Mapping 定义略。

FNDLOAD DOWNLOAD:

FNDLOAD apps/apps 0 Y DOWNLOAD bneintegrator.lct C_XLA_JE_LOAD.ldt

BNE_INTEGRATORS INTEGRATOR_ASN=C_APPS

INTEGRATOR_CODE=C_XLA_JE_LOAD_XINTG

Keraun

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle ebs webadi,R12 WEBADI 开发实例 (EBS 版本：12.1.3)

--Created by Tony Liu.这两天做了一个R12 WEBADI的开发任务，任务描述如下：1. 根据用户的data file，导入数据至XLA, 生成Journals, 然后再调用标准请求，生成GL Journals2. 建立staging table, 用来存放WEBADI导入的数据，表名为c_xla_je_load_staging3. 调用API 来生成XLA Jour...
复制链接

扫一扫