[Home] [Help]
151: -- and item of project, third party, cost center and personnel and Balance Side
152: -- of all accounts.
153:
154: -- add Global Data Elements supporting
155: -- while using Global Data Elements, the value of FND_FLEX_VALUES.VALUE_CATEGORY will be null
156: IF 'Global Data Elements'=nvl(l_acc_level_context,'') THEN
157: l_sql_str :=
158: 'SELECT DISTINCT '
159: ||' FFV.FLEX_VALUE acc_number '
178: ||' acc_bal '
179: ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
180: ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
181: ||' ,FND_FLEX_VALUE_SETS FFVS '
182: ||' ,FND_FLEX_VALUES FFV '
183: --||' ,GL_LEDGERS LEDGER '
184: ||' WHERE '
185: --Get all correct row of FFV
186: --||' LEDGER.ledger_id = ' || l_LEDGER_id
226: ||' '''') acc_bal '
227: ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
228: ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
229: ||' ,FND_FLEX_VALUE_SETS FFVS '
230: ||' ,FND_FLEX_VALUES FFV '
231: --||' ,GL_LEDGERS LEDGER '
232: ||' WHERE '
233: --Get all correct row of FFV
234: --||' LEDGER.ledger_id = ' || l_LEDGER_id
281: ||' ''/''), ''/'') sub_item '
282: ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
283: ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
284: ||' ,FND_FLEX_VALUE_SETS FFVS '
285: ||' ,FND_FLEX_VALUES FFV '
286: ||' ,GL_SETS_OF_BOOKS SOB '
287: ||' WHERE '
288: --Get all correct row of FFV
289: ||' SOB.set_of_books_id = ' || l_sob_id
355: ||' ''/''), ''/'') sub_item '
356: ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
357: ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
358: ||' ,FND_FLEX_VALUE_SETS FFVS '
359: ||' ,FND_FLEX_VALUES FFV '
360: ||' ,GL_SETS_OF_BOOKS SOB '
361: --Balane table used here only for project from Project Module
362: --||' ,JA_CN_ACCOUNT_BALANCES BAL '
363: ||' ,(SELECT * FROM JA_CN_ACCOUNT_BALANCES WHERE '
469: l_acc_seg_serial NUMBER;
470: TYPE t_level_seg_lens IS TABLE OF NUMBER;
471: l_acc_seg_lens t_level_seg_lens;
472:
473: l_na_number FND_FLEX_VALUES.FLEX_VALUE%TYPE;
474: l_na_name FND_FLEX_VALUES_TL.description%TYPE;
475: l_na_parent VARCHAR2(1);
476: l_na_level VARCHAR2(100);
477: l_na_sub_flag VARCHAR2(1);
470: TYPE t_level_seg_lens IS TABLE OF NUMBER;
471: l_acc_seg_lens t_level_seg_lens;
472:
473: l_na_number FND_FLEX_VALUES.FLEX_VALUE%TYPE;
474: l_na_name FND_FLEX_VALUES_TL.description%TYPE;
475: l_na_parent VARCHAR2(1);
476: l_na_level VARCHAR2(100);
477: l_na_sub_flag VARCHAR2(1);
478: l_na_sub_item VARCHAR2(100);
561: FROM JA_CN_ACC_SUBS_V sub
562: ,FND_ID_FLEX_SEGMENTS FIFS
563: ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
564: ,FND_FLEX_VALUE_SETS FFVS
565: ,FND_FLEX_VALUES FFV
566: ,FND_FLEX_VALUES_TL FFVT
567: ,GL_LEDGERS ledger
568: WHERE --Get all correct row of FFV
569: ledger.ledger_id = l_ledger_id --using variable l_sob_id
562: ,FND_ID_FLEX_SEGMENTS FIFS
563: ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
564: ,FND_FLEX_VALUE_SETS FFVS
565: ,FND_FLEX_VALUES FFV
566: ,FND_FLEX_VALUES_TL FFVT
567: ,GL_LEDGERS ledger
568: WHERE --Get all correct row of FFV
569: ledger.ledger_id = l_ledger_id --using variable l_sob_id
570: AND ledger.chart_of_accounts_id = FIFS.id_flex_num
1137: WHERE set_of_books_id = l_sob_id
1138: AND global_attribute_category = 'JA.CN.GLXSTBKS.BOOKS';*/
1139:
1140: --Cursor to get project_number from table JA_CN_ACCOUNT_BALANCES, and name from table
1141: -- PA_PROJECTS_ALL or FND_FLEX_VALUES_TL.
1142: --If two projects from PA and COA are with a same number and same name then
1143: -- only show them one time.
1144: CURSOR c_pj IS
1145: SELECT *
1168: ,Ja_Cn_Sub_Acc_Sources_All SUBAS
1169: ,FND_ID_FLEX_SEGMENTS FIFS
1170: ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1171: ,FND_FLEX_VALUE_SETS FFVS
1172: ,FND_FLEX_VALUES_TL FFVT
1173: ,FND_FLEX_VALUES FFV
1174: WHERE BAL.Ledger_Id= l_ledger_id --using variable l_sob_id
1175: AND BAL.account_segment IS NOT NULL
1176: AND nvl(BAL.project_source, 'N') = 'COA'
1169: ,FND_ID_FLEX_SEGMENTS FIFS
1170: ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1171: ,FND_FLEX_VALUE_SETS FFVS
1172: ,FND_FLEX_VALUES_TL FFVT
1173: ,FND_FLEX_VALUES FFV
1174: WHERE BAL.Ledger_Id= l_ledger_id --using variable l_sob_id
1175: AND BAL.account_segment IS NOT NULL
1176: AND nvl(BAL.project_source, 'N') = 'COA'
1177: and BAL.project_number IS NOT NULL
1800: l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1801:
1802: l_row_count NUMBER; --count of rows
1803: l_cc_number JA_CN_ACCOUNT_BALANCES.cost_center%TYPE;
1804: l_cc_name FND_FLEX_VALUES_TL.description%TYPE;
1805:
1806: --Cursor to get cost_center from table JA_CN_ACCOUNT_BALANCES and description from table
1807: -- FND_FLEX_VALUES_TL, as Department number and name.
1808: --Because the value set of cost center can be changed manually as natural account, it is
1803: l_cc_number JA_CN_ACCOUNT_BALANCES.cost_center%TYPE;
1804: l_cc_name FND_FLEX_VALUES_TL.description%TYPE;
1805:
1806: --Cursor to get cost_center from table JA_CN_ACCOUNT_BALANCES and description from table
1807: -- FND_FLEX_VALUES_TL, as Department number and name.
1808: --Because the value set of cost center can be changed manually as natural account, it is
1809: -- no sense to store cost center id in table JA_CN_ACCOUNT_BALANCES. Thus the name should
1810: -- be gotten with the full flow.
1811: CURSOR c_cc IS
1817: FROM JA_CN_ACCOUNT_BALANCES BAL
1818: ,FND_ID_FLEX_SEGMENTS FIFS
1819: ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1820: ,FND_FLEX_VALUE_SETS FFVS
1821: ,FND_FLEX_VALUES_TL FFVT
1822: ,FND_FLEX_VALUES FFV
1823: ,GL_LEDGERS LEDGER
1824: WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
1825: AND BAL.account_segment IS NOT NULL
1818: ,FND_ID_FLEX_SEGMENTS FIFS
1819: ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1820: ,FND_FLEX_VALUE_SETS FFVS
1821: ,FND_FLEX_VALUES_TL FFVT
1822: ,FND_FLEX_VALUES FFV
1823: ,GL_LEDGERS LEDGER
1824: WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
1825: AND BAL.account_segment IS NOT NULL
1826: and BAL.cost_center IS NOT NULL