[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(DFF1.CONTEXT_CODE,'') acc_level_context
,nvl(DFF2.CONTEXT_CODE,'') acc_sub_context
,nvl(DFF6.CONTEXT_CODE,'') acc_bal_context
,nvl(DFF1.ATTRIBUTE_COLUMN, '') acc_level_position
,nvl(DFF2.ATTRIBUTE_COLUMN, '') sub_pj_position
,nvl(DFF3.ATTRIBUTE_COLUMN, '') sub_tp_position
,nvl(DFF4.ATTRIBUTE_COLUMN, '') sub_cc_position
,nvl(DFF5.ATTRIBUTE_COLUMN, '') 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 DFF2
,JA_CN_DFF_ASSIGNMENTS DFF3
,JA_CN_DFF_ASSIGNMENTS DFF4
,JA_CN_DFF_ASSIGNMENTS DFF5
,JA_CN_DFF_ASSIGNMENTS DFF6
WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
AND DFF2.DFF_TITLE_CODE = 'SAPA' -- Project
AND DFF3.DFF_TITLE_CODE = 'SATP' -- Third party
AND DFF4.DFF_TITLE_CODE = 'SACC' -- Cost center
AND DFF5.DFF_TITLE_CODE = 'SAEE' -- Personnel
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 DFF2.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF3.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF4.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF5.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 '
||' ,DECODE( '
||' nvl(FFV.' || l_sub_pj_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
||' , ''NNNN'', ''0'', ''1'') sub_flag '
||' ,nvl( DECODE(nvl(FFV.' || l_sub_pj_position || ', ''N''),'
||' ''Y'', ''Project/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
||' ''S'', ''Supplier/'',''C'', ''Customer/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
||' ''Y'', ''Cost Center/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
||' ''N''), ''Y'', ''Personnel/'', ''''), '
||' ''/'') 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 '
||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
||' DECODE( '
||' nvl(FFV.' || l_sub_pj_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
||' , ''NNNN'', ''0'', ''1''), '
||' ''0'') sub_flag '
||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_sub_context ||''','
||' nvl(DECODE(nvl(FFV.' || l_sub_pj_position || ', ''N''),'
||' ''Y'', ''Project/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
||' ''S'', ''Supplier/'',''C'', ''Customer/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
||' ''Y'', ''Cost Center/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
||' ''N''), ''Y'', ''Personnel/'', ''''), '
||' ''/''), ''/'') 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 DISTINCT ' --For porject of 'N' or 'COA'
||' FFV.FLEX_VALUE acc_number '
||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_level_context||''','
||' nvl(FFV.' || l_acc_level_position ||', ''''), '
||' '''') acc_level '
||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
||' DECODE( '
||' DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
||' ''N'', ''N'', '
||' ''COA'', nvl(FFV.' || l_sub_pj_position
||' , ''N'') ) || '
||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
||' , ''NNNN'', ''0'', ''1''), '
||' ''0'') sub_flag '
||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
||' nvl(DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
||' ''N'', '''', '--Leave Blank
||' ''COA'', DECODE(nvl(FFV.' || l_sub_pj_position
||' , ''N''), ''Y'', ''Project-COA/'', '''')'
||' )|| '
||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
||' ''Y'', ''Third Party/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
||' ''Y'', ''Cost Center/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
||' ''N''), ''Y'', ''Personnel/'', ''''), '
||' ''/''), ''/'') sub_item '
||' FROM FND_ID_FLEX_SEGMENTS FIFS '
||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
||' ,FND_FLEX_VALUE_SETS FFVS '
||' ,FND_FLEX_VALUES FFV '
||' ,GL_SETS_OF_BOOKS SOB '
||' WHERE '
--Get all correct row of FFV
||' SOB.set_of_books_id = ' || l_sob_id
||' AND SOB.global_attribute_category = ''JA.CN.GLXSTBKS.BOOKS'' '
||' AND SOB.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 '
\*--for all. --The context code may be null or others!
||' AND (FFV.VALUE_CATEGORY is null OR '
||' FFV.VALUE_CATEGORY = ''Subsidiary'') '*\
--for Subsidiary account item of project
||' AND ( nvl(SOB.GLOBAL_ATTRIBUTE1, ''N'') = ''N'' '
||' OR nvl(SOB.GLOBAL_ATTRIBUTE1, ''N'') = ''COA'' '
--AND DFF2.DFF_TITLE_CODE = ''SAPA''
||' ) '
;
'SELECT DISTINCT '
||' FFV.FLEX_VALUE acc_number '
||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_level_context||''','
||' nvl(FFV.' || l_acc_level_position ||', ''''), '
||' '''') acc_level '
||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
||' DECODE( '
||' DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
||' ''PA'', DECODE(nvl(BAL.PROJECT_NUMBER, ''''), '
||' '''', ''N'', ''Y'') '
\*||' ''PA'', DECODE( '
||' decode(nvl(BAL.SET_OF_BOOKS_ID, ''-1''), SOB.set_of_books_id, '
||' decode(nvl(BAL.account_segment, ''@@''), FFV.FLEX_VALUE, '
||' decode(nvl(BAL.project_source, ''@@''), ''PA'', '
||' nvl(BAL.PROJECT_NUMBER, ''''), ''''), '
||' ''''), ''''),'''', ''N'', ''Y'') '*\
||' ) || '
||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
||' , ''NNNN'', ''0'', ''1''), '
||' ''0'') sub_flag '
||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
||' nvl(DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
||' ''PA'', DECODE(nvl(BAL.PROJECT_NUMBER, ''''), '
||' '''', '''', ''Project-PM/'') '
\*||' ''PA'', DECODE( '
||' decode(nvl(BAL.SET_OF_BOOKS_ID, ''-1''), SOB.set_of_books_id, '
||' decode(nvl(BAL.account_segment, ''@@''), FFV.FLEX_VALUE, '
||' decode(nvl(BAL.project_source, ''@@''), ''PA'', '
||' nvl(BAL.PROJECT_NUMBER, ''''), ''''), '
||' ''''), ''''),'''', '''', ''Project-PM/'') '*\
||' )|| '
||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
||' ''Y'', ''Third Party/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
||' ''Y'', ''Cost Center/'', '''')|| '
||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
||' ''N''), ''Y'', ''Personnel/'', ''''), '
||' ''/''), ''/'') sub_item '
||' FROM FND_ID_FLEX_SEGMENTS FIFS '
||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
||' ,FND_FLEX_VALUE_SETS FFVS '
||' ,FND_FLEX_VALUES FFV '
||' ,GL_SETS_OF_BOOKS SOB '
--Balane table used here only for project from Project Module
--||' ,JA_CN_ACCOUNT_BALANCES BAL '
||' ,(SELECT * FROM JA_CN_ACCOUNT_BALANCES WHERE '
||' project_source = ''PA'' AND set_of_books_id = '
|| l_sob_id || ') BAL '
||' WHERE '
--Get all correct row of FFV
||' SOB.set_of_books_id = ' || l_sob_id
||' AND SOB.global_attribute_category = ''JA.CN.GLXSTBKS.BOOKS'' '
||' AND SOB.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 '
\*--for all. --The context code may be null or others!
||' AND (FFV.VALUE_CATEGORY is null OR '
||' FFV.VALUE_CATEGORY = ''Subsidiary'') '*\
--for Subsidiary account item of project
||' AND nvl(SOB.GLOBAL_ATTRIBUTE1, ''N'') = ''PA'' '
||' AND BAL.account_segment(+) = FFV.FLEX_VALUE '
;
SELECT DECODE(nvl(DFF1.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF1.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
|| DECODE(nvl(DFF2.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF2.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
|| DECODE(nvl(DFF3.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF3.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
|| DECODE(nvl(DFF4.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF4.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
|| DECODE(nvl(DFF5.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF5.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 DFF2
,JA_CN_DFF_ASSIGNMENTS DFF3
,JA_CN_DFF_ASSIGNMENTS DFF4
,JA_CN_DFF_ASSIGNMENTS DFF5
,JA_CN_DFF_ASSIGNMENTS DFF6
WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
AND DFF2.DFF_TITLE_CODE = 'SAPA' -- Project
AND DFF3.DFF_TITLE_CODE = 'SATP' -- Third party
AND DFF4.DFF_TITLE_CODE = 'SACC' -- Cost center
AND DFF5.DFF_TITLE_CODE = 'SAEE' -- Personnel
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 DFF2.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF3.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF4.CHART_OF_ACCOUNTS_ID=l_coa_id
AND DFF5.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 FLV.meaning
INTO l_project_meaning
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.lookup_code = 'PJ'
and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
and FLV.LANGUAGE = userenv('LANG')
;
SELECT FLV.meaning
INTO l_thirdparty_meaning
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.lookup_code = 'TP'
and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
and FLV.LANGUAGE = userenv('LANG')
;
SELECT FLV.meaning
INTO l_supplier_meaning
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.lookup_code = 'S'
and FLV.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
and FLV.LANGUAGE = userenv('LANG')
;
SELECT FLV.meaning
INTO l_customer_meaning
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.lookup_code = 'C'
and FLV.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
and FLV.LANGUAGE = userenv('LANG')
;
SELECT FLV.meaning
INTO l_costcenter_meaning
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.lookup_code = 'CC'
and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
and FLV.LANGUAGE = userenv('LANG')
;
SELECT FLV.meaning
INTO l_personnel_meaning
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.lookup_code = 'PERSON'
and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
and FLV.LANGUAGE = userenv('LANG')
;
/* SELECT nvl(SYS_PAR.ACCOUNT_STRUCTURE, '') acc_str
\*,nvl(SYS_PAR.ENT_FLAG, 'ENT') ent_flag*\
INTO l_na_acc_str
\*,l_ent_flag*\
FROM JA_CN_SYSTEM_PARAMETERS_ALL SYS_PAR
WHERE SYS_PAR.LEGAL_ENTITY_ID = P_LE_ID --using parameter P_LE_ID*/
--Using dynamitc sql to fetch account structure. The view
-- 'ja_cn_account_structures_kfv' doesn't exist when creating patch.
/* SELECT nvl(ACC_STR_V.concatenated_segments, '') acc_str
INTO l_na_acc_str
FROM JA_CN_SYSTEM_PARAMETERS_ALL SYS_PAR
,ja_cn_account_structures_kfv ACC_STR_V
WHERE ACC_STR_V.account_structure_id = SYS_PAR.ACCOUNT_STRUCTURE_ID
AND SYS_PAR.LEGAL_ENTITY_ID = P_LE_ID --using parameter P_LE_ID
;*/
'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 distinct FIFStr.Concatenated_Segment_Delimiter
INTO l_delimiter_label
FROM FND_ID_FLEX_STRUCTURES FIFStr
WHERE FIFStr.APPLICATION_ID=7000
AND FIFStr.ID_FLEX_CODE='ACCT' --JA_CN_ACCOUNT_STRUCTURES
/* FROM GL_SETS_OF_BOOKS SOB
,FND_ID_FLEX_STRUCTURES FIFStr
WHERE SOB.set_of_books_id = l_sob_id --using variable l_sob_id
AND FIFStr.APPLICATION_ID=7000
AND FIFStr.ID_FLEX_CODE='ACCT' --JA_CN_ACCOUNT_STRUCTURES
AND FIFStr.ID_FLEX_NUM = SOB.chart_of_accounts_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')
/*and ( nvl('', FLV1.territory_code) = FLV1.territory_code
or FLV1.territory_code is null )
and FLV1.VIEW_APPLICATION_ID = 3
and FLV1.SECURITY_GROUP_ID = 0*/
;
SELECT nvl(GLOBAL_ATTRIBUTE1, 'N')
,nvl(GLOBAL_ATTRIBUTE3, '') --DECODE(nvl(GLOBAL_ATTRIBUTE3, ''), '', '', 'PS')
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id = l_sob_id
AND global_attribute_category = 'JA.CN.GLXSTBKS.BOOKS';*/
SELECT *
FROM (
--Get name for projects from project module
SELECT DISTINCT
BAL.project_number pj_number
,nvl(PPA.name, '') pj_name --name for project from PA
FROM JA_CN_ACCOUNT_BALANCES BAL
,PA_PROJECTS_ALL PPA
WHERE BAL.Ledger_Id= l_ledger_id --using variable l_sob_id
AND BAL.account_segment IS NOT NULL
AND nvl(BAL.project_source, 'N') = 'PA'
and BAL.project_number IS NOT NULL
--AND PPA.project_id = BAL.PROJECT_ID --PROJECT_ID is no use here, replaced it.
AND PPA.SEGMENT1 = BAL.project_number
UNION
--Get name for projects from COA
SELECT DISTINCT
BAL.project_number pj_number
,nvl(FFVT.description, '') pj_name --name for project from COA
FROM JA_CN_ACCOUNT_BALANCES BAL
,GL_LEDGERS LEDGER
,Ja_Cn_Sub_Acc_Sources_All SUBAS
,FND_ID_FLEX_SEGMENTS FIFS
,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALUES_TL FFVT
,FND_FLEX_VALUES FFV
WHERE BAL.Ledger_Id= l_ledger_id --using variable l_sob_id
AND BAL.account_segment IS NOT NULL
AND nvl(BAL.project_source, 'N') = 'COA'
and BAL.project_number IS NOT NULL
--Get project name. --PROJECT_ID is no use here, replaced it.
AND FFV.FLEX_VALUE = BAL.project_number
AND LEDGER.ledger_id = BAL.ledger_id
AND LEDGER.chart_of_accounts_id = FIFS.id_flex_num
AND FIFS.id_flex_num = FSAV.id_flex_num
AND SUBAS.CHART_OF_ACCOUNTS_ID = LEDGER.CHART_OF_ACCOUNTS_ID -- ?? NOT SURE
AND ( ( nvl(SUBAS.PROJECT_SOURCE_FLAG, 'N') = 'COA' --Currently it's from COA
and SUBAS.COA_SEGMENT = FSAV.APPLICATION_COLUMN_NAME
)
OR --It's a old one
( (nvl(SUBAS.PROJECT_SOURCE_FLAG, 'N') = 'N' OR nvl(SUBAS.PROJECT_SOURCE_FLAG, 'N') = 'PA')
and SUBAS.HISTORY_COA_SEGMENT = FSAV.APPLICATION_COLUMN_NAME
)
)
AND FIFS.application_id = 101
AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FIFS.application_id = FSAV.application_id
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 FFVT.flex_value_id = FFV.flex_value_id
AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
) tmp_pj_tbl
-- add order by to keep the output item's seqence
order by tmp_pj_tbl.pj_number
;
SELECT *
FROM (
SELECT DISTINCT
PV.vendor_id vender_id
/*,'S'||nvl(PV.SEGMENT1, '') sup_number*/
--?? ,nvl(PV.SEGMENT1, '') sup_number --column vendor_number of view AP_VENDORS_V
,nvl(BAL.Third_Party_Number,'') sup_number -- temp solutin ????
,nvl(PV.VENDOR_NAME, '') sup_name
/*,nvl(LC_TYPE.DISPLAYED_FIELD, '') sup_type --vendor_type_disp*/
,''
FROM JA_CN_ACCOUNT_BALANCES BAL
,PO_VENDORS PV
/*,PO_LOOKUP_CODES LC_TYPE*/
WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
AND BAL.account_segment IS NOT NULL
AND BAL.THIRD_PARTY_ID IS NOT NULL
AND nvl(BAL.THIRD_PARTY_TYPE, 'X') = 'S'
AND BAL.THIRD_PARTY_ID = PV.vendor_id
) tmp_sup_tbl
-- add order by to keep the output item's seqence
ORDER BY tmp_sup_tbl.sup_number
/*-- Type
AND LC_TYPE.LOOKUP_CODE(+) = PV.VENDOR_TYPE_LOOKUP_CODE
and LC_TYPE.LOOKUP_TYPE(+) = 'VENDOR TYPE'*/
;
SELECT *
FROM (
SELECT DISTINCT
CUST.CUST_ACCOUNT_ID cust_account_id
,CUST.PARTY_ID party_id
/*,'C'||nvl(CUST.ACCOUNT_NUMBER, '') cust_number*/
--?? ,nvl(CUST_PARTY.Party_Number, '') cust_number -- take hz_parties.Party_Number to keep consistency with sla export
--,nvl(BAL.Third_Party_Number,'') cust_number -- temp solutin ???? --Deleted by Chaoqun for fixing bug#8420682 on 19-May-2009
,nvl(CUST_PARTY.PARTY_NUMBER,'') cust_number --Updated by Chaoqun for fixing bug#8420682 on 19-May-2009
,nvl(CUST_PARTY.PARTY_NAME, '') cust_name
/*,nvl(L_CLASS.MEANING, '') cust_class --CUSTOMER_CLASS_MEANING*/
,nvl(CP.CREDIT_RATING, '') cust_credit
FROM JA_CN_ACCOUNT_BALANCES BAL
,HZ_CUST_ACCOUNTS CUST
,HZ_PARTIES CUST_PARTY
/*,AR_LOOKUPS L_CLASS*/
,HZ_CUSTOMER_PROFILES CP
WHERE BAL.Ledger_Id = l_ledger_id --using variable l_ledger_id
AND BAL.LEGAL_ENTITY_ID=l_le_id --using variable l_le_id
AND BAL.account_segment IS NOT NULL
AND BAL.THIRD_PARTY_ID IS NOT NULL
AND nvl(BAL.THIRD_PARTY_TYPE, 'X') = 'C'
AND BAL.THIRD_PARTY_ID = CUST.CUST_ACCOUNT_ID
AND CUST.PARTY_ID = CUST_PARTY.PARTY_ID
/*-- Class
AND CUST.CUSTOMER_CLASS_CODE = L_CLASS.LOOKUP_CODE(+)
and L_CLASS.LOOKUP_TYPE(+) = 'CUSTOMER CLASS'*/
-- Credit rating
AND CP.CUST_ACCOUNT_ID(+) = CUST.CUST_ACCOUNT_ID
and CP.site_use_id is null
) tmp_cst_tbl
-- add order by to keep the output item's seqence
order by tmp_cst_tbl.cust_number
;
SELECT nvl(FLV.meaning,'') sup_meaning
,nvl(FLV1.meaning,'') cust_meaning
INTO l_sup_meaning
,l_cust_meaning
FROM FND_LOOKUP_VALUES FLV
,FND_LOOKUP_VALUES FLV1
WHERE FLV.lookup_code = 'S'
AND FLV.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
AND FLV.LANGUAGE = userenv('LANG')
AND FLV1.lookup_code = 'C'
AND FLV1.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
AND FLV1.LANGUAGE = userenv('LANG')
;
SELECT DISTINCT
nvl(PVSA.CITY, '') sup_city
/* --JiaQian make it sure that get city from column 'city'
nvl(PVSA.PROVINCE,
nvl(PVSA.STATE, '')) sup_city
*/
,nvl(PVSA.ADDRESS_LINE1, '') sup_addr
BULK COLLECT INTO l_all_sup_city_addr
FROM PO_VENDOR_SITES_ALL PVSA
,HR_ORGANIZATION_INFORMATION HOI
WHERE --Check "Primary Pay" Vendor site of OUs under current LE
HOI.org_information_context = 'Operating Unit Information'
AND HOI.Org_Information2 = l_le_id --using variable l_le_id
AND HOI.Org_Information3 = l_ledger_id --using variable l_ledger_id
AND PVSA.Org_id = HOI.ORGANIZATION_ID
AND PVSA.vendor_id = l_vender_id --using variable l_vender_id
and nvl(PVSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y'
;
SELECT DISTINCT
'0' || DECODE(nvl(PVC.AREA_CODE, ''), '', '', PVC.AREA_CODE || '-')
|| nvl(PVC.PHONE, '') sup_phone
BULK COLLECT INTO l_all_sup_phone
FROM PO_VENDOR_CONTACTS PVC
,PO_VENDOR_SITES_ALL PVSA
,HR_ORGANIZATION_INFORMATION HOI
WHERE PVC.vendor_site_id = PVSA.vendor_site_id
--Check "Primary Pay" Vendor site of OUs under current LE
AND HOI.org_information_context = 'Operating Unit Information'
AND HOI.Org_Information2 = l_le_id --using variable l_le_id
AND HOI.Org_Information3 = l_sob_id --using variable l_sob_id
AND PVSA.Org_id = HOI.ORGANIZATION_ID
AND PVSA.vendor_id = l_vender_id --using variable l_vender_id
and nvl(PVSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y'
;*/
SELECT DISTINCT
nvl(PVC.AREA_CODE, '')
|| DECODE(NVL(PVC.AREA_CODE, ''),'','','-')
|| nvl(PVC.PHONE, '') sup_phone
BULK COLLECT INTO l_all_sup_phone
FROM PO_VENDOR_CONTACTS PVC
,AP_SUPPLIER_SITES_ALL ASSA
WHERE PVC.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND ASSA.VENDOR_ID=l_vender_id --using variable l_vender_id
AND nvl(ASSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y';
SELECT DISTINCT
nvl(LOC.CITY, '') cust_city
/* --JiaQian make it sure that get city from column 'city'
nvl(LOC.PROVINCE,
nvl(LOC.STATE, '')) sup_city
*/
,nvl(LOC.ADDRESS1, '') cust_addr
BULK COLLECT INTO l_all_cust_city_addr
FROM HZ_CUST_ACCT_SITES_ALL ADDR
,HZ_LOCATIONS LOC
,HZ_PARTY_SITES PARTY_SITE
,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
,HZ_CUST_SITE_USES_ALL SU
,HR_ORGANIZATION_INFORMATION HOI
WHERE --ADDR.CUST_ACCOUNT_ID alias CUSTOMER_ID in AR_ADDRESSES_V
ADDR.CUST_ACCOUNT_ID = l_cust_account_id --using variable l_cust_account_id
and ADDR.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
and LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
and nvl(LOC.LANGUAGE, userenv('LANG')) = userenv('LANG')
and LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
and NVL(ADDR.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
--Check Customer site of OUs under current LE
AND HOI.org_information_context = 'Operating Unit Information'
AND HOI.Org_Information2 = l_le_id --using variable l_le_id
AND HOI.Org_Information3 = l_ledger_id --using variable l_ledger_id
AND ADDR.org_id = HOI.ORGANIZATION_ID
-- Check "Primary Bill To"
and SU.CUST_ACCT_SITE_ID= ADDR.CUST_ACCT_SITE_ID --alias address_id in HZ_SITE_USES_V
and SU.SITE_USE_CODE = 'BILL_TO'
and nvl(SU.PRIMARY_FLAG, 'N') = 'Y'
;
SELECT
DECODE(HCP.PRIMARY_FLAG, 'Y', 'PRIMARY',
DECODE(HCP.PRIMARY_BY_PURPOSE, 'Y', 'PREFERRED', 'NORMAL')
) cust_phone_priority
,NVL(HCP.PHONE_COUNTRY_CODE,'')
|| DECODE(NVL(HCP.PHONE_COUNTRY_CODE,''),'','','-')
|| NVL(HCP.PHONE_AREA_CODE,'')
|| DECODE(NVL(HCP.PHONE_AREA_CODE,''),'','','-')
|| HCP.PHONE_NUMBER cust_phone
BULK COLLECT INTO l_all_cust_phone
FROM HZ_CONTACT_POINTS HCP
,HZ_PARTY_SITES HPS
WHERE HCP.OWNER_TABLE_ID(+)=HPS.PARTY_SITE_ID
AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND NVL(HCP.PRIMARY_FLAG,'')='Y'
AND NVL(HCP.STATUS,'')='A' --only 'Active' one
AND NVL(HCP.CONTACT_POINT_TYPE,'')='PHONE'
AND NVL(HCP.PHONE_LINE_TYPE,'')='GEN' --only 'Telephone' type, just the code 'GEN'
AND HPS.PARTY_ID = l_party_id --using variable l_cust_account_id
ORDER BY HCP.primary_flag desc,
HCP.primary_by_purpose desc
;
SELECT *
FROM (
SELECT DISTINCT
FFV.FLEX_VALUE cc_number
,nvl(FFVT.description, '') cc_name
FROM JA_CN_ACCOUNT_BALANCES BAL
,FND_ID_FLEX_SEGMENTS FIFS
,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALUES_TL FFVT
,FND_FLEX_VALUES FFV
,GL_LEDGERS LEDGER
WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
AND BAL.account_segment IS NOT NULL
and BAL.cost_center IS NOT NULL
--for name. OR: FFVT.flex_value_meaning = BAL.cost_center
AND FFV.FLEX_VALUE = BAL.cost_center
AND LEDGER.Ledger_Id = l_ledger_id --using variable l_ledger_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_id = FSAV.application_id
AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'FA_COST_CTR'
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 FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
) tmp_cc_tbl
ORDER BY tmp_cc_tbl.cc_number
;
SELECT *
FROM (
SELECT DISTINCT
BAL.personnel_number person_number
,nvl(PER.last_name||PER.first_name, '') person_name
FROM JA_CN_ACCOUNT_BALANCES BAL
,PER_ALL_PEOPLE_F PER
WHERE BAL.Ledger_Id = l_ledger_id --using variable l_ledger_id
AND BAL.account_segment IS NOT NULL
and BAL.personnel_id IS NOT NULL
AND PER.person_id = BAL.personnel_id
) tmp_psn_tbl
ORDER BY tmp_psn_tbl.person_number
;