[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(DFF1.CONTEXT_CODE,'') acc_level_context
,nvl(NULL,'') acc_sub_context
,nvl(DFF6.CONTEXT_CODE,'') acc_bal_context
,nvl(DFF1.ATTRIBUTE_COLUMN, '') acc_level_position
,nvl(NULL, '') sub_pj_position
,nvl(NULL, '') sub_tp_position
,nvl(NULL, '') sub_cc_position
,nvl(NULL, '') sub_person_position
,nvl(DFF6.ATTRIBUTE_COLUMN, '') acc_bal_position
INTO l_acc_level_context
,l_acc_sub_context
,l_acc_bal_context
,l_acc_level_position
,l_sub_pj_position
,l_sub_tp_position
,l_sub_cc_position
,l_sub_person_position
,l_acc_bal_position
FROM JA_CN_DFF_ASSIGNMENTS DFF1
,JA_CN_DFF_ASSIGNMENTS DFF6
WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
AND DFF6.DFF_TITLE_CODE = 'ACBS' -- Balance Side
-- Check whether the flexfields had been set for current COA_ID
AND DFF1.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF6.CHART_OF_ACCOUNTS_ID=l_coa_id
;
'SELECT DISTINCT '
||' FFV.FLEX_VALUE acc_number '
||' ,nvl(FFV.' || l_acc_level_position ||', '''') '
||' acc_level '
||' ,''0'' sub_flag '
||' , ''/'' sub_item '
||' ,nvl(FFV.' || l_acc_bal_position ||', '''') '
||' acc_bal '
||' FROM FND_ID_FLEX_SEGMENTS FIFS '
||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
||' ,FND_FLEX_VALUE_SETS FFVS '
||' ,FND_FLEX_VALUES FFV '
--||' ,GL_LEDGERS LEDGER '
||' WHERE '
--Get all correct row of FFV
--||' LEDGER.ledger_id = ' || l_LEDGER_id
||' FIFS.id_flex_num = '|| l_coa_id ||' '
||' AND FIFS.id_flex_num = FSAV.id_flex_num '
||' AND FIFS.application_id = 101 '
||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
||' AND FIFS.application_id = FSAV.application_id '
||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
--||' AND FFV.VALUE_CATEGORY IS NULL '
||' ORDER BY FFV.FLEX_VALUE '
;
'SELECT DISTINCT '
||' FFV.FLEX_VALUE acc_number '
||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_level_context ||''','
||' nvl(FFV.' || l_acc_level_position ||', ''''), '
||' '''') acc_level '
||' , ''0'' sub_flag '
||' , ''/'' sub_item '
||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_bal_context ||''','
||' nvl(FFV.' || l_acc_bal_position ||', ''''), '
||' '''') acc_bal '
||' FROM FND_ID_FLEX_SEGMENTS FIFS '
||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
||' ,FND_FLEX_VALUE_SETS FFVS '
||' ,FND_FLEX_VALUES FFV '
--||' ,GL_LEDGERS LEDGER '
||' WHERE '
--Get all correct row of FFV
--||' LEDGER.ledger_id = ' || l_LEDGER_id
||' FIFS.id_flex_num = '|| l_coa_id ||' '
||' AND FIFS.id_flex_num = FSAV.id_flex_num '
||' AND FIFS.application_id = 101 '
||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
||' AND FIFS.application_id = FSAV.application_id '
||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
||' ORDER BY FFV.FLEX_VALUE '
;
SELECT DECODE(nvl(DFF1.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF1.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
|| DECODE(nvl(DFF6.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF6.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
dff_assign
FROM JA_CN_DFF_ASSIGNMENTS DFF1
,JA_CN_DFF_ASSIGNMENTS DFF6
WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
AND DFF6.DFF_TITLE_CODE = 'ACBS' -- Balance Side
-- Check whether the flexfields had been set for current COA_ID
AND DFF1.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF6.CHART_OF_ACCOUNTS_ID=l_coa_id
;
SELECT DISTINCT
--FFV.FLEX_VALUE acc_number -- replace with sub.acc_number
sub.acc_number
,nvl(FFVT.description, '') acc_name
,DECODE(FFV.summary_flag, 'Y', 'Y', 'N') acc_parent
,SUBSTR(TO_CHAR(FFV.COMPILED_VALUE_ATTRIBUTES) --such as 'Y Y L'
,5,1) acc_type_code
,nvl(sub.acc_level, '') acc_level
,nvl(sub.sub_flag, '0') sub_flag
,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
nvl(sub.sub_item, '/'), 'Project', l_project_meaning
), 'Third Party', l_thirdparty_meaning
), 'Supplier', l_supplier_meaning
), 'Customer', l_customer_meaning
), 'Cost Center', l_costcenter_meaning
), 'Personnel', l_personnel_meaning
) sub_item
,nvl(sub.acc_bal, '') acc_bal
FROM JA_CN_ACC_SUBS_V sub
,FND_ID_FLEX_SEGMENTS FIFS
,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALUES FFV
,FND_FLEX_VALUES_TL FFVT
,GL_LEDGERS ledger
WHERE --Get all correct row of FFV
ledger.ledger_id = l_ledger_id --using variable l_sob_id
AND ledger.chart_of_accounts_id = FIFS.id_flex_num
AND FIFS.id_flex_num = FSAV.id_flex_num
AND FIFS.application_id = 101
AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FIFS.application_id = FSAV.application_id
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
--AND nvl(FFV.ENABLED_FLAG, 'N') = 'Y' --Including disabled accounts
AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
--For account level, subsidiary account flag and item
AND sub.acc_number(+) = FFV.FLEX_VALUE
order by sub.acc_number
;
'SELECT '
||' nvl(ACC_STR_V.concatenated_segments, '''') acc_str '
||' FROM Ja_Cn_Sub_Acc_Sources_All SYS_PAR '
||' ,' || l_account_structures_kfv || ' ACC_STR_V '
||'WHERE ACC_STR_V.account_structure_id = SYS_PAR.ACCOUNTING_STRUCT_ID'
||' AND SYS_PAR.CHART_OF_ACCOUNTS_ID = ' || l_coa_id --using parameter P_LE_ID
;
SELECT fifs.concatenated_segment_delimiter separator
INTO l_delimiter_label
FROM FND_ID_FLEX_STRUCTURES fifs
,ja_cn_account_structures_kfv jcask
,ja_cn_sub_acc_sources_all jcsasa
WHERE fifs.id_flex_num = jcask.structure_id
AND jcask.account_structure_id = jcsasa.accounting_struct_id
AND jcsasa.chart_of_accounts_id = pn_coa_id;
INSERT INTO JA_CN_COA_NA_EXCEPTIONS
( ACCOUNT_SEGMENT
,ACCOUNT_LEVEL
,VALUE_LENGTH
,EXPECTED_LENGTH
,ACCOUNT_STRUCTURE
,NA_REQUEST_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
VALUES( l_na_number
,l_na_level
,l_length
,l_expected_length
,l_na_acc_str
,l_na_curr_req_id
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.LOGIN_ID
);
SELECT count(*)
INTO l_exceptions_count
FROM JA_CN_COA_NA_EXCEPTIONS
WHERE NA_REQUEST_ID = l_na_curr_req_id
;
DELETE
FROM JA_CN_COA_NA_EXCEPTIONS
WHERE NA_REQUEST_ID = l_na_curr_req_id;
SELECT DISTINCT
nvl(UOM.UNIT_OF_MEASURE, '') acc_uom
INTO l_na_mea
FROM GL_LEDGERS LEDGER
,GL_STAT_ACCOUNT_UOM UOM
WHERE LEDGER.ledger_id = l_ledger_id --using variable l_sob_id
AND UOM.CHART_OF_ACCOUNTS_ID = LEDGER.CHART_OF_ACCOUNTS_ID
AND UOM.ACCOUNT_SEGMENT_VALUE = l_na_number --using variable l_na_number
;
SELECT nvl(FLV.meaning,'') acc_type
,FLV1.meaning acc_bal_side
INTO l_na_type
,l_na_bal
FROM FND_LOOKUP_VALUES FLV
,FND_LOOKUP_VALUES FLV1
WHERE --Get meaning of account type
FLV.lookup_code = l_acc_type_code --using variable l_acc_type_code
AND FLV.lookup_type = l_ent_acc_type --'ACCOUNT_TYPE'
and FLV.LANGUAGE = userenv('LANG')
--The following 3 conditions should be remained
AND ( nvl('', FLV.territory_code) = FLV.territory_code
or FLV.territory_code is null )
AND FLV.VIEW_APPLICATION_ID = 0
AND FLV.SECURITY_GROUP_ID = 0
--Get meaning of balance side
AND FLV1.lookup_code = DECODE(
l_acc_type_code, --using variable l_acc_type_code
'A', DECODE(l_acc_bal_code, 'C', 'C', 'D'), --using variable l_acc_bal_code
'E', DECODE(l_acc_bal_code, 'C', 'C', 'D'),
'L', DECODE(l_acc_bal_code, 'D', 'D', 'C'),
'O', DECODE(l_acc_bal_code, 'D', 'D', 'C'),
'R', DECODE(l_acc_bal_code, 'D', 'D', 'C')
)
AND FLV1.lookup_type = 'JA_CN_DEBIT_CREDIT'--'DEBIT_CREDIT'
AND FLV1.LANGUAGE = userenv('LANG');