===========================================================

利用基表写DB trigger 来产生总帐凭证编号(2)

作者: moonsoft(http://moonsoft.itpub.net)
发表于: 2007.02.02 15:06
分类: 财务 , EBS
出处: http://moonsoft.itpub.net/post/15182/259869
---------------------------------------------------------------

还有一个问题很重要,就是获得GL_JE_HEADERS 的period_name
方法一采用 01-07 的会计期 + 方案一
方法二采用 JAN-07 的会计期+ 方案二

方案二
修改公司段的取值: trigger alr_gl_je_headers_num 不同

/* Formatted on 2007/02/02 13:22 (Formatter Plus v4.8.7) */
CREATE OR REPLACE TRIGGER apps.alr_gl_je_headers_num
AFTER UPDATE OF status
ON gl_je_headers
-- REFERENCING OLD AS h_old NEW AS h_new
--FOR EACH ROW
--WHEN (h_new.status = 'P')
DECLARE
v_org_id NUMBER;
v_company VARCHAR2 (10);
v_month VARCHAR2 (10);
v_month_a VARCHAR2 (10);
v_month_b NUMBER;
v_je_header_id NUMBER;
v_year NUMBER;
v_max NUMBER;
v_max_y NUMBER;
v_period_name VARCHAR2 (200);
BEGIN
fnd_profile.get (NAME => 'ORG_ID', val => v_org_id);

FOR i IN 1 .. gl_je_headers_num_pkg.t_je_index
LOOP
SELECT period_name
INTO v_period_name
FROM gl_je_headers
WHERE ROWID = gl_je_headers_num_pkg.t_je (i);

-- get ¹«Ë¾¶ÎÖµ modify by Aaron to get company from method 2
/*SELECT attribute1
INTO v_company
FROM hr_organization_units
WHERE organization_id = v_org_id; */
SELECT r.flex_value_rule_name
INTO v_company
FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u
WHERE r.flex_value_rule_id = u.flex_value_rule_id
AND u.application_id = fnd_profile.VALUE ('RESP_APPL_ID')
--application_id
AND u.responsibility_id = fnd_profile.VALUE ('RESP_ID');

-- responsibility id

--get ÔÂ·Ý
SELECT h.je_header_id
INTO v_je_header_id
FROM gl_je_headers h
WHERE ROWID = gl_je_headers_num_pkg.t_je (i);

SELECT DECODE (UPPER (SUBSTR (h.period_name, 1, 3)), --notice here
'JAN', '01',
'FEB', '02',
'MAR', '03',
'APR', '04',
'MAY', '05',
'JUN', '06',
'JUL', '07',
'AUG', '08',
'SEP', '09',
'OCT', '10',
'NOV', '11',
'DEC', '12'
),
p.adjustment_period_flag
INTO v_month_b,
v_month_a
FROM gl_je_headers h, gl_periods p, gl_sets_of_books b
WHERE h.je_header_id = v_je_header_id
AND h.set_of_books_id = b.set_of_books_id
AND h.period_name = p.period_name
AND b.period_set_name = p.period_set_name;

/*AND h.je_header_id =
(SELECT je_header_id
FROM gl_je_headers
WHERE h.ROWID = gl_je_headers_num_pkg.t_je (i)); */
IF v_month_a = 'Y'
THEN
v_month := 'AD';
ELSE
v_month := v_month_b;
END IF;

-- get max number of updated gl
SELECT COUNT (*)
INTO v_max
FROM gl_je_headers h
WHERE h.period_name = v_period_name
AND h.status = 'P'
AND h.external_reference IS NOT NULL;

--get year
SELECT SUBSTR (h.period_name, 5, 6)
INTO v_year
FROM gl_je_headers h
WHERE ROWID = gl_je_headers_num_pkg.t_je (i);

-- get year max number of updated gl
SELECT COUNT (*)
INTO v_max_y
FROM gl_je_headers h
WHERE SUBSTR (h.period_name, 5, 6) = SUBSTR (v_period_name, 5, 6)
AND h.status = 'P'
AND h.external_reference IS NOT NULL;

UPDATE gl_je_headers
SET external_reference =
TRIM (TO_CHAR (v_company, '00'))
|| v_month
|| TRIM (TO_CHAR ((v_max + 1), '000000'))
|| '|'
|| TRIM (TO_CHAR (v_year, '00'))
|| TRIM (TO_CHAR (v_max_y + 1, '00000000'))
WHERE ROWID = gl_je_headers_num_pkg.t_je (i);
--gl_je_headers_num_pkg.t_je (i);
END LOOP;
--commit;
END;
/