SLA — Запрос для просмотра данных учета (Subledger Accounting)

13 Апрель 2012 Нет комментариев

Запрос для просмотра данных учета

select xte.application_id        as appl_id
      ,xte.entity_code           as entity_code
      ,ec.event_class_code       as event_class_code
      ,ec.name                   as event_class
      ,xal.code_combination_id   as cc_id     
      ,fnd_flex_ext.get_segs('SQLGL','GL#',gl.chart_of_accounts_id, xal.code_combination_id)      as ACCOUNT
      --,xla_oa_functions_pkg.get_ccid_description(gl.chart_of_accounts_id,xal.code_combination_id) as account_description
      ,xah.ae_header_id          as xah_ae_header_id
      ,xal.ae_line_num           as xal_ae_line_num
      ,xal.ledger_id             as xal_ledger_id
      ,xte.ledger_id             as xte_ledger_id
      ,xae.event_type_code       as event_type_code
      ,et.name                   as event_type
      ,xae.event_number          as event_number
      ,xal.accounting_class_code as acc_class_code   -- класс учета
      ,lk1.meaning               as accounting_class
      ,xal.accounting_date       as accounting_date
      ,xal.accounted_dr          as accounted_dr
      ,xal.accounted_cr          as accounted_cr
      ,xal.currency_code         as currency_code
      ,nvl(xte.source_id_int_1, -99) as source_id_int_1
      ,decode(xal.party_type_code, 'C', xal.party_id)      as customer_id
      ,decode(xal.party_type_code, 'C', xal.party_site_id) as customer_site_id
from xla.xla_transaction_entities  xte
   , xla.xla_ae_headers            xah
   , xla.xla_ae_lines              xal
   , xla.xla_events                xae
   , xla_lookups                   lk1
   , xla_gl_ledgers_v              gl   
   , xla.xla_event_types_tl        et
   , xla.xla_event_classes_tl      ec
where 1=1
      -- sla xah
      and xah.entity_id      = xte.entity_id
      and xah.application_id = xte.application_id
      and xah.ledger_id      = xal.ledger_id
      -- sla xal
      and xal.ae_header_id   = xah.ae_header_id
      and xal.application_id = xah.application_id
      and (nvl(xal.accounted_dr,0) != 0 or nvl(xal.accounted_cr,0)!=0)
      -- sla xae
      and xae.application_id  = xah.application_id
      and xae.event_id        = xah.event_id  
      -- lk1
      and lk1.lookup_code = xal.accounting_class_code
      and lk1.lookup_type = 'XLA_ACCOUNTING_CLASS'
      -- gl
      and gl.ledger_id = xah.ledger_id
      -- et 
      and et.application_id  = xte.application_id
      and et.entity_code     = xte.entity_code      
      and et.event_type_code = xae.event_type_code
      and et.language        = USERENV('LANG')
      -- ec
      and ec.application_id   = xte.application_id
      and ec.entity_code      = xte.entity_code
      and ec.event_class_code = et.event_class_code
      and ec.language         = USERENV('LANG')
      -- ======
      -- and xte.entity_code    = 'RECEIPTS'
      -- and xte.application_id = 222
      -- and xte.ledger_id = fnd_profile.value('GL_SET_OF_BKS_ID')
      -- and nvl(source_id_int_1,(-99)) = 

PL/SQL Developer — отображение чисел (number -> char)

13 Апрель 2012 Нет комментариев

Иногда требуется отследить числа (визуально), с большим количеством знаков после точки.
Но столкнемся с тем, что PL/SQL Developer при отображении обрезает число.

select 1.000000000000001234567 v from dual
union all
select 1.000000100000001234567 from dual
union all
select 1.001000000000001234567 from dual
union all
select 1.123456789123456789123 from dual
Отображение чисел

В PL/SQL Developer есть полезная опция Number fields to_char
Читать дальше про “PL/SQL Developer — отображение чисел (number -> char)” »

Вызов формы FNDRSRUN для запуска канкарента с определенными параметрами

10 Апрель 2012 Нет комментариев

Открытие формы FNDRSRUN с определенным канкарентом и выставленными параметрами:

1) Создаем свою форм функцию XX_FNDRSRUN для доступа к форме FNDRSRUN. (необязательный шаг)

2) Помещаем вызов нашего канкарента в меню Сервис на определенный SPECIAL.

3) Строка для вызова

CONCURRENT_PROGRAM_NAME ="<CODE>"
PROGRAM_APPL_SHORT_NAME="<APPL_CODE>"
CHAR1="<param1:param2:...>"
SUBMIT_ONCE="Y"

В параметр CHAR1 записываем параметры канкарента (value), через двоеточие.

FNDDSRUN Запуск канкарента ОЕБС

