[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT JCAB.ACCOUNT_SEGMENT --Account Number
FROM JA_CN_ACCOUNT_BALANCES JCAB,
GL_PERIODS GP,
GL_LEDGERS LED
WHERE JCAB.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
AND JCAB.LEGAL_ENTITY_ID = pn_legal_entity_id --parameter: pn_legal_entity_id
AND JCAB.PERIOD_NAME = GP.PERIOD_NAME
AND LED.LEDGER_ID = JCAB.LEDGER_ID
AND LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
/* AND GP.START_DATE BETWEEN
(SELECT START_DATE
FROM GL_PERIODS GP
WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.PERIOD_NAME = pv_period_from) --parameter: pv_period_from
AND (SELECT START_DATE
FROM GL_PERIODS GP
WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.PERIOD_NAME = pv_period_to) --parameter: pv_period_to*/
ORDER BY JCAB.ACCOUNT_SEGMENT;
SELECT MAX(JCAB.SEGMENT1) SEGMENT1
,MAX(JCAB.SEGMENT2) SEGMENT2
,MAX(JCAB.SEGMENT3) SEGMENT3
,MAX(JCAB.SEGMENT4) SEGMENT4
,MAX(JCAB.SEGMENT5) SEGMENT5
,MAX(JCAB.SEGMENT6) SEGMENT6
,MAX(JCAB.SEGMENT7) SEGMENT7
,MAX(JCAB.SEGMENT8) SEGMENT8
,MAX(JCAB.SEGMENT9) SEGMENT9
,MAX(JCAB.SEGMENT10) SEGMENT10
,MAX(JCAB.SEGMENT11) SEGMENT11
,MAX(JCAB.SEGMENT12) SEGMENT12
,MAX(JCAB.SEGMENT13) SEGMENT13
,MAX(JCAB.SEGMENT14) SEGMENT14
,MAX(JCAB.SEGMENT15) SEGMENT15
,MAX(JCAB.SEGMENT16) SEGMENT16
,MAX(JCAB.SEGMENT17) SEGMENT17
,MAX(JCAB.SEGMENT18) SEGMENT18
,MAX(JCAB.SEGMENT19) SEGMENT19
,MAX(JCAB.SEGMENT20) SEGMENT20
,MAX(JCAB.SEGMENT21) SEGMENT21
,MAX(JCAB.SEGMENT22) SEGMENT22
,MAX(JCAB.SEGMENT23) SEGMENT23
,MAX(JCAB.SEGMENT24) SEGMENT24
,MAX(JCAB.SEGMENT25) SEGMENT25
,MAX(JCAB.SEGMENT26) SEGMENT26
,MAX(JCAB.SEGMENT27) SEGMENT27
,MAX(JCAB.SEGMENT28) SEGMENT28
,MAX(JCAB.SEGMENT29) SEGMENT29
,MAX(JCAB.SEGMENT30) SEGMENT30
FROM JA_CN_ACCOUNT_BALANCES JCAB,
GL_PERIODS GP,
GL_LEDGERS LED
WHERE JCAB.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
AND JCAB.LEGAL_ENTITY_ID = pn_legal_entity_id --parameter: pn_legal_entity_id
AND JCAB.PERIOD_NAME = GP.PERIOD_NAME
AND LED.LEDGER_ID = JCAB.LEDGER_ID
AND LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
AND JCAB.ACCOUNT_SEGMENT = lv_account_number --Account Number
/* AND GP.START_DATE BETWEEN
(SELECT START_DATE
FROM GL_PERIODS GP
WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.PERIOD_NAME = pv_period_from) --parameter: pv_period_from
AND (SELECT START_DATE
FROM GL_PERIODS GP
WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.PERIOD_NAME = pv_period_to) --parameter: pv_period_to*/
;
SELECT *
--COA source
FROM (SELECT DISTINCT TO_NUMBER(SUBSTR(SUBSIDIARY_SEGMENT_CODE,
8)) AS SUB_ITEM_NUM, --Subsidiary Item Number
FIFS.SEGMENT_NAME AS SUB_ITEM_NAME, --Subsidiary Item Name
FIFS.SEGMENT_NAME AS CORR_RECORD, --Corresponding Record
FFVS.DESCRIPTION AS SUB_ITEM_DESC --Subsidiary Item Description
FROM FND_ID_FLEX_SEGMENTS FIFS,
FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
GL_LEDGERS LED,
FND_FLEX_VALUE_SETS FFVS,
JA_CN_SUB_ACC_MAPPING SAM
WHERE FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FIFS.APPLICATION_COLUMN_NAME =
FSAV.APPLICATION_COLUMN_NAME
AND (FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL' OR
(FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_GLOBAL' AND
NOT EXISTS
(SELECT *
FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV1
WHERE FSAV1.APPLICATION_ID = FSAV.APPLICATION_ID
AND FSAV1.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
AND FSAV1.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FSAV1.APPLICATION_COLUMN_NAME =
FSAV.APPLICATION_COLUMN_NAME
AND FSAV1.ATTRIBUTE_VALUE = 'Y'
AND FSAV1.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')))
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FIFS.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = FIFS.ID_FLEX_CODE
AND FSAV.ID_FLEX_CODE = 'GL#'
AND FIFS.APPLICATION_ID = FSAV.APPLICATION_ID
AND LED.CHART_OF_ACCOUNTS_ID = FIFS.ID_FLEX_NUM
AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
AND LED.CHART_OF_ACCOUNTS_ID = pn_chart_of_account_id --parameter: pn_chart_of_account_id
AND SAM.CHART_OF_ACCOUNTS_ID = LED.CHART_OF_ACCOUNTS_ID
AND SAM.CONTEXT_CODE = FSAV.APPLICATION_COLUMN_NAME
AND SAM.SOURCES_CODE = 'COA'
UNION
--SLA source
SELECT TO_NUMBER(SUBSTR(SUBSIDIARY_SEGMENT_CODE,
8)) AS SUB_ITEM_NUM, --Subsidiary Item Number
FLV1.MEANING AS SUB_ITEM_NAME, --Subsidiary Item Name
FLV2.MEANING AS CORR_RECORD, --Corresponding Record
FLV1.MEANING AS SUB_ITEM_DESC --Subsidiary Item Description
FROM FND_LOOKUP_VALUES FLV1,
FND_LOOKUP_VALUES FLV2,
JA_CN_SUB_ACC_MAPPING SAM
WHERE FLV1.LOOKUP_TYPE = 'JA_CN_SUB_ACC_SLA_CONTEXT'
AND FLV2.LOOKUP_TYPE = 'JA_CN_SLA_CORR_RECO'
AND DECODE(FLV1.LOOKUP_CODE, 'CUSTOMER', 'CUSTOMER RECORD',
'SUPPLIER', 'SUPPLIER RECORD',
'EMPLOYEE_SUPPLIER', 'EMPLOYEE RECORD')
= FLV2.LOOKUP_CODE
AND SAM.SOURCES_CODE = 'SLA'
AND SAM.CHART_OF_ACCOUNTS_ID = pn_chart_of_account_id --parameter: pn_chart_of_account_id
AND SAM.CONTEXT_CODE = FLV1.LOOKUP_CODE
AND FLV1.LANGUAGE = USERENV('LANG')
AND FLV2.LANGUAGE = USERENV('LANG')
UNION
--Project module source
SELECT TO_NUMBER(SUBSTR(SUBSIDIARY_SEGMENT_CODE,
8)) AS SUB_ITEM_NUM, --Subsidiary Item Number
XAHB.ANALYTICAL_CRITERION_CODE AS SUB_ITEM_NAME, --Subsidiary Item Name
XAHB.ANALYTICAL_CRITERION_CODE AS CORR_RECORD, --Corresponding Record
XAHT.DESCRIPTION AS SUB_ITEM_DESC --Subsidiary Item Description
FROM XLA_ANALYTICAL_HDRS_B XAHB,
XLA_ANALYTICAL_HDRS_TL XAHT,
JA_CN_SUB_ACC_MAPPING SAM
WHERE XAHB.ANALYTICAL_CRITERION_CODE = 'PROJECT_NUMBER'
AND XAHB.ANALYTICAL_CRITERION_CODE =
XAHT.ANALYTICAL_CRITERION_CODE
AND XAHB.ANALYTICAL_CRITERION_TYPE_CODE =
XAHT.ANALYTICAL_CRITERION_TYPE_CODE
AND XAHB.AMB_CONTEXT_CODE = XAHT.AMB_CONTEXT_CODE
AND XAHB.ANALYTICAL_CRITERION_CODE = SAM.CONTEXT_CODE
AND SAM.SOURCES_CODE = 'PROJECT MODULE'
AND SAM.CHART_OF_ACCOUNTS_ID = pn_chart_of_account_id --parameter: pn_chart_of_account_id
AND XAHT.LANGUAGE = USERENV('LANG'))
WHERE DECODE(SUB_ITEM_NUM, 1, lv_segment1, 2, lv_segment2, 3, lv_segment3, 4, lv_segment4, 5, lv_segment5,
6, lv_segment6, 7, lv_segment7, 8, lv_segment8, 9, lv_segment9, 10, lv_segment10,
11, lv_segment11, 12, lv_segment12, 13, lv_segment13, 14, lv_segment14, 15, lv_segment15,
16, lv_segment16, 17, lv_segment17, 18, lv_segment18, 19, lv_segment19, 20, lv_segment20,
21, lv_segment21, 22, lv_segment22, 23, lv_segment23, 24, lv_segment24, 25, lv_segment25,
26, lv_segment26, 27, lv_segment27, 28, lv_segment28, 29, lv_segment29, 30, lv_segment30,
NULL) IS NOT NULL
ORDER BY SUB_ITEM_NUM;