个人整理

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, ADJ-09, 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, 5, 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,
       FFVV.DESCRIPTION,
       (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",
       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, 5, 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 = '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, 5, 1),
                 'A',
                 'Asset',
                 'E',
                 'Expense',
                 'L',
                 'Liability',
                 'R',
                 'Revenue',
                 'O',
                 'Ownership/Stockholder Equity',
                 SUBSTR(FFVV.COMPILED_VALUE_ATTRIBUTES, 5, 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', '760479', '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,
 apps.GE_GET_GL_SEGMENT(BC.CODE_COMBINATION_ID, '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_code_combinations gc,
       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'),
          apps.GE_GET_GL_SEGMENT(BC.CODE_COMBINATION_ID, '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, i, 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',
deprn_request_id = NULL,
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',
              'Korea',
              '3',
              'Korea',
              '4',
              'Korea',
              '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', 'US', sysdate, 1763, 1763, sysdate, 982535);
      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, 'ZHS', 'US', sysdate, 1763, 1763, sysdate, 982535);
   
      insert into FA_CATEGORIES_B
        (category_id,
         summary_flag,
         enabled_flag,
         owned_leased,
         last_update_date,
         last_updated_by,
         category_type,
         capitalize_flag,
         segment1,
         segment2,
         segment3,
         segment4,
         inventorial,
         created_by,
         creation_date,
         last_update_login)
      values
        (v_category_id,
         'N',
         'Y',
         'OWNED',
         SYSDATE,
         1763,
         'NON-LEASE',
         'YES',
         upper(C1.major),
         upper(C1.minor),
         upper(C1.sub),
         upper(C1.cap),
         'YES',
         1763,
         SYSDATE,
         982535);
   
      insert into FA_CATEGORY_BOOKS
        (category_id,
         book_type_code,
         last_update_date,
         last_updated_by,
         created_by,
         creation_date,
         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,
         1763,
         1763,
         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),
         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.accumulated_deprn_cob));
   
      insert into FA_CATEGORY_BOOK_DEFAULTS
        (category_id,
         book_type_code,
         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,
         last_update_date,
         last_updated_by,
         created_by,
         creation_date,
         last_update_login,
         capital_gain_threshold,
         use_deprn_limits_flag,
         percent_salvage_value)
      values
        (v_category_id,
         C1.book_name,
         to_date('1970/01/01', 'YYYY/MM/DD'),
         C1.deprn_life * 12,
         C1.deprn_method,
         C1.deprn_proration,
         'NO',
         'NO',
         'YES',
         C1.retirement_proration,
         'NO',
         sysdate,
         1763,
         1763,
         sysdate,
         982535,
         12,
         'NO',
         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,
         book_type_code,
         last_update_date,
         last_updated_by,
         created_by,
         creation_date,
         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,
         1763,
         1763,
         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),
         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.accumulated_deprn_cob));
   
      insert into FA_CATEGORY_BOOK_DEFAULTS
        (category_id,
         book_type_code,
         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,
         last_update_date,
         last_updated_by,
         created_by,
         creation_date,
         last_update_login,
         capital_gain_threshold,
         use_deprn_limits_flag,
         percent_salvage_value)
      values
        (v_category_id,
         C1.book_name,
         to_date('1970/01/01', 'YYYY/MM/DD'),
         C1.deprn_life * 12,
         C1.deprn_method,
         C1.deprn_proration,
         'NO',
         'NO',
         'YES',
         C1.retirement_proration,
         'NO',
         sysdate,
         1763,
         1763,
         sysdate,
         982535,
         12,
         'NO',
         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 c
where (a.set_of_books_id = 1 or a.set_of_books_id=4)
and a.code_combination_id=b.code_combination_id
and c.company='S25'
and b.segment3 = c.cost_center
and (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.segment3
order by a.set_of_books_id,a.Period_name,b.segment3