Baza Wiedzy o technologiach Oracle i MS

API Oe_Fnd_Attachments_Pub – do dodawania kopiowania załączników

Opublikowano 01/15/2016, autor: Luko

API Oe_Fnd_Attachments_Pub służy do tworzenia i dodawania załączników.

1. Dodanie załącznika do formatki.
– Oe_Fnd_Attachments_Pub.Create_Short_Text_Document – utworzenie złącznika
– OE_FND_ATTACHMENTS_PUB.Add_Attachment – dodanie załącznika do formatki

2. Kopiowanie załączników z formatki na inna formatkę.
– pseudo kod

    l_OEXOEORD   VARCHAR2(100):='OE_ORDER_HEADERS';
    l_ARXTWMAI   VARCHAR2(100):='RA_CUSTOMER_TRX';
    v_tab oe_fnd_attachments_pub.Copy_Attachments_Tbl_Type;
FOR rec IN get_invoices(l_request_id)
    LOOP
      v_tab(1).from_entity_name:=l_OEXOEORD;
      v_tab(1).from_pk1_value  :=rec.header_id;
      v_tab(1).to_entity_name  :=l_ARXTWMAI;
      v_tab(1).to_pk1_value    :=rec.customer_trx_id;
      Oe_Fnd_Attachments_Pub.Copy_Attachments( p_api_version => 1.0, p_copy_attachments_tbl =>v_tab );
      COMMIT;
    END LOOP;

Zaszufladkowano do kategorii ADM, API, PLSQL | Dodaj komentarz

Tabele powiązane z zleceniami współbieżnymi (CM)

Opublikowano 01/15/2016, autor: Luko

Concurrent Request Log:

SELECT logfile_name FROM fnd_concurrent_requests WHERE request_id = <request_id>;
</request_id>

Concurrent Output file:

SELECT outfile_name FROM fnd_concurrent_requests WHERE request_id = <request_id>;
</request_id>

Concurrent Manager Worker Log:

SELECT logfile_name FROM fnd_concurrent_processes WHERE concurrent_process_id=(SELECT controlling_manager
FROM fnd_concurrent_requests WHERE request_id=<reqid>);
</reqid>

Which Manager ran the request?

SELECT concurrent_queue_name, user_concurrent_queue_name FROM fnd_concurrent_queues_tl
WHERE concurrent_queue_id = (SELECT concurrent_queue_id FROM fnd_concurrent_processes
WHERE concurrent_process_id=(SELECT controlling_manager FROM fnd_concurrent_requests WHERE request_id= <request id>));
</request>

XML publisher output (example: pdf)

SELECT * FROM  fnd_conc_req_outputs

OS output.

$APPLCSF/$APPLOUT/{REPORTNAME}_{REQUEST_ID}_{COUNT}.PDF|RTF|EXCEL|HTML

Zaszufladkowano do kategorii ADM | Dodaj komentarz

SQL – znaki specjalne w bazie danych

Opublikowano 12/11/2015, autor: Luko

SQL do wyszukania znaków specjalnych.

SELECT LEVEL,CHR(LEVEL),
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:graph:]]'), 1) is_graph,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:blank:]]'), 1) is_blank,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:alnum:]]'), 1) is_alnum,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:alpha:]]'), 1) is_alpha,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:digit:]]'), 1) is_digit,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:cntrl:]]'), 1) is_cntrl,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:lower:]]'), 1) is_lower,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:upper:]]'), 1) is_upper,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:print:]]'), 1) is_print,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:punct:]]'), 1) is_punct,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:space:]]'), 1) is_space,
       DECODE(CHR(LEVEL), regexp_substr(CHR(LEVEL), '[[:xdigit:]]'), 1) is_xdigit
  FROM dual
CONNECT BY LEVEL < = 300

Zaszufladkowano do kategorii SQL | Dodaj komentarz

PL/SQL Standards

Opublikowano 12/11/2015, autor: Luko

Standardy PL/SQL, nazewnictwo zmiennych itp
https://www.toadworld.com/platforms/oracle/w/wiki/8245.plsql-standards

LukoNaming Conventions and Coding Standards

Zaszufladkowano do kategorii PLSQL | Dodaj komentarz

Jak włączyć DBMS_OUTPUT w SQL Developerze

Opublikowano 10/15/2015, autor: Luko

Domyślnie Dbms_Output.Put_Line(sysdate); nie działa w SQL Developerze. Trzeba go włączyć komendą SET SERVEROUTPUT ON

SET SERVEROUTPUT ON
 
BEGIN
 DBMS_OUTPUT.Put_Line(SYSDATE);
END;

Zaszufladkowano do kategorii SQL Developer | Otagowano sql developer | Jeden komentarz

Ustawienie / zmiana kontekstu aplikacji w PL/SQL

Opublikowano 09/16/2015, autor: malin

SELECT USER_ID,
  RESPONSIBILITY_ID,
  RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS
WHERE USER_ID =
  (SELECT USER_ID FROM FND_USER WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID =
  (SELECT RESPONSIBILITY_ID
  FROM FND_RESPONSIBILITY_VL
  WHERE RESPONSIBILITY_NAME = '&resp_name'
  );
 
-- Jeżeli chcemy dynamicznie pobrać idki w zleceniu współbieżnym
FND_GLOBAL.USER_ID
FND_GLOBAL.RESP_ID
FND_GLOBAL.RESP_APPL_ID
--poniższe wywołanie zlecenie samo wywołuje
 
FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID,FND_GLOBAL.RESP_ID,FND_GLOBAL.RESP_APPL_ID); 
 
--wykonanie
PROMPT Initialize context OF profiles, etc.
PROMPT Note you can query the USER id, responsibility id, 
PROMPT AND application id FROM the FND tables.
EXECUTE fnd_global.APPS_INITIALIZE(UserID,ResponsibilityID,ApplicationID);

Zaszufladkowano do kategorii _EBS - ogólnie, PLSQL, SQL-ki | Dodaj komentarz

SQL – listagg – wyświetlenie wyników w jednej kolumnie

Opublikowano 09/01/2015, autor: Luko

Witam,

Jak wyświetlić wszystkie opisy danej faktury w jednej kolumnie?

select listagg(description, ' ,') WITHIN GROUP(order by description)
          from AP_INVOICE_LINES_V ail
         where ail.invoice_id = ai.invoice_id
           and ail.line_type_lookup_code = 'ITEM'

Jeżeli chcemy zrobić distinct musi użyć rexexpa

REGEXP_REPLACE(
listagg((FND_NUMBER.CANONICAL_TO_NUMBER(rctl.ATTRIBUTE14) * 100), '% ,’) WITHIN GROUP(order by rctl_tax.TAX_RATE)||’% ’
,'([^,]+)(,\1)+’, '\1′)

Luko

Zaszufladkowano do kategorii SQL | Otagowano sql | Dodaj komentarz

Jak dodać autoryzację User Management

Opublikowano 06/22/2015, autor: malin

How to to Define an Application User That Has All The Privileges in User Management Responsibility options As SYSADMIN User (Doc ID 378262.1)

1. Log into the applications As SYSADMIN

2. Choose User Management responsibility.

3. Navigate to Users web page.

4. Search and find the user one wants to inherit the Security Administrator Role.

5. Click on Update Icon.

6. Click on Assign Roles button.

7. Find and choose ’Security Administrator’ Role.

8. Apply.

9. Log as the user one has assigned 'Security Administrator’ Role.
Does the user now have the User Management Responsibility?
Can the user now search all Users?

Zaszufladkowano do kategorii _EBS - ogólnie | Otagowano sysadmin, user management | Dodaj komentarz