[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
FND_DATE.CANONICAL_TO_DATE(P_START_DATE),
FND_DATE.CANONICAL_TO_DATE(P_END_DATE),
FND_NUMBER.CANONICAL_TO_NUMBER(P_LEDGER_ID),
FND_DATE.CANONICAL_TO_DATE(P_START_UPDATE_DATE),
FND_DATE.CANONICAL_TO_DATE(P_END_UPDATE_DATE)
-- FND_NUMBER.CANONICAL_TO_NUMBER(:P_DOC_ID),
-- FND_NUMBER.CANONICAL_TO_NUMBER(:P_DOC_VALUE),
-- FND_NUMBER.CANONICAL_TO_NUMBER(:P_AMOUNT_FROM),
-- FND_NUMBER.CANONICAL_TO_NUMBER(:P_AMOUNT_TO),
-- FND_NUMBER.CANONICAL_TO_NUMBER(:P_SUB_DOC_ID),
-- FND_NUMBER.CANONICAL_TO_NUMBER(:P_SUB_DOC_VALUE)
INTO
H_START_DATE,
H_END_DATE,
H_SET_OF_BOOKS_ID,
H_START_UPDATE_DATE,
H_END_UPDATE_DATE
-- :H_DOC_ID,
-- :H_DOC_VALUE,
-- :H_AMOUNT_FROM,
-- :H_AMOUNT_TO,
-- :H_SUB_DOC_ID,
-- :H_SUB_DOC_VALUE
FROM DUAL;
SELECT NAME , CHART_OF_ACCOUNTS_ID
INTO ACCESS_SET_NAME, CHART_OF_ACCOUNTS_ID
FROM GL_ACCESS_SETS
WHERE ACCESS_SET_ID = P_ACCESS_SET_ID;
/* GET THE LEDGER INFO WHEN A LEDGER/LEDGER SET IS SELECTED BY THE USER*/
IF P_LEDGER_ID IS NOT NULL THEN
/* GET INFORMATION OF LEDGER PARAMETER. */
BEGIN
SELECT CURRENCY_CODE, OBJECT_TYPE_CODE
INTO L_LEDGER_CURR,
L_LEDGER_TYPE
FROM GL_LEDGERS
WHERE LEDGER_ID = P_LEDGER_ID;
'(SELECT LOOKUP_CODE FROM GL_LOOKUPS ' ||
'WHERE LOOKUP_TYPE = ''MJE_BATCH_STATUS'' '||
'AND LOOKUP_CODE NOT IN (''S'', ''I'', ''U'', ''P'')) ';
OUTPUT=":SELECT_ACCOUNT"
MODE="SELECT"
DISPLAY="ALL"
TABLEALIAS="CC"');
OUTPUT=":SELECT_ACCT_SEGMENT"
MODE="SELECT"
DISPLAY="GL_ACCOUNT"
IDISPLAY="GL_ACCOUNT"
TABLEALIAS="CC"');
OUTPUT=":SELECT_BAL_SEGMENT"
MODE="SELECT"
DISPLAY="GL_BALANCING"
TABLEALIAS="CC"');
OUTPUT=":SELECT_COST_CTR_SEGMENT"
MODE="SELECT"
DISPLAY="FA_COST_CTR"
IDISPLAY="FA_COST_CTR"
TABLEALIAS="CC"');*/
OUTPUT=":SELECT_SECONDARY_SEGMENT"
MODE="SELECT"
DISPLAY="GL_SECONDARY_TRACKING"
TABLEALIAS="CC"'); */
SELECT 'CC.'||APPLICATION_COLUMN_NAME , FORM_LEFT_PROMPT
INTO SELECT_SECONDARY_SEGMENT, PARAM_SEC_SEG_NAME
FROM FND_ID_FLEX_SEGMENTS_VL S
WHERE S.ID_FLEX_CODE = 'GL#'
AND S.ID_FLEX_NUM = CHART_OF_ACCOUNTS_ID
AND S.ENABLED_FLAG = 'Y'
AND S.SEGMENT_NUM = P_SEC_SEG_NUM;
OUTPUT=":P_ACC_SEGMENT_SELECT"
MODE="SELECT"
DISPLAY="ALL"
TABLEALIAS="GCC"');
P_CONTRA_ACCOUNT_WHERE := ' AND EXISTS (SELECT GJL2.JE_LINE_NUM
FROM GL_JE_LINES GJL2, GL_CODE_COMBINATIONS GCC2
WHERE
GJL2.JE_HEADER_ID = GLH.JE_HEADER_ID AND
GJL2.CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID AND
((GJL2.SUBLEDGER_DOC_SEQUENCE_ID = GLL.SUBLEDGER_DOC_SEQUENCE_ID AND
GJL2.SUBLEDGER_DOC_SEQUENCE_VALUE = GLL.SUBLEDGER_DOC_SEQUENCE_VALUE) OR
(GLL.SUBLEDGER_DOC_SEQUENCE_ID IS NULL AND GJL2.SUBLEDGER_DOC_SEQUENCE_ID IS NULL AND
GJL2.SUBLEDGER_DOC_SEQUENCE_VALUE IS NULL)) AND
((GLL.ACCOUNTED_DR IS NOT NULL AND GLL.ACCOUNTED_DR<> 0 AND GJL2.ACCOUNTED_CR IS NOT NULL
AND GJL2.ACCOUNTED_CR <> 0) OR
(GLL.ACCOUNTED_CR IS NOT NULL AND GLL.ACCOUNTED_CR<> 0 AND GJL2.ACCOUNTED_DR IS NOT NULL
AND GJL2.ACCOUNTED_DR <> 0)) AND '||P_CONTRA_ACCOUNT|| ' )';
IF (P_START_UPDATE_DATE IS NOT NULL OR P_END_UPDATE_DATE IS NOT NULL) THEN
IF P_START_UPDATE_DATE IS NULL THEN
P_JOURNAL_UPD_DATE_WHERE :=
' AND TRUNC(GLH.LAST_UPDATE_DATE) <= '''|| H_END_UPDATE_DATE ||'''';
ELSIF P_END_UPDATE_DATE IS NULL THEN
P_JOURNAL_UPD_DATE_WHERE := ' AND TRUNC(GLH.LAST_UPDATE_DATE) >= '''|| H_START_UPDATE_DATE ||'''';
ELSIF ((P_START_UPDATE_DATE IS NOT NULL) AND (P_END_UPDATE_DATE IS NOT NULL)) THEN
P_JOURNAL_UPD_DATE_WHERE := ' AND TRUNC(GLH.LAST_UPDATE_DATE) BETWEEN ''' || H_START_UPDATE_DATE || ''' AND ''' || H_END_UPDATE_DATE || '''';
IF(P_LAST_UPDATED_BY IS NOT NULL) THEN
P_JOURNAL_LAST_UPD_WHERE := ' AND GLH.LAST_UPDATED_BY = '||P_LAST_UPDATED_BY;
P_JOURNAL_DATE_WHERE_2 := ' AND GLH2.PERIOD_NAME IN (SELECT PERIOD_NAME FROM GL_PERIOD_STATUSES PS
WHERE PS.APPLICATION_ID = 101 AND PS.LEDGER_ID = LGR1.LEDGER_ID ';
P_JOURNAL_DATE_WHERE_2 := ' AND GLL2.PERIOD_NAME IN (SELECT PERIOD_NAME FROM GL_PERIOD_STATUSES PS
WHERE PS.APPLICATION_ID = 101 AND PS.SET_OF_BOOKS_ID = ' || P_LEDGER_ID || ' ';
:P_JOURNAL_DATE_WHERE := ' AND GLH.PERIOD_NAME IN (SELECT + CARDINALITY(PS,2 ) PERIOD_NAME
FROM GL_PERIOD_STATUSES PS
WHERE PS.APPLICATION_ID = 101 AND PS.LEDGER_ID = LGR.LEDGER_ID ';
P_JOURNAL_DATE_WHERE := ' AND GLL.PERIOD_NAME IN (SELECT /*+ CARDINAILTY(PS,2 ) */ PERIOD_NAME
FROM GL_PERIOD_STATUSES PS
WHERE PS.APPLICATION_ID = 101 AND PS.LEDGER_ID = LGR.LEDGER_ID ';
IF NVL(P_JOURNALS_LINE_FLAG,'L') = 'J' THEN -- SELECTION BY JOURNAL BASE
AMOUNT_WHERE:= NULL;
P_JOURNAL_FROM_CLAUSE:= ', (SELECT DISTINCT GLL2.JE_HEADER_ID JE_HEADER_ID,
GLL2.SUBLEDGER_DOC_SEQUENCE_ID SUB_DOC_SEQ_ID,
GLL2.SUBLEDGER_DOC_SEQUENCE_VALUE SUB_DOC_SEQ_VALUE
FROM ' || JOURNAL_FROM ||
' GL_LEDGERS LGR1,
GL_JE_LINES GLL2,
GL_JE_HEADERS GLH2,
GL_CODE_COMBINATIONS GCC3 '||L_LEDGER_FROM||'
WHERE LGR1.LEDGER_ID = GLH2.LEDGER_ID
'|| JOURNAL_WHERE ||'
'||L_LEDGER_WHERE||'
'|| P_JOURNAL_DATE_WHERE_2 || '
'|| AMOUNT_WHERE2 || '
'|| P_AFF_WHERE_JRL ||'
'|| L_DAS_WHERE||
' AND GLL2.JE_HEADER_ID = GLH2.JE_HEADER_ID
AND GLH2.LEDGER_ID = GLL2.LEDGER_ID
AND GLL2.CODE_COMBINATION_ID = GCC3.CODE_COMBINATION_ID
AND (NVL(GLL2.ACCOUNTED_DR, 0) <> 0 OR NVL(GLL2.ACCOUNTED_CR, 0) <> 0 )) SUB_TABLE
';
ELSE -- SELECTION BY LINE BASE
P_JOURNAL_FROM_CLAUSE:= ' ';
SELECT NAME
INTO PARAM_LEDGER_NAME
FROM GL_LEDGERS
WHERE LEDGER_ID = P_LEDGER_ID;
PARAM_START_DATE := H_START_UPDATE_DATE;
PARAM_END_DATE := H_END_UPDATE_DATE;
SELECT USER_JE_SOURCE_NAME
INTO PARAM_SOURCE
FROM GL_JE_SOURCES
WHERE JE_SOURCE_NAME = P_SOURCE;
SELECT USER_JE_CATEGORY_NAME
INTO PARAM_CATEGORY
FROM GL_JE_CATEGORIES
WHERE JE_CATEGORY_NAME = P_CATEGORY;
IF P_LAST_UPDATED_BY IS NOT NULL THEN
SELECT USER_NAME
INTO PARAM_LAST_UPDATED_BY
FROM FND_USER
WHERE USER_ID = P_LAST_UPDATED_BY;
GL_INFO.GL_GET_LOOKUP_VALUE('M', P_JOURNALS_LINE_FLAG, 'SELECTION_BASE',
PARAM_BASIS, T_ERRORBUFFER);
SELECT NAME
INTO PARAM_DOC_SEQ_NAME
FROM FND_DOCUMENT_SEQUENCES
WHERE DOC_SEQUENCE_ID = P_DOC_ID;
SELECT NAME
INTO PARAM_SUB_DOC_SEQ_NAME
FROM FND_DOCUMENT_SEQUENCES
WHERE DOC_SEQUENCE_ID = P_SUB_DOC_ID;
SELECT MIN(start_date)
INTO start_period_date
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = H_SET_OF_BOOKS_ID
AND adjustment_period_flag <> 'Y';
SELECT p1.period_name
INTO start_period_name
FROM gl_period_statuses p1
WHERE p1.application_id = 101
AND p1.set_of_books_id = H_SET_OF_BOOKS_ID
AND p1.adjustment_period_flag <> 'Y'
AND p1.start_date =
(SELECT MIN(p2.start_date)
FROM gl_period_statuses p2
WHERE p2.application_id = p1.application_id
AND p2.set_of_books_id = p1.set_of_books_id
AND p2.adjustment_period_flag <> 'Y');
SELECT budget_name
INTO l_budget_name
FROM GL_BUDGET_VERSIONS
WHERE budget_version_id = BUDGET_VERSION_ID;
SELECT encumbrance_type
INTO l_enc_type
FROM GL_ENCUMBRANCE_TYPES
WHERE encumbrance_type_id = ENCUMBRANCE_TYPE_ID;
SELECT GLT.TAX_CODE
INTO l_tax_code
FROM GL_TAX_CODES_V GLT
WHERE to_char(GLT.TAX_CODE_ID) = TAX_CODE_ID
AND GLT.TAX_TYPE_CODE = TAX_TYPE_CODE;
l_CONTRA_ACCOUNT_NAME := GL_XML_JOURNAL_RPT_PKG.Get_Contra_Account(SELECT_ACCT_SEGMENT,
HDR_ID,
SUB_DOC_SEQ_ID,
SUB_DOC_SEQ_VAL,
ACCOUNTED_DR,
ACCOUNTED_CR);
P_BAL_SEG_NAME => SELECT_BAL_SEGMENT,
P_BAL_SEG_VAL => BAL_SEG_VAL,
P_ACCT_SEG_NAME => SELECT_ACCT_SEGMENT,
P_ACCT_SEG_VAL => ACCT_SEG_VAL,
P_SEC_SEG_NAME => SELECT_SECONDARY_SEGMENT,
P_SEC_SEG_VAL => ADDITIONAL_SEGMENT_VALUE);
P_BAL_SEG_NAME => SELECT_BAL_SEGMENT,
P_BAL_SEG_VAL => BAL_SEG_VAL,
P_ACCT_SEG_NAME => SELECT_ACCT_SEGMENT,
P_ACCT_SEG_VAL => ACCT_SEG_VAL,
P_SEC_SEG_NAME => SELECT_SECONDARY_SEGMENT,
P_SEC_SEG_VAL => ADDITIONAL_SEGMENT_VALUE);
Function SELECT_ACCT_SEGMENT_p return varchar2 is
Begin
return SELECT_ACCT_SEGMENT;
Function SELECT_BAL_SEGMENT_p return varchar2 is
Begin
return SELECT_BAL_SEGMENT;
Function SELECT_ACCOUNT_p return varchar2 is
Begin
return SELECT_ACCOUNT;
Function SELECT_SECONDARY_SEGMENT_p return varchar2 is
Begin
return SELECT_SECONDARY_SEGMENT;
Function PARAM_LAST_UPDATED_BY_p return varchar2 is
Begin
return PARAM_LAST_UPDATED_BY;
Function SELECT_COST_CTR_SEGMENT_p return varchar2 is
Begin
return SELECT_COST_CTR_SEGMENT;
Function P_ACC_SEGMENT_SELECT_p return varchar2 is
Begin
return P_ACC_SEGMENT_SELECT;
Function H_START_UPDATE_DATE_p return date is
Begin
return H_START_UPDATE_DATE;
Function H_END_UPDATE_DATE_p return date is
Begin
return H_END_UPDATE_DATE;