delete Journal Batch
select *
delete from gl_je_batches
where name ='Income Statement Close: 815151,ADJ-09,13-JAN-10 10:58:42'
select *
delete from gl_je_headers where je_batch_id = 81376
select *
delete from gl_je_lines where je_header_id in (321590,321591)
select *
--delete
from apps.gl_je_batches
where name ='Income Statement Close: 815155,13-JAN-10 11:04:44';
select *
--delete
from apps.gl_je_headers where je_batch_id = 81377 ;
select *
---delete
from apps.gl_je_lines where je_header_id in (321592,321593) ;
Update View
select * from PEUSER.V_TOTAL_ASSET_IN_OFA
1.Update view? to include the book? add " : GEVG_USD_GAAP "
2.create view PEUSER.V_PE_DEPRECIATION_vg
3.create view PEUSER.V_PE_ASSET_ID_2_vg
find ORG_ID
select *from HR_ALL_ORGANIZATION_UNITS_TL
lookup code:
GE_FA_CURRENCY_MAPPING
FND_LOOKUP_VALUES_VL
select organization_id
??? into v_org_id
??? from apps.hr_organization_units
?? where name = 'GECEPA_Operating Unit';
look up lock object
select * from v$locked_object? a,dba_objects b where a.OBJECT_ID = b.OBJECT_ID
fixfild
SELECT *
? FROM APPS.FND_FLEX_VALUES_VL FFVV,APPS.FND_FLEX_VALUE_SETS FFVS
?WHERE FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
?? AND FFVS.FLEX_VALUE_SET_NAME LIKE '%FA_LOCATION%'
?? and FFVV.enabled_flag = 'Y'
select FFVV.FLEX_VALUE,
?????? FFVV.VALUE_CATEGORY,
?????? FFVV.DESCRIPTION,
?????? DECODE(SUBSTR(FFVV.COMPILED_VALUE_ATTRIBUTES,5,1),
????????????? 'A',
????????????? 'Asset',
????????????? 'E',
????????????? 'Expense',
????????????? 'L',
????????????? 'Liability',
????????????? 'R',
????????????? 'Revenue',
????????????? 'O',
????????????? 'Ownership/Stockholder Equity',
????????????? SUBSTR(FFVV.COMPILED_VALUE_ATTRIBUTES,1)) type,
????? FFVV.attribute3 "Stat Account",
????? ( SELECT FFV.description
??????????????? FROM APPS.FND_FLEX_VALUES_VL? FFV,
???????????????????? APPS.FND_FLEX_VALUE_SETS FF
?????????????? WHERE FFV.FLEX_VALUE_SET_ID = FF.FLEX_VALUE_SET_ID
???????????????? AND FF.FLEX_VALUE_SET_NAME ='GEWATER_PRC_ACCT'
???????????????? and FFV.FLEX_VALUE=? FFVV.attribute3)? "Stat Name"
? FROM APPS.FND_FLEX_VALUES_VL FFVV,APPS.FND_FLEX_VALUE_SETS FFVS
?WHERE FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
?? AND FFVS.FLEX_VALUE_SET_NAME = 'GEWATER_COST_CENTER'
?? and FFVV.enabled_flag = 'Y'
?
select c.*
? from fND_ID_FLEXS????????????? a,
?????? FND_ID_FLEX_STRUCTURES_VL b,
?????? FND_ID_FLEX_SEGMENTS_VL?? c
?where 1 = 1
?? and b.id_flex_num = c.id_flex_num
?? and b.id_flex_structure_code = 'GEWATER_ACCOUNTING_FLEXFIELD'
?? and a.id_flex_code = b.id_flex_code
?? and a.application_id = b.application_id
?? and a.id_flex_name = 'Accounting Flexfield'
--- find user login system date
select fu.user_name,
?????? fu.description,
?????? /*max(fl.start_time) "The Last Login System",
?????? max(fl.end_time) "The Last Logout System",*/
?????? min(fl.start_time) "The First Login System"
? from FND_LOGINS fl,fnd_user fu
?WHERE fl.user_ID = fu.user_id
?? and fu.user_name in
?????? ('501709915','501749502','501710311','501781443','501479420')
?group by fu.user_name,fu.description
SELECT FU.USER_NAME,
?????? FU.description,
?????? FV.RESPONSIBILITY_NAME,
?????? FD.creation_date?????? "Resp_Start_Date",
?????? FD.end_date??????????? "Resp_End_Date",
?????? FU.Start_date????????? "Account_Start_Date",
?????? FU.end_date??????????? "Account_End_Date"
? FROM apps.FND_RESPONSIBILITY_VL?????? FV,
?????? apps.FND_USER_RESP_GROUPS_DIRECT FD,
?????? apps.FND_USER??????????????????? FU
?WHERE FD.RESPONSIBILITY_ID = FV.RESPONSIBILITY_ID
?? AND FD.RESPONSIBILITY_APPLICATION_ID = FV.APPLICATION_ID
?? AND FD.USER_ID = FU.USER_ID
?? AND (FV.END_DATE IS NULL OR FV.END_DATE > SYSDATE)
?? AND FU.USER_NAME in ('501709915',
??????????????????????? '501749502')??
? order by FU.end_date,FU.USER_NAME,FD.creation_date
---- find GESD Mapping
select FFVV.FLEX_VALUE,
?????? --FFVV.VALUE_CATEGORY,
?????? (SELECT FFV.description
????????? FROM APPS.FND_FLEX_VALUES_VL FFV,APPS.FND_FLEX_VALUE_SETS FF
???????? WHERE FFV.FLEX_VALUE_SET_ID = FF.FLEX_VALUE_SET_ID
?????????? AND FF.FLEX_VALUE_SET_NAME = 'GESD_MARS'
?????????? and FFV.FLEX_VALUE = FFVV.FLEX_VALUE) "MARS_LINES",
?????? -- FFVV.attribute3 ""Stat Account"",APPS.FND_FLEX_VALUE_SETS FF
???????? WHERE FFV.FLEX_VALUE_SET_ID = FF.FLEX_VALUE_SET_ID
?????????? AND FF.FLEX_VALUE_SET_NAME = 'GESD_MAP'
?????????? and FFV.FLEX_VALUE = FFVV.FLEX_VALUE) "TRANS_ORACLE_ACCOUNT"
? FROM APPS.FND_FLEX_VALUES_VL FFVV,APPS.FND_FLEX_VALUE_SETS FFVS
?WHERE FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
?? AND FFVS.FLEX_VALUE_SET_NAME = 'GESD_ACCOUNT'
?? and enabled_flag = 'Y'
?order by DECODE(SUBSTR(FFVV.COMPILED_VALUE_ATTRIBUTES,
???????????????? 'A',
???????????????? 'Asset',
???????????????? 'E',
???????????????? 'Expense',
???????????????? 'L',
???????????????? 'Liability',
???????????????? 'R',
???????????????? 'Revenue',
???????????????? 'O',
???????????????? 'Ownership/Stockholder Equity',
???????????????? SUBSTR(FFVV.COMPILED_VALUE_ATTRIBUTES,1)),
????????? FFVV.FLEX_VALUE
?
select DECODE(gc.segment1,'760479','S21',gc.segment1) COMPANY,
?????? gb.name sob,
?????? jb.name batch_name,
?????? jh.name journal_name,
?????? jh.posted_date,
?????? (select user_name
????????? from apps.fnd_user
???????? where user_id = jh.last_updated_by) poster,
?????? (select description
????????? from apps.fnd_user
???????? where user_id = jh.last_updated_by) poster_name,
?????? jl.accounted_dr,
?????? jl.accounted_cr,
?????? gc.segment1 ledger_entity,
?????? gc.segment2 account_no,
?????? (SELECT FFVV.description
????????? FROM APPS.FND_FLEX_VALUES_VL FFVV,APPS.FND_FLEX_VALUE_SETS FFVS
???????? WHERE FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
?????????? AND FFVS.FLEX_VALUE_SET_NAME = (case
???????????????? when gc.segment1 = '760479' then
????????????????? 'S21_ACCOUNT'
???????????????? WHEN gc.segment1 = 'GESD' THEN
????????????????? 'GESD_ACCOUNT'
???????????????? WHEN gc.segment1 = 'S25' THEN
????????????????? 'GECN_ACCOUNT'
???????????????? WHEN gc.segment1 = 'S29' THEN
????????????????? 'GECN_ACCOUNT'
?????????????? END)
?????????? and FFVV.FLEX_VALUE = gc.segment2) account_description,
?????? gc.segment1 || '.' || gc.segment2 || '.' || gc.segment3 || '.' ||
?????? gc.segment4 || '.' || gc.segment5 || '.' || gc.segment6 || '.' ||
?????? gc.segment7 category
? from apps.gl_je_batches??????? jb,
?????? apps.gl_je_headers??????? jh,
?????? apps.gl_je_lines????????? jl,
?????? apps.gl_code_combinations gc,
?????? APPS.GL_SETS_OF_BOOKS??? gb
?where jh.je_header_id = jl.je_header_id
?? and upper(jh.je_source) = 'MANUAL'
?? and jh.period_name in ('DEC-09','JAN-10')
?? and jh.set_of_books_id = jl.set_of_books_id
?? and jl.code_combination_id = gc.code_combination_id
?? ---and gc.segment1 in ('S25','S29','GESD')
?? and gb.set_of_books_id in (152,? 1,172)
?? and jl.status = 'P'
?? and jh.actual_flag = 'A'
?? and jb.je_batch_id(+) = jh.je_batch_id
?? and jb.set_of_books_id(+) = jh.set_of_books_id
?? and gb.SET_OF_BOOKS_ID = jh.set_of_books_id
?? and gb.mrc_sob_type_code ='P'
?
SELECT --BC.CODE_COMBINATION_ID,
?gb.name || '???? ' || gb.currency_code SOB,
?apps.GE_GET_GL_SEGMENT(BC.CODE_COMBINATION_ID,'FA_COST_CTR') cost,'GL_ACCOUNT') account,
?APPS.GE_TRIAL_BALANCE_NEW.GET_ACCOUNT_DESCRIPTION(BC.CODE_COMBINATION_ID) description,
?/*APPS.GE_TRIAL_BALANCE_NEW.GET_MARS_ACCOUNT('AP0025',
?APPS.GE_GET_GL_SEGMENT(BC.CODE_COMBINATION_ID,
??????????????????????? 'GL_ACCOUNT')) MARS_ACCOUNT,*/
?APPS.GE_TRIAL_BALANCE_NEW.GET_CONCATENATED_SEGMENTS(BC.CODE_COMBINATION_ID) account_flexfield,
?sum((CASE
?????? WHEN PS.EFFECTIVE_PERIOD_NUM = '&Period_from' /*'20090001'*/
??????? THEN
??????? 1
?????? ELSE
??????? 0
???? END) * (BC.BEGIN_BALANCE_DR - BC.BEGIN_BALANCE_CR)) BEGIN_BALANCE,
?sum(BC.PERIOD_NET_DR) PERIOD_NET_DR,
?sum(BC.PERIOD_NET_CR) PERIOD_NET_CR,
?sum(BC.PERIOD_NET_DR - BC.PERIOD_NET_CR) PERIOD_NET,
?sum((CASE
?????? WHEN PS.EFFECTIVE_PERIOD_NUM = '&Period_to' /*'20090003'*/
??????? THEN
??????? 1
?????? ELSE
??????? 0
???? END) * (BC.BEGIN_BALANCE_DR + BC.PERIOD_NET_DR - BC.BEGIN_BALANCE_CR -
???? BC.PERIOD_NET_CR)) END_BALANCE
? FROM apps.GL_BALANCES????????? BC,
?????? apps.GL_PERIOD_STATUSES?? PS,
?????? apps.gl_sets_of_books???? gb
?WHERE BC.SET_OF_BOOKS_ID = gb.set_of_books_id --50
?? and BC.Set_of_books_id in (50,51,67)
?? AND BC.CURRENCY_CODE = gb.currency_code --'CNY'
?? AND BC.ACTUAL_FLAG = 'A'
?? AND ((BC.BEGIN_BALANCE_DR - BC.BEGIN_BALANCE_CR) <> 0 OR
?????? BC.PERIOD_NET_DR <> 0 OR BC.PERIOD_NET_CR <> 0 OR
?????? (BC.PERIOD_NET_DR - BC.PERIOD_NET_CR) <> 0 OR
?????? (BC.BEGIN_BALANCE_DR + BC.PERIOD_NET_DR - BC.BEGIN_BALANCE_CR -
?????? BC.PERIOD_NET_CR) <> 0)
?? AND APPS.GE_TRIAL_BALANCE_NEW.GET_CC_SUMMARY_FLAG(BC.CODE_COMBINATION_ID) = 'N'
????? --AND GET_ACCOUNT_STATUS(BC.CODE_COMBINATION_ID) = 'Y'
?? AND APPS.GE_GET_GL_SEGMENT(BC.CODE_COMBINATION_ID,'GL_BALANCING') =
?????? 'AP0025'
?? AND PS.APPLICATION_ID = 101
?? AND PS.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID
?? AND PS.PERIOD_NAME = BC.PERIOD_NAME
?? AND PS.EFFECTIVE_PERIOD_NUM BETWEEN '&Period_from' /*'20090001'*/
?????? AND '&Period_to' /*'20090003'*/
?? and gc.code_combination_id = BC.CODE_COMBINATION_ID
?? and gc.segment3 in ('B295','DGA5','J453','TSCH','ET19')
?GROUP BY BC.CODE_COMBINATION_ID,gb.name,gb.currency_code
?ORDER BY gb.name,
????????? apps.GE_GET_GL_SEGMENT(BC.CODE_COMBINATION_ID,'FA_COST_CTR'),'GL_ACCOUNT');
declare
? p_book varchar2(20);
? i????? number;
? j????? number;
? k????? number;
? cursor c(i in number) is
??? select substr('S25S26S29S2C',i,3) sob from dual;
begin
? i := 1;
? k := 1;
? select length('S25S26S29S2C') / 3 into j from dual;
? for i in 1 .. j loop
??? for a in c(k) loop
????? p_book := a.sob;
????? dbms_output.put_line('sob: ' || p_book);
??? end loop;
??? k := k + 3;
? end loop;
end;
?
FUNCTION fn_split(p_str IN VARCHAR2,p_delimiter IN VARCHAR2)
RETURN ty_str_split IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2(4000);
str_split ty_str_split := ty_str_split();
BEGIN
len := length(p_str);
len1 := length(p_delimiter);
WHILE j < len LOOP
j := instr(p_str,p_delimiter,i);
IF j = 0 THEN
j := len;
str := substr(p_str,i);
str_split.EXTEND;
str_split(str_split.COUNT) := str;
IF i >= len THEN
EXIT;
END IF;
ELSE
str := substr(p_str,j - i);
i := j + len1;
str_split.EXTEND;
str_split(str_split.COUNT) := str;
END IF;
END LOOP;
RETURN str_split;
END fn_split;
?
SELECT GJH.JE_SOURCE SOURCE,GJH.NAME JOURNAL_NAME,GJL.DESCRIPTION
? FROM GL.GL_JE_LINES GJL,GL.GL_JE_HEADERS GJH
?WHERE GJH.SET_OF_BOOKS_ID = '1'
?? AND GJH.PERIOD_NAME IN ('OCT-09')
?? AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
?? AND GJH.NAME LIKE 'Billable fence journal%'
?ORDER BY GJH.DOC_SEQUENCE_VALUE
-- FA category list
SELECT CB.BOOK_TYPE_CODE BOOK,
?????? CAT.SEGMENT1 MAJOR,
?????? CAT.SEGMENT2 MINOR,
?????? CAT.SEGMENT3 SUB,
?????? CAT.SEGMENT4 CAP,
?????? CAT.SEGMENT1 || '.' || CAT.SEGMENT2 || '.' || CAT.SEGMENT3 || '.' ||
?????? CAT.SEGMENT4 Categroy_Comb,
?????? CBD.DEPRN_METHOD METHOD,
?????? CBD.LIFE_IN_MONTHS LIFE_IN_MONTHS,
??????
?????? --CB.ASSET_COST_ACCT A_ACCOUNT,
?????? GE_FA.GET_GL_CODE_COMBINATION(CB.ASSET_COST_ACCOUNT_CCID) COST_ACCOUNT,
?????? --CB.DEPRN_RESERVE_ACCT R_ACCOUNT,
?????? GE_FA.GET_GL_CODE_COMBINATION(CB.RESERVE_ACCOUNT_CCID) RESERVE_ACCOUNT,
?????? GE_FA.GET_GL_CODE_COMBINATION(CB.ASSET_CLEARING_ACCOUNT_CCID) CLEARING_ACCOUNT,
???????
?
??????
?????? GE_FA.GET_GL_CODE_COMBINATION(CB.wip_cost_account_ccid) CIP_Cost_ACCOUNT,
?????? GE_FA.GET_GL_CODE_COMBINATION(CB.wip_clearing_account_ccid) CIP_CLearing_ACCOUNT,
??????
?????? CB.DEPRN_EXPENSE_ACCT EXPENSE_ACCOUNT,??
????? /* CBD.PRODUCTION_CAPACITY PROD,
?????? CBD.BASIC_RATE BASIC_RATE,
?????? CBD.ADJUSTED_RATE ADJ_RATE,*/
?????? CBD.PRORATE_CONVENTION_CODE CONVENTION,
?????? CBD.RETIREMENT_PRORATE_CONVENTION RETIRE_CONV/*,
?????? DECODE(CBD.USE_ITC_CEILINGS_FLAG,
????????????? 'NO',
????????????? DECODE(CBD.CEILING_NAME,NULL,'NO','YES'),
????????????? 'YES') CEIL*//*,
?????? CBD.PRICE_INDEX_NAME PRICE_INDEX*/ /*,
????????????????????????????????????????????????????? validat_ccid(cb.category_id,p_book_type_code,P_LEGAL_ENTITY) validate*/
? FROM FA_CATEGORIES???????????? CAT,
?????? FA_SYSTEM_CONTROLS??????? SC,
?????? FA_CATEGORY_BOOKS???????? CB,
?????? FA_CATEGORY_BOOK_DEFAULTS CBD
?????? --- FA_CATEGORY_BOOKS
?WHERE CBD.CATEGORY_ID = CB.CATEGORY_ID
?? AND CBD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
?? AND CAT.CATEGORY_ID = CB.CATEGORY_ID
?? AND CB.BOOK_TYPE_CODE IN ('GECO_CNY_GEGAAP','GECO_CNY_TAX','GECO_USD_GEGAAP')
?? and CAT.SEGMENT1 =upper('Building')
?? and CAT.SEGMENT2 ='207-4'
?? and CAT.SEGMENT3 in ('S25','S26')
?? and CBD.creation_date >sysdate -0.2
??? ---= P_BOOK_TYPE_CODE
????? --AND (P_LEGAL_ENTITY is NULL or CAT.SEGMENT3 = P_LEGAL_ENTITY)
? /* AND (P_LEGAL_ENTITY IS NULL OR
?????? VALIDAT_CCID(CB.CATEGORY_ID,P_BOOK_TYPE_CODE,P_LEGAL_ENTITY) = 1)*/
?ORDER BY CB.BOOK_TYPE_CODE,Categroy_Comb;
---find locke
select t2.username,
?????? t2.sid,
?????? t2.serial#,
?????? t2.action,
?????? t2.logon_time,
?????? t1.OBJECT_ID,
?????? obj.OBJECT_NAME,
?????? t1.LOCKED_MODE
? from v$locked_object t1,v$session t2,all_objects obj
?where t1.object_id = obj.object_id
?? and t1.session_id = t2.sid
?order by t2.logon_time;
select * from AP_BANK_BRANCHES where bank_name ='北京招商银行双榆树支行' ---bank_branch_id =14088
select * from ap_bank_accounts_all where bank_branch_id =14088 -- bank_account_id =20215
select * from ap_bank_account_uses_all where external_bank_account_id =20215? --vendor_id = 8013
select * from po_vendors where vendor_id = 8013? ---vendor_site_id = 9894
select * from po_vendor_sites_all where vendor_site_id= 9894
--modify city code
CREATE TABLE AP_INVOICE_DIS_A_100430001
AS SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL
where PERIOD_NAME = 'APR-10'
AND SET_OF_BOOKS_ID = '1'
AND INVOICE_ID ( '245021','245023');
--S25.140200000.00.00.00.00.00
--S25.140200000.00.0021.00.00.00
UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
SET DIST_CODE_COMBINATION_ID = '102993'
WHERE period_name = 'APR-10'
AND set_of_books_id = '1'
AND DIST_CODE_COMBINATION_ID = '1004'
AND INVOICE_ID ( '245021','245023');
To implement the solution,please execute the following steps:
1) Check status of corporate book and related tax books to find which all have status of 'S'.
2) Run following update scripts in tax book(s) for which status in 'S'.
update fa_book_controls
set deprn_status = 'E',
deprn_request_id = NULL,
mass_request_id = NULL
where book_type_code = '&book'
Then run depreciation for that tax book(s) without closing period and then rollback it.
3) Then run same update scripts for Corp book.
For example:
update fa_book_controls
set deprn_status = 'E',
mass_request_id = NULL
where book_type_code = '&book'
Then you should be able to run depreciation for the Corp book.
创建DBLINK 有两种方法
?
create database link DEV2_TO_UAT
? connect to apps identified by uat34app? using '(DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = 3.242.188.32)(PORT = 1528))
??? )
??? (CONNECT_DATA =
????? (SERVICE_NAME = UAT)
??? )
? )';
一 :?? 动态DB LINK
?-- Create database link
create public database link MYLINK2
? connect to USERID
? using '(DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = DB2)(PORT = 1521))
??? )
??? (CONNECT_DATA =
????? (SERVICE_NAME = DS2)
??? )
? )';
?
说是动态并不是真正动态的,只是这种方式可以随时建立,适合程序临时使用.
?
?
二:? SERVER TO SERVER DB-LINK
-- Create database link
create public database link MYLINK
? connect to USERID
? using 'DB2.TNSNAME';
?
该方法需要在当前的SERVER上建立名为 'DB2.TNSNAME' 的TNS .
注意是在SERVER上建TNS,不是CLIENT. 很多人建了DB-LINK 就会报错
ORA-12154,就是这个原因.
?
?
建好后可以访问远端数据DB2.
?
select * from TABLE1@MYLINK2? where comFORT='1'
?
也可以继续建立synonym (同义词)
-- Create the synonym
create or replace public synonym r_db2
? for sys.table1@MYLINK2;
?
那么就可以用
select * from r_db2? where comFORT='1'
select decode(a.set_of_books_id,
????????????? '1',
????????????? 'Korea',
????????????? '2',
????????????? '3',
????????????? '4',
????????????? 'Vietnam') Country,
?????? a.invoice_num "Invoice number",
?????? a.invoice_date "Invoice date",
?????? (select b.segment1 from po_vendors b where b.vendor_id = a.vendor_id) "Vendor Code",
?????? (select b.vendor_name
????????? from po_vendors b
???????? where b.vendor_id = a.vendor_id) "Vendor Name",
?????? (select nvl(b.creation_date,b.last_update_date)
????????? from po_vendors b
???????? where b.vendor_id = a.vendor_id) "Vendor creation date",
?????? (select name from AP_TERMS where term_id = a.terms_id) "Payment Term",
?????? null "ME code",
?????? (select segment1
????????? from gl_code_combinations gc
???????? where gc.code_combination_id = b.dist_code_combination_id) "LE code",
?????? (select segment2
????????? from gl_code_combinations gc
???????? where gc.code_combination_id = b.dist_code_combination_id) account,
?????? (select FFVV.DESCRIPTION
????????? FROM APPS.FND_FLEX_VALUES_VL FFVV,APPS.FND_FLEX_VALUE_SETS FFVS
???????? WHERE FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
?????????? AND FFVS.FLEX_VALUE_SET_NAME = (case
???????????????? when a.set_of_books_id = 8 then
????????????????? 'GEII_VN_ACCOUNT'
???????????????? when a.set_of_books_id = 7 then
????????????????? 'VietAccount'
???????????????? when a.set_of_books_id = 5 then
????????????????? 'Account'
???????????????? when a.set_of_books_id = 6 then
????????????????? 'Account'
???????????????? else
????????????????? 'KR ACCOUNT'
?????????????? end)
?????????? and enabled_flag = 'Y'
?????????? and FFVV.FLEX_VALUE =
?????????????? (select segment2
????????????????? from gl_code_combinations gc
???????????????? where gc.code_combination_id = b.dist_code_combination_id)) "CCL Account Description",
?????? a.invoice_currency_code "Invoice currency",
?????? sum(b.amount) "Invoice amount(Invoice Curr)",
??????
?????? sum(b.amount) * (case
????????????????????????????????? when a.invoice_currency_code = 'USD' then
?????????????????????????????????? 1
????????????????????????????????? else
?????????????????????????????????? (select conversion_rate
????????????????????????????????????? from GL_DAILY_RATES_V rate
???????????????????????????????????? where from_currency = a.invoice_currency_code
?????????????????????????????????????? and to_currency = 'USD'
?????????????????????????????????????? and user_conversion_type = 'KR GEII MOR'
?????????????????????????????????????? and to_date(conversion_date,'DD-MM-YY') =
?????????????????????????????????????????? to_date(a.gl_date,'DD-MM-YY'))
??????????????????????????????? end) "Invoice amount(USD)",
?????? b.period_name PERIOD_NAME,
?????? to_char(to_date(a.gl_date,'DD-MM-YY'),'MON') "Posting Month",
?????? (select name
????????? from gl_sets_of_books
???????? where set_of_books_id = a.set_of_books_id) SET_OF_BOOKS_NAME,
???????? decode(nvl(a.cancelled_date,'XX'),'XX','','Cancelled') "Invoice Status"/*,
?????? a.**/
? from ap_invoices_all a,AP_INVOICE_DISTRIBUTIONS_all b
?where a.invoice_id = b.invoice_id
?? and a.set_of_books_id = b.set_of_books_id
?? and a.invoice_date between to_date('2009-01-01','YYYY-MM-DD') and to_date('2010-07-30','YYYY-MM-DD')
?group by a.set_of_books_id,
????????? a.invoice_num,
????????? a.invoice_date,
????????? a.vendor_id,
????????? b.period_name,
????????? a.gl_date,
????????? a.terms_id,
????????? b.dist_code_combination_id,
????????? a.invoice_currency_code,
????????? a.cancelled_date
order by? a.set_of_books_id,? a.invoice_date???
--- update JE Headers
create table GL_JE_HEADERS010723 as
select * from GL_JE_HEADERS
where JE_HEADER_ID = '335821'
?? AND SET_OF_BOOKS_ID = '1';
UPDATE GL_JE_HEADERS
?? SET ACCRUAL_REV_JE_HEADER_ID = '',ACCRUAL_REV_STATUS = ''
?WHERE JE_HEADER_ID = '335821'
?? AND SET_OF_BOOKS_ID = '1';
upload categories by DB
--step1 check account_combination
select cost_account_cob,clear_account_cob,
accumulated_deprn_cob,a.* from setup_categories_all a
where not exists ( select 'challenge' from gl_code_combinations_kfv where concatenated_segments= a.accumulated_deprn_cob)
create table SETUP_CATEGORIES_ALL
(
? CODE_COMBINATION????? VARCHAR2(84),
? BOOK_NAME???????????? VARCHAR2(30),
? DEPRN_METHOD????????? VARCHAR2(20),
? DEPRN_LIFE??????????? NUMBER,
? DEPRN_PRORATION?????? VARCHAR2(20),
? RETIREMENT_PRORATION? VARCHAR2(20),
? COST_ACCOUNT????????? VARCHAR2(20),
? CLEAR_ACCOUNT???????? VARCHAR2(20),
? COST_ACCOUNT_COB????? VARCHAR2(200),
? CLEAR_ACCOUNT_COB???? VARCHAR2(200),
? EXPENSE_ACCOUNT?????? VARCHAR2(20),
? ACCUMULATED_DEPRN???? VARCHAR2(20),
? ACCUMULATED_DEPRN_COB VARCHAR2(200),
? CIP_COST_ACCOUNT????? VARCHAR2(20),
? CIP_CLEAR_ACCOUNT???? VARCHAR2(20),
? CIP_COST_ACCOUNT_COB? VARCHAR2(200),
? CIP_CLEAR_ACCOUNT_COB VARCHAR2(200),
? MAJOR???????????????? VARCHAR2(20),
? MINOR???????????????? VARCHAR2(20),
? SUB?????????????????? VARCHAR2(20),
? CAP?????????????????? VARCHAR2(20),
? percent_salvage_value number
);
?
select * from FND_ID_FLEX_STRUCTURES_VL? where id_flex_structure_code ='GECI_ACCOUNTING_FLEXFIELD'
-----chart_of_account = 50349
declare
? v_category_id number;
? cursor categories is
??? select * from setup_categories_all a where not exists (select 'challenge tang' from FA_CATEGORIES_B fc,FA_CATEGORY_BOOKS fb
where fc.category_id=fb.category_id
and fc.segment1 = upper(a.major)
and fc.segment2 = upper(a.minor)
and fc.segment3 = upper(a.sub)
and fc.segment4 = upper(a.cap)
and fb.book_type_code = a.book_name ) ;
begin
? for C1 in categories loop
??? begin
????? select category_id
??????? into v_category_id
??????? from FA_CATEGORIES_B
?????? where segment1 = upper(C1.major)
???????? and segment2 = upper(C1.minor)
???????? and segment3 = upper(C1.sub)
???????? and segment4 = upper(C1.cap);
????? dbms_output.put_line(C1.major || '.' || C1.minor || '.' || C1.sub || '.' ||
?????????????????????????? C1.cap || ' Category ID: ' || v_category_id);
??? exception
????? when others then
??????? v_category_id := null;
?????? --- dbms_output.put_line('v_category_id is null');
??? end;
??? if v_category_id is null then
????? select FA_CATEGORIES_B_S.nextval into v_category_id from dual;
????? dbms_output.put_line(C1.major || '.' || C1.minor || '.' || C1.sub || '.' ||
?????????????????????????? C1.cap || ' Category1 ID: ' || v_category_id);
???
????? insert into FA_CATEGORIES_TL
??????? (category_id,
???????? language,
???????? source_lang,
???????? last_update_date,
???????? last_updated_by,
???????? created_by,
???????? creation_date,
???????? last_update_login)
????? values
??????? (v_category_id,'US',sysdate,1763,982535);
????? insert into FA_CATEGORIES_TL
??????? (category_id,'ZHS',982535);
???
????? insert into FA_CATEGORIES_B
??????? (category_id,
???????? summary_flag,
???????? enabled_flag,
???????? owned_leased,
???????? category_type,
???????? capitalize_flag,
???????? segment1,
???????? segment2,
???????? segment3,
???????? segment4,
???????? inventorial,
???????? 'N',
???????? 'Y',
???????? 'OWNED',
???????? SYSDATE,
???????? 1763,
???????? 'NON-LEASE',
???????? 'YES',
???????? upper(C1.major),
???????? upper(C1.minor),
???????? upper(C1.sub),
???????? upper(C1.cap),
???????? 982535);
???
????? insert into FA_CATEGORY_BOOKS
??????? (category_id,
???????? book_type_code,
???????? last_update_login,
???????? asset_clearing_acct,
???????? asset_cost_acct,
???????? cip_clearing_acct,
???????? cip_cost_acct,
???????? deprn_expense_acct,
???????? deprn_reserve_acct,
???????? asset_cost_account_ccid,----
???????? asset_clearing_account_ccid,
???????? wip_cost_account_ccid,
???????? wip_clearing_account_ccid,
???????? reserve_account_ccid,
???????? bonus_deprn_expense_acct,
???????? bonus_deprn_reserve_acct,
???????? bonus_reserve_acct_ccid)
????? values
??????? (v_category_id,
???????? C1.book_name,
???????? sysdate,
???????? 982535,
???????? C1.clear_account,
???????? C1.cost_account,
???????? C1.cip_clear_account,
???????? C1.cip_cost_account,
???????? C1.expense_account,
???????? C1.accumulated_deprn,
???????? (select CODE_COMBINATION_ID
??????????? from gl_code_combinations_kfv
?????????? where chart_of_accounts_id = '&Chart_Of_Account_ID'
???????????? and concatenated_segments = C1.cost_account_cob),
???????? (select CODE_COMBINATION_ID
??????????? from gl_code_combinations_kfv
?????????? where chart_of_accounts_id = '&Chart_Of_Account_ID'
???????????? and concatenated_segments = C1.clear_account_cob),
???????? (select CODE_COMBINATION_ID
??????????? from gl_code_combinations_kfv
?????????? where chart_of_accounts_id = '&Chart_Of_Account_ID'
???????????? and concatenated_segments = C1.cip_cost_account_cob),
???????? (select CODE_COMBINATION_ID
??????????? from gl_code_combinations_kfv
?????????? where chart_of_accounts_id = '&Chart_Of_Account_ID'
???????????? and concatenated_segments = C1.cip_clear_account_cob),
???????? (select CODE_COMBINATION_ID
??????????? from gl_code_combinations_kfv
?????????? where chart_of_accounts_id = '&Chart_Of_Account_ID'
???????????? and concatenated_segments = C1.accumulated_deprn_cob),
???????? (select CODE_COMBINATION_ID
??????????? from gl_code_combinations_kfv
?????????? where chart_of_accounts_id = '&Chart_Of_Account_ID'
???????????? and concatenated_segments = C1.accumulated_deprn_cob));
???
????? insert into FA_CATEGORY_BOOK_DEFAULTS
??????? (category_id,
???????? start_dpis,
???????? life_in_months,
???????? deprn_method,
???????? prorate_convention_code,
???????? itc_eligible_flag,
???????? use_itc_ceilings_flag,
???????? depreciate_flag,
???????? retirement_prorate_convention,
???????? use_stl_retirements_flag,
???????? capital_gain_threshold,
???????? use_deprn_limits_flag,
???????? percent_salvage_value)
????? values
??????? (v_category_id,
???????? to_date('1970/01/01','YYYY/MM/DD'),
???????? C1.deprn_life * 12,
???????? C1.deprn_method,
???????? C1.deprn_proration,
???????? 'NO',
???????? C1.retirement_proration,
???????? 12,
???????? C1.percent_salvage_value);
???
??? elsif v_category_id is not null then
????? dbms_output.put_line('v_category_id: ' || v_category_id);
????? insert into FA_CATEGORY_BOOKS
??????? (category_id,
???????? asset_clearing_account_ccid,
???????? C1.percent_salvage_value);
?/*?? dbms_output.put_line(''in);*/
??? end if;
? end loop;
end;
----查找最近提交的program.
SELECT distinct? b.RESPONSIBILITY_NAME,c.user_concurrent_program_name ---program_name,a.*
?FROM FND_CONCURRENT_REQUESTS a,FND_RESPONSIBILITY_VL b,FND_CONCURRENT_PROGRAMS_TL c?
?where a.concurrent_program_id = c.concurrent_program_id
?--AND a.responsibility_application_id= b.APPLICATION_ID
?and a.RESPONSIBILITY_ID =b.RESPONSIBILITY_ID
?---and a.responsibility_application_id=c.application_id
?and c.language='US'
?and a.request_date > sysdate -730
select decode(a.set_of_books_id,'4','GECN_R_SOB','1','GECN_SOB') SOB,?????? a.Period_name,b.segment3 Cost_Center,Count(a.je_line_num) Line_Num?from gl.gl_je_lines a,gl.gl_code_combinations b,ofc.ge_china_cost_center cwhere (a.set_of_books_id = 1 or a.set_of_books_id=4)and a.code_combination_id=b.code_combination_idand c.company='S25'and b.segment3 = c.cost_centerand (c.udc7='01' or c.udc7='02')and a.effective_date between to_date('01-01-09','DD-MM-YY') and to_date('20-07-10','DD-MM-YY')group by a.set_of_books_id,a.Period_name,b.segment3order by a.set_of_books_id,b.segment3