Читать дальше про “Вызов формы FNDRSRUN для запуска канкарента с определенными параметрами” »

ОЕБС Расширенный скрипт инициализации сессиии

6 Апрель 2012 3 comments

ОЕБС Инициализация сессии
ОЕБС Расширенный скрипт для инициализации сессии:

  • Выводит в output короткую запись для инициализации.
  • Если не заданы полномочия или приложение, то выводит в output список назначенных пользователю полномочий.
declare
 l_lang           varchar2(2)   := 'RU';
 l_user_name      varchar2(100) := 'USER_NAME';
 l_resp_name      varchar2(400);
 l_resp_appl_name varchar2(400);
 -- ------------------------------------------------------------------
 l_user_id        NUMBER;
 l_resp_id        NUMBER;
 l_resp_appl_id   NUMBER;
 l_appl_code      VARCHAR2(10);
 l_org_id         NUMBER;
 l_mo_sec_profile NUMBER; 
begin
 
 -- l_resp_name := 'Диспетчер Дебиторов';
 -- l_resp_name := 'Разработчик приложений';
 -- l_resp_name := 'Движение денежных средств: суперпользователь';
 
 -- l_resp_appl_name := 'Главная книга';
 -- l_resp_appl_name := 'Дебиторы' 
 -- l_resp_appl_name := 'Кредиторы';
 -- l_resp_appl_name := 'Движение денежных средств';
 -- l_resp_appl_name := 'Библиотека объектов приложений';
 
   select user_id into l_user_id from fnd_user t where user_name like l_user_name; 
 
   if (l_resp_name is null or l_resp_appl_name is null) then
 
     for i in (
                 SELECT ap.application_short_name as appl_code,
                        a.application_name        as appl_name,
                        r.responsibility_name     as resp_name,
                        --
                        max(length(a.application_name )) over() as length_appl_name
                  FROM fnd_user u,
                       fnd_user_resp_groups g,
                       fnd_application_tl a,
                       fnd_responsibility_tl r,
                       fnd_application       ap
                 WHERE g.user_id(+) = u.user_id
                   AND g.responsibility_application_id = a.application_id
                   AND a.application_id = r.application_id
                   AND g.responsibility_id = r.responsibility_id
                   and a.language = l_lang
                   and ap.application_id = a.application_id
                   and r.language = l_lang
                   and u.user_name = l_user_name
                   and r.responsibility_name <> 'Диагностика приложения'
                   and r.responsibility_name = nvl(l_resp_name,r.responsibility_name)
                   and a.application_name = nvl(l_resp_appl_name,a.application_name)
 
                 ORDER BY appl_name, resp_name
               )
     loop
       dbms_output.put_line(rpad('l_resp_appl_name := '''||i.appl_name||'''',i.length_appl_name+22,' ') || ' ; l_resp_name := '''||i.resp_name||''';');
     end loop;
 
   else
 
     select t.responsibility_id into l_resp_id from fnd_responsibility_tl t where t.responsibility_name like l_resp_name and t.language = l_lang;
     
     select b.application_id, b.application_short_name into l_resp_appl_id, l_appl_code 
     from fnd_application_tl t 
         ,fnd_application    b
     where 1=1
           and t.application_name like l_resp_appl_name 
           and t.language = l_lang 
           and b.application_id = t.application_id;
 
     l_org_id := fnd_profile.VALUE_SPECIFIC(NAME => 'ORG_ID', RESPONSIBILITY_ID => l_resp_id, APPLICATION_ID=> l_resp_appl_id);
     
     fnd_global.apps_initialize( user_id      => l_user_id,
                                 resp_id      => l_resp_id,
                                 resp_appl_id => l_resp_appl_id
                               );
     begin
       MO_GLOBAL.init(l_appl_code);
       
       if (l_org_id is not null) then
         mo_global.set_policy_context('M',l_org_id);
       end if;
       
       l_mo_sec_profile := fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
       
       mo_global.set_org_context(l_resp_id,l_mo_sec_profile,l_appl_code);
     exception
       when others then
         dbms_output.put_line('SQLERRM:'||SQLERRM||chr(10));
         dbms_output.put_line('FORMAT_ERROR_BACKTRACE:'||chr(10)||dbms_utility.format_error_backtrace);
     end;

     dbms_output.put_line('begin'||chr(10)||' fnd_global.apps_initialize('||l_user_id||', '||l_resp_id||', '||l_resp_appl_id||'); --'||l_resp_name||' - '||l_resp_appl_name
                          ||chr(10)||' mo_global.init('''|| l_appl_code||''');'
                          ||case when l_org_id is not null then chr(10)||' mo_global.set_policy_context(''M'','||l_org_id||');' end                        
                          ||chr(10)||' mo_global.set_org_context('||l_resp_id||', '||l_mo_sec_profile||', '''||l_appl_code||''');'
                          ||chr(10)||'end;');     
   end if;
end;

Информация о расчетных счетах — поставщик или заказчик

oebs_supplier_customer

Запрос для определения типа расчетного счета:

(поставщик или заказчик)

SELECT account.bank_account_num
      ,account.ext_bank_account_id -- Link to Bank and Branch Information
      ,acc_instr.instrument_id
      ,case when ext_payer.ext_payer_id is not null then 'CUSTOMER'
            when ext_iep.ext_payee_id is not null   then 'SUPPLIER'
       end type_account
      ,case when ext_payer.ext_payer_id is not null then 
         (select ac.CUSTOMER_ID || ' - ' || ac.CUSTOMER_NAME 
          from ar_customers ac where ac.CUSTOMER_ID = ext_payer.cust_account_id)
            when ext_iep.ext_payee_id is not null then
         (select ap.vendor_id || ' - ' || ap.vendor_name 
          from ap_suppliers ap where ap.party_id = ext_iep.payee_party_id) 
       end owner_account_name
  FROM iby_ext_bank_accounts   account
      ,iby_pmt_instr_uses_all  acc_instr
      ,iby_external_payers_all ext_payer
      ,iby_external_payees_all ext_iep
 WHERE 1 = 1
   AND account.ext_bank_account_id = acc_instr.instrument_id
   AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id(+)
   AND acc_instr.ext_pmt_party_id = ext_iep.ext_payee_id(+)
--   AND account.bank_account_num in ('','')

Список расчетных счетов для заказчиков

Для просмотра списка расчетных счетов заказчиков (покупателей, контрагентов) используем следующий запрос:

SELECT ac.CUSTOMER_id
   , cust.party_name customer_name
   , cust_acct.account_number
   , cust_acct.cust_account_id
   , cust_uses.site_use_code
   , cust_loc.address1
   , cust_loc.address2
   , cust_loc.address3
   , cust_loc.address4
   , cust_loc.city
   , cust_loc.postal_code
   , bank.party_name                   bank_name
   , bank_prof.home_country
   , branch.party_name                 branch_name
   , branch_prof.bank_or_branch_number branch_number
   , account.bank_account_num
   , account.bank_account_name
   , acc_instr.order_of_preference
   , cust_uses.primary_flag
FROM hz_parties               bank
   , hz_relationships         rel
   , hz_parties               branch
   , hz_organization_profiles bank_prof
   , hz_organization_profiles branch_prof
   , iby_ext_bank_accounts    account
   , iby_external_payers_all  ext_payer
   , iby_pmt_instr_uses_all   acc_instr
   , hz_parties               cust
   , hz_cust_accounts         cust_acct
   , hz_cust_acct_sites_all   cust_site
   , hz_cust_site_uses_all    cust_uses
   , hz_party_sites           party_site
   , hz_locations             cust_loc
   , ar_customers             ac
WHERE 1=1
AND bank.party_id                    = rel.object_id
and bank.party_type                  = rel.object_type
AND rel.object_table_name            = 'HZ_PARTIES'
AND rel.relationship_code            = 'BRANCH_OF'
AND rel.subject_id                   = branch.party_id
AND rel.subject_type                 = branch.party_type
AND rel.subject_table_name           = 'HZ_PARTIES'
AND bank.party_id                    = bank_prof.party_id
AND branch.party_id                  = branch_prof.party_id
AND cust_acct.cust_account_id        = cust_site.cust_account_id
AND cust_site.cust_acct_site_id      = cust_uses.cust_acct_site_id
and cust_uses.status                 = 'A'
AND party_site.party_id              = cust.party_id
AND party_site.party_site_id         = cust_site.party_site_id
AND party_site.location_id           = cust_loc.location_id
AND cust.party_id                    = cust_acct.party_id
AND bank.party_id                    = account.bank_id
AND branch.party_id                  = account.branch_id
AND account.ext_bank_account_id      = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id       = ext_payer.ext_payer_id
AND ext_payer.cust_account_id        = cust_acct.cust_account_id
AND cust_uses.site_use_id            = ext_payer.acct_site_use_id
and ext_payer.cust_account_id        = ac.CUSTOMER_id

Список расчетных счетов для поставщиков

Для получения списка расчетных счетов поставщиков используем следующий запрос

SELECT party_supp.party_name             supplier_name
      ,aps.segment1                      supplier_number
      ,ass.vendor_site_code              supplier_site
      ,ieb.bank_account_num              bank_account_num
      ,ieb.bank_account_name             bank_account_name
      ,party_bank.party_name             bank_name
      ,branch_prof.bank_or_branch_number bank_number
      ,party_branch.party_name           branch_name
      ,branch_prof.bank_or_branch_number branch_number
  FROM hz_parties               party_supp
      ,ap_suppliers             aps
      ,hz_party_sites           site_supp
      ,ap_supplier_sites_all    ass
      ,iby_external_payees_all  iep
      ,iby_pmt_instr_uses_all   ipi
      ,iby_ext_bank_accounts    ieb
      ,hz_parties               party_bank
      ,hz_parties               party_branch
      ,hz_organization_profiles bank_prof
      ,hz_organization_profiles branch_prof
 WHERE party_supp.party_id = aps.party_id
   AND party_supp.party_id = site_supp.party_id
   AND site_supp.party_site_id = ass.party_site_id
   AND ass.vendor_id = aps.vendor_id
   AND iep.payee_party_id = party_supp.party_id
   AND iep.party_site_id = site_supp.party_site_id
   AND iep.supplier_site_id = ass.vendor_site_id
   AND iep.ext_payee_id = ipi.ext_pmt_party_id
   AND ipi.instrument_id = ieb.ext_bank_account_id
   AND ieb.bank_id = party_bank.party_id
   AND ieb.branch_id = party_branch.party_id
   AND party_branch.party_id = branch_prof.party_id
   AND party_bank.party_id = bank_prof.party_id
 ORDER BY party_supp.party_name, ass.vendor_site_code;

Выборка неотсторнированных журналов

Для выбора неотсторнированных журналов можно использовать следующий запрос:

SELECT  * FROM gl_je_batches        b,
               gl_je_headers        h,
               gl_je_lines          l,
               gl_code_combinations gcc
WHERE h.je_header_id = l.je_header_id
      AND b.je_batch_id  = h.je_batch_id
      AND gcc.code_combination_id = l.code_combination_id
      AND (  --Неотсторнированные журналы
            ( h.accrual_rev_status IS NULL AND
              h.reversed_je_header_id IS NULL
             )
             OR -- сторно на сторно на сторно ...
            ( h.accrual_rev_status IS NULL AND
              h.reversed_je_header_id IS NOT NULL
              AND MOD((SELECT COUNT(*)
                          FROM gl_je_headers jh
                         START WITH jh.je_header_id = h.je_header_id
                       CONNECT BY jh.je_header_id = PRIOR jh.reversed_je_header_id
                        ), 2 ) != 0
             )
          )

Скрипт для создания профиля

Скрипт для создания профиля в OEBS:

DECLARE
  vr_Profile_name VARCHAR2(100) := 'PROFILE_CODE!!';
BEGIN
  fnd_profile_options_pkg.load_row
  (
    x_profile_name              => vr_Profile_name
  , x_owner                     => 'INITIAL SETUP'
  , x_application_short_name    => 'APPL_CODE!!'
  , x_user_profile_option_name  => 'PROFILE_NAME!!'
  , x_description               => 'PROFILE_NAME!!'
  , x_user_changeable_flag      => 'Y'
  , x_user_visible_flag         => 'Y'
  , x_read_allowed_flag         => 'Y'
  , x_write_allowed_flag        => 'Y'
  , x_site_enabled_flag         => 'Y'
  , x_site_update_allowed_flag  => 'Y'
  , x_app_enabled_flag          => 'Y'
  , x_app_update_allowed_flag   => 'Y'
  , x_resp_enabled_flag         => 'Y'
  , x_resp_update_allowed_flag  => 'Y'
  , x_user_enabled_flag         => 'Y'
  , x_user_update_allowed_flag  => 'Y'
  , x_start_date_active         => to_char(sysdate,'yyyy/mm/dd')
  , x_end_date_active           => NULL
  , x_sql_validation            => 'SQL="SELECT LOOKUP_CODE, MEANING \"Включить проверку\"
INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = ''YES_NO''"
COLUMN="\"Включить проверку\"(20)"'
  );
  --
  IF NOT 
    fnd_profile.save
    (
      x_name           => vr_Profile_name
    , x_value          => 'Y'
    , x_level_name     => 'SITE' 
    )
  THEN
    DBMS_OUTPUT.Put_Line('Error in setting value');
  END IF;
  COMMIT;
END;

Форма FNDRSRUN («Вид -> Запросы») — отображение конкретного запроса

16 Март 2012 14 comments

Для отображении формы FNDRSRUN («Вид -> Запросы») с конкретным запросом, используем полезный параметр DODT_REQ_ID.

FNDDSRUN
      fnd_function.execute
      (
        function_name => 'FND_FNDRSRUN'
      , open_flag     => 'Y'
      , session_flag  => 'Y'
      , other_params  => 'MODE="VIEW" DODT_REQ_ID="' || to_char(l_request_id) || '"'
      );