[EBS]Sales Order Join Delivery

 

SELECT

OOH.ORG_ID,

OOH.ORDER_NUMBER,

OOH.ORDERED_DATE,

OOH.ORDER_TYPE_ID,

OOL.LINE_ID "ORDER_LINE_ID",

OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER "ORDER_LINE",

OOL.INVENTORY_ITEM_ID "ITEM_ID",

OOL.ORDERED_ITEM "ITEM_NUMBER",

OOL.ORDERED_QUANTITY "QUANTITY",

OOL.ORDER_QUANTITY_UOM "UOM",

OOL.UNIT_SELLING_PRICE "UNIT_PRICE",

OOH.TRANSACTIONAL_CURR_CODE "CURRENCY",

trunc(OOL.SCHEDULE_SHIP_DATE) "DEMAND_DATE",

OOH.SOLD_TO_ORG_ID "CUSTOMER_ID",

OOH.SHIP_TO_ORG_ID,

OOH.INVOICE_TO_ORG_ID,

WND.name

FROM

APPS.OE_ORDER_HEADERS_ALL OOH

INNER JOIN APPS.OE_ORDER_LINES_ALL OOL ON

OOL.HEADER_ID = OOH.HEADER_ID

INNER JOIN wsh_delivery_details WDD ON

OOL.header_id=WDD.source_header_id

AND OOL.line_id=WDD.source_line_id

INNER JOIN wsh_delivery_assignments WDA ON

WDD.delivery_detail_id=WDA.delivery_detail_id

INNER JOIN wsh_new_deliveries WND ON

WND.delivery_id = WDA.delivery_id

WHERE

1 = 1

AND OOH.order_number = '12345'

AND WND.name='DD102201130'

;

[EBS]Delivery Status

 https://blog.51cto.com/baser/2059271

[Oracle]DB使用狀況

--查看Table硬碟使用狀況

 select segment_name,segment_type, sum(bytes/1024/1024/1024) GB

from dba_segments

 --where segment_name='&Your_Table_Name' 

group by segment_name,segment_type; 

--查看db 空間使用狀況

select

"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)", ("Reserved_Space(MB)" - "Free_Space(MB)")/"Reserved_Space(MB)"*100||'%' "Used Percent"

from(

select

(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",

(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"

from dual );

[EBS]刪除已建立的Concurrent

無法從前端刪除, 但可執行以下Script刪除

 BEGIN

fnd_program.delete_program('short_name','application name');

fnd_program.delete_executable('short_name','application name');

COMMIT;

END;

 

SELECT fa.application_id           "Application ID",

       fat.application_name        "Application Name",

       fa.application_short_name   "Application Short Name"

  FROM fnd_application fa,

       fnd_application_tl  fat

 WHERE fa.application_id = fat.application_id

   AND fat.language      = USERENV('LANG')

   AND fat.application_name = 'Order Management';

[Oracle]指定sequence為欄位identify

Altering an IDENTITY Column

參考:Oracle 如何做到 SQL Server 的 Identity 欄位型態 - Yowko's Notes

         https://www.oracletutorial.com/oracle-basics/oracle-identity-column/

Oracle 12c introduced a new way that allows you to define an identity column for a table, which is similar to the AUTO_INCREMENT column in MySQL or IDENTITY column in SQL Server.

**Oracle原本無identity用法

--1. 先移除原本存在的IDENTITY

ALTER TABLE test_tb (MODIFY pk_id DROP IDENTITY);

--2. 指定sequence給pk_id 

alter table test_tb modify pk_id default on null test_tb_seq .nextval;

-- CREATE SEQUENCE

CREATE SEQUENCE test_tb_seq 

    START WITH 1

    INCREMENT BY 1

    MAXVALUE 9999999999999999999999999999

    CACHE 20

    CYCLE

[EBS]OM 銷售到出倉所經歷的表

 https://www.twblogs.net/a/5b8cf6cd2b7177188337aec1

[EBS]查看某個Request的Output File

 

Oracle EBS標準功能中request執行完後只能看到自己執行的結果(view output), 

系統人員有時要幫忙查看問題就顯得有些麻煩...

2023更新: Responsibility>Application Developer > Concurrent > Requests > 可以Find其他人執行的結果

查看有個function可以取到request的output, 取得EBS report網址:

SELECT fnd_webfile.get_url(
                           file_type   => 4,                --輸出類行
                           id          => 60240818,         --Request_id
                           gwyuid      => '',               --不使用 (環境參數)
                           two_task    => '',               --不使用 (Two Task)
                           expire_time => 10                --URL保留分鐘數
                          ) url
  FROM dual;

/* Define file types for get_url */

process_log constant number := 1;

icm_log constant number := 2;

request_log constant number := 3;

request_out constant number := 4;

request_mgr constant number := 5;

frd_log constant number := 6;

generic_log constant number := 7;

generic_trc constant number := 8;

generic_ora constant number := 9;

generic_cfg constant number := 10;

context_file constant number := 11;

generic_text constant number := 12;

generic_binary constant number := 13;

request_xml_output constant number :=14;

DECLARE

   l_request_id   NUMBER := :P_REQ_ID;                       -- The request id

   l_two_task     VARCHAR2 (256);

   l_gwyuid       VARCHAR2 (256);

   l_url          VARCHAR2 (1024);

BEGIN

   -- Get the value of the profile option named, Gateway User ID (GWYUID)

   --- l_gwyuid := fnd_profile.VALUE ('APPLSYSPUB/PUB');

   SELECT   profile_option_value

     INTO   l_gwyuid

     FROM   fnd_profile_options o, fnd_profile_option_values ov

    WHERE       profile_option_name = 'GWYUID'

            AND o.application_id = ov.application_id

            AND o.profile_option_id = ov.profile_option_id;

   -- Get the value of the profile option named, Two Task(TWO_TASK)

   SELECT   profile_option_value

     INTO   l_two_task

     FROM   fnd_profile_options o, fnd_profile_option_values ov

    WHERE       profile_option_name = 'TWO_TASK'

            AND o.application_id = ov.application_id

            AND o.profile_option_id = ov.profile_option_id;

   l_url :=

      fnd_webfile.get_url (file_type     => fnd_webfile.request_out, -- for out file

                           ID            => l_request_id,

                           gwyuid        => l_gwyuid,

                           two_task      => l_two_task,

                           expire_time   => 500-- minutes, security!.

                           );

   DBMS_OUTPUT.put_line (l_url);

END;