--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;
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;
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',
1,
c4.glb_quantity,
NULL,
NULL,
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',
1,
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',
1,
c4.oid_ac1,
c4.oid_ac2,
c4.oid_ac3,
c4.oid_ac4,
c4.oid_ac5);
END IF;
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;
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 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,
'AE_HEADER_ID| LEDGER_NAME| JE_CATEGORY_NAME| ACCOUNTING_DATE| REFERENCE_DATE| DESCRIPTION| AE_LINE_NUM| ACCOUNTING_FF| ACCOUNTING_CLASS_CODE| CURRENCY_CODE| ENTERED_DR| ACCOUNTED_DR| ENTERED_CR| ACCOUNTED_CR| LINE_DESCRIPTION| CUSTOMER_NAME| CUSTOMER_NUMBER| SITE_NUMBER| GBL_QUANTITY| DISCOUNT_TYPE| PRODUCT_CODE| DEAL_NUMBER| UNITS| COMMENTS');
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;
END LOOP;
fnd_file.put_line(fnd_file.output, ' ');
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,
'AE_HEADER_ID| LEDGER_NAME| JE_CATEGORY_NAME| ACCOUNTING_DATE| REFERENCE_DATE| DESCRIPTION| AE_LINE_NUM| ACCOUNTING_FF| ACCOUNTING_CLASS_CODE| CURRENCY_CODE| ENTERED_DR| ACCOUNTED_DR| ENTERED_CR| ACCOUNTED_CR| LINE_DESCRIPTION| CUSTOMER_NAME| CUSTOMER_NUMBER| SITE_NUMBER| GBL_QUANTITY| DISCOUNT_TYPE| PRODUCT_CODE| DEAL_NUMBER| UNITS| COMMENTS');
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 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 (注意： 使用此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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10359218/viewspace-702819/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10359218/viewspace-702819/
945
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
