Oracle Reports(Запросы и триггеры отчетов)
Запрос:-
/* Formatted on 5/18/2016 10:40:35 AM (QP5 v5.163.1008.3004) */
SELECT gcc.CODE_COMBINATION_ID,
GCC.SEGMENT7 NATURAL_ACC,
FFVT.DESCRIPTION NATURAL_ACC_DESC,
GCC.SEGMENT10 BRANCH_CODE,
GCC.SEGMENT4 MAIN_FUNC_CODE,
GCC.SEGMENT3 BUS_FUNC_CODE,
TRUNC (GJH.POSTED_DATE) VOUCHER_DATE,
GJH.DOC_SEQUENCE_VALUE VOUCHER_NO,
DECODE (GJH.JE_CATEGORY, '1', 'ETL', GJH.JE_CATEGORY) VOUCHER_TYPE,
GJH.PERIOD_NAME,
GJL.DESCRIPTION LINE_DESC,
GJL.ACCOUNTED_DR DEBIT,
GJL.ACCOUNTED_CR CREDIT,
SUBSTR (ffv.COMPILED_VALUE_ATTRIBUTES, 5, 1) Account_Type,
DECODE (
Account_Type,
'E', (SELECT NVL (
SUM (
NVL (gjl1.ACCOUNTED_DR, 0)
- NVL (gjl1.ACCOUNTED_CR, 0)),
0)
FROM gl_je_lines gjl1,
gl_je_headers gjh1,
gl_code_combinations gcc1
WHERE gjh1.JE_HEADER_ID = gjl1.JE_HEADER_ID
AND gjl1.CODE_COMBINATION_ID =
gcc1.CODE_COMBINATION_ID
AND gjh1.ledger_id = '2022'
AND gjh1.ACTUAL_FLAG = 'A'
AND gjl1.status = 'P'
AND gcc1.SEGMENT7 = gcc.SEGMENT7
AND TRUNC (GJH1.DEFAULT_EFFECTIVE_DATE) BETWEEN '01-JAN-'
|| TO_CHAR (
TO_DATE (
:P_DATE_FROM,
' YYYY/MM/DD HH24:MI:SS '),
'YY')
AND TO_CHAR (
TO_DATE (
:P_DATE_FROM,
'YYYY/MM/DD HH24:MI:SS ')
- 1,
'DD-MON-YY') &ADD_WHERE_CLAUSE1
),'R',( select nvl(sum(nvl(gjl1.ACCOUNTED_DR,0) - nvl(gjl1.ACCOUNTED_CR,0)),0)
from gl_je_lines gjl1,
gl_je_headers gjh1,
gl_code_combinations gcc1
where gjh1.JE_HEADER_ID = gjl1.JE_HEADER_ID
and gjl1.CODE_COMBINATION_ID = gcc1.CODE_COMBINATION_ID
and gjh1.ledger_id = '2022'
and gjh1.ACTUAL_FLAG = 'A'
and gjl1.status = 'P'
and gcc1.SEGMENT7 = gcc.SEGMENT7
and trunc(GJH1.DEFAULT_EFFECTIVE_DATE ) BETWEEN '01-JAN-'||to_char(to_date(:P_DATE_FROM,' YYYY/MM/DD HH24:MI:SS '),'YY')
AND to_char(to_date(:P_DATE_FROM,'YYYY/MM/DD HH24:MI:SS ')-1,'DD-MON-YY')
&ADD_WHERE_CLAUSE1
),( select sum(nvl(gjl1.ACCOUNTED_DR,0) - nvl(gjl1.ACCOUNTED_CR,0))
from gl_je_lines gjl1,
gl_je_headers gjh1,
gl_code_combinations gcc1
where gjh1.JE_HEADER_ID = gjl1.JE_HEADER_ID
and gjl1.CODE_COMBINATION_ID = gcc1.CODE_COMBINATION_ID
and gjh1.ledger_id = '2022'
and gjh1.ACTUAL_FLAG = 'A'
and gjl1.status = 'P'
and gcc1.SEGMENT7 = gcc.SEGMENT7
and trunc(GJH1.DEFAULT_EFFECTIVE_DATE ) < to_char(to_date(:P_DATE_FROM,' YYYY/MM/DD HH24:MI:SS '),'DD-MON-YY')
&ADD_WHERE_CLAUSE1
)) Opening_balance
FROM GL_CODE_COMBINATIONS GCC,
GL_JE_LINES GJL,
GL_JE_HEADERS GJH,
FND_FLEX_VALUES FFV,
FND_FLEX_VALUES_TL FFVT
WHERE GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND FFV.FLEX_VALUE_ID = FFVT.FLEX_VALUE_ID
AND GCC.SEGMENT7 = FFV.FLEX_VALUE
AND FFV.FLEX_VALUE_SET_ID = '1013540'
AND GCC.SEGMENT7 <> '301010203004'
AND GJH.ACTUAL_FLAG = 'A' --- JUZER MODIFIED -----
--and gjh.REVERSED_JE_HEADER_ID is null
--and gjh.JE_HEADER_ID not in ( select distinct nvl(a.REVERSED_JE_HEADER_ID,0)from gl_je_headers a where a.JE_HEADER_ID <> gjh.JE_HEADER_ID )
AND GJL.STATUS = 'P'
--and trunc(gjh.DEFAULT_EFFECTIVE_DATE) between :p_date_from and :p_date_to
AND GCC.SEGMENT7 between :P_NAT_ACCT1 and :P_NAT_ACCT2
and trunc(GJH.DEFAULT_EFFECTIVE_DATE ) BETWEEN to_char(to_date(:P_DATE_FROM,'YYYY/MM/DD HH24:MI:SS '),'DD-MON-YY')
AND to_char(to_date(:P_DATE_TO,'YYYY/MM/DD HH24:MI:SS '),'DD-MON-YY')
&ADD_WHERE_CLAUSE
ORDER BY GCC.SEGMENT7,GJH.POSTED_DATE
и в своем отчете триггер имеет это после формы параметра
/* Отформатировано 18.05.2016 в 10:37:19 (QP5 v5.163.1008.3004) */
FUNCTION AfterPForm
RETURN BOOLEAN
IS
LV_DATE_FROM VARCHAR2 (100);
LV_DATE_TO VARCHAR2 (100);
BEGIN
IF :P_BRANCH_CODE IS NOT NULL
THEN
:ADD_WHERE_CLAUSE :=
:ADD_WHERE_CLAUSE || ' AND GCC.SEGMENT10 = :P_BRANCH_CODE ';
END IF;
/* IF :P_DATE_FROM IS NOT NULL AND :P_DATE_TO IS NOT NULL THEN
:ADD_WHERE_CLAUSE := :ADD_WHERE_CLAUSE || ' AND TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN TO_CHAR(TO_DATE(:P_DATE_FROM,''YYYY/MM/DD HH24:MI:SS''),''DD-MON-YYYY'') AND TO_CHAR(TO_DATE(:P_DATE_TO,''YYYY/MM/DD HH24:MI:SS''),''DD-MON-YYYY'') ';
:ADD_WHERE_CLAUSE1 := :ADD_WHERE_CLAUSE1 || ' and trunc(gjh1.DEFAULT_EFFECTIVE_DATE) < to_char(to_date(:P_DATE_FROM,''YYYY/MM/DD HH24:MI:SS''),''DD-MON-YYYY'') ';
:ADD_WHERE_CLAUSE3 := :ADD_WHERE_CLAUSE3 || ' and trunc(gjh3.DEFAULT_EFFECTIVE_DATE) < to_char(to_date(:P_DATE_FROM,''YYYY/MM/DD HH24:MI:SS''),''DD-MON-YYYY'') ';
:ADD_WHERE_CLAUSE4 := :ADD_WHERE_CLAUSE4 || ' AND TRUNC(GJH4.DEFAULT_EFFECTIVE_DATE) BETWEEN TO_CHAR(TO_DATE(:P_DATE_FROM,''YYYY/MM/DD HH24:MI:SS''),''DD-MON-YYYY'') AND TO_CHAR(TO_DATE(:P_DATE_TO,''YYYY/MM/DD HH24:MI:SS''),''DD-MON-YYYY'') ';
END IF;
*/
IF :P_SOURCE IS NOT NULL
THEN
IF :P_SOURCE = 'ETL'
THEN
:ADD_WHERE_CLAUSE :=
:ADD_WHERE_CLAUSE
|| ' and gjh.JE_SOURCE = ''1'' and gjh.JE_CATEGORY = ''1'' and gjh.REVERSED_JE_HEADER_ID is null and gjh.JE_HEADER_ID not in ( select distinct nvl(a.REVERSED_JE_HEADER_ID,0)from gl_je_headers a where a.JE_HEADER_ID <> gjh.JE_HEADER_ID )';
:ADD_WHERE_CLAUSE1 :=
:ADD_WHERE_CLAUSE1
|| ' and gjh1.JE_SOURCE = ''1'' and gjh1.JE_CATEGORY = ''1''and gjh1.REVERSED_JE_HEADER_ID is null and gjh1.JE_HEADER_ID not in ( select distinct nvl(a.REVERSED_JE_HEADER_ID,0)from gl_je_headers a where a.JE_HEADER_ID <> gjh1.JE_HEADER_ID ) ';
:ADD_WHERE_CLAUSE3 :=
:ADD_WHERE_CLAUSE3
|| ' and gjh3.JE_SOURCE = ''1'' and gjh3.JE_CATEGORY = ''1''and gjh3.REVERSED_JE_HEADER_ID is null and gjh3.JE_HEADER_ID not in ( select distinct nvl(a.REVERSED_JE_HEADER_ID,0)from gl_je_headers a where a.JE_HEADER_ID <> gjh3.JE_HEADER_ID ) ';
:ADD_WHERE_CLAUSE4 :=
:ADD_WHERE_CLAUSE4
|| ' and gjh4.JE_SOURCE = ''1'' and gjh4.JE_CATEGORY = ''1''and gjh4.REVERSED_JE_HEADER_ID is null and gjh4.JE_HEADER_ID not in ( select distinct nvl(a.REVERSED_JE_HEADER_ID,0)from gl_je_headers a where a.JE_HEADER_ID <> gjh4.JE_HEADER_ID ) ';
ELSIF :P_SOURCE = 'OFSA'
THEN
:ADD_WHERE_CLAUSE :=
:ADD_WHERE_CLAUSE
|| ' and gjh.JE_SOURCE <> ''1'' and gjh.JE_CATEGORY <> ''1''';
:ADD_WHERE_CLAUSE1 :=
:ADD_WHERE_CLAUSE1
|| ' and gjh1.JE_SOURCE <> ''1'' and gjh1.JE_CATEGORY <> ''1''';
:ADD_WHERE_CLAUSE3 :=
:ADD_WHERE_CLAUSE3
|| ' and gjh3.JE_SOURCE <> ''1'' and gjh3.JE_CATEGORY <> ''1''';
:ADD_WHERE_CLAUSE3 :=
:ADD_WHERE_CLAUSE3
|| ' and gjh3.JE_SOURCE <> ''1'' and gjh3.JE_CATEGORY <> ''1''';
:ADD_WHERE_CLAUSE4 :=
:ADD_WHERE_CLAUSE4
|| ' and gjh4.JE_SOURCE <> ''1'' and gjh4.JE_CATEGORY <> ''1''';
END IF;
END IF;
/*
IF :P_PERIOD_FROM IS NOT NULL AND :P_PERIOD_TO IS NOT NULL THEN
SELECT TO_CHAR(TRUNC(GP.START_DATE),'DD-MON-YYYY')
INTO LV_DATE_FROM
FROM GL_PERIODS GP
WHERE GP.PERIOD_NAME = :P_PERIOD_FROM
AND PERIOD_SET_NAME = 'NIB_Calendar';
SELECT TO_CHAR(TRUNC(GP.END_DATE),'DD-MON-YYYY')
INTO LV_DATE_TO
FROM GL_PERIODS GP
WHERE GP.PERIOD_NAME = :P_PERIOD_TO
AND PERIOD_SET_NAME = 'NIB_Calendar';
:P_DATE_FROM := LV_DATE_FROM;
:P_DATE_TO := LV_DATE_TO;
:ADD_WHERE_CLAUSE := :ADD_WHERE_CLAUSE || ' and trunc(gjh.DEFAULT_EFFECTIVE_DATE) between :p_date_from and :p_date_to';
:ADD_WHERE_CLAUSE1 := :ADD_WHERE_CLAUSE1 || ' and trunc(GJH1.DEFAULT_EFFECTIVE_DATE ) <= (SELECT A.START_DATE-1 FROM GL_PERIODS A WHERE A.PERIOD_NAME = GJH.PERIOD_NAME ) ';
:ADD_WHERE_CLAUSE3 := :ADD_WHERE_CLAUSE3 || ' and trunc(GJH3.DEFAULT_EFFECTIVE_DATE ) <= (SELECT A.START_DATE-1 FROM GL_PERIODS A WHERE A.PERIOD_NAME = :P_PERIOD_FROM) ';
:ADD_WHERE_CLAUSE4 := :ADD_WHERE_CLAUSE4 || ' and trunc(gjh4.DEFAULT_EFFECTIVE_DATE) between :p_date_from and :p_date_to';
END IF;
*/
RETURN (TRUE);
END;
Я хочу выполнить запрос сверху на жабе
Он запрашивает параметр add_where_clause, может кто-нибудь сказать мне, какое значение положить, кто-то сказал мне, что триггер отчета add_where_clause придет.
Заранее спасибо!!!
2 ответа
Записывать Set define off; в верхней части скрипта вашей функции и нажмите кнопку F5.
У вас есть это в конце вашего запроса:
&ADD_WHERE_CLAUSE ORDER BY GCC.SEGMENT7, GJH.POSTED_DATE
Я полагаю, вам следует удалить &ADD_WHERE_CLAUSE, а затем выполнить запрос или заменить &ADD_WHERE_CLAUSE предложением WHERE.