oracle erp

1.小巨蛋外拍9:00 集合 黃元昱老師

select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME, decode(v.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', 10005, 'Server', 'UnDef') LEVEL_SET, decode(to_char(v.level_id), '10001', '', '10002', app.application_short_name, '10003', rsp.responsibility_key, '10005', svr.node_name, '10006', org.name, '10004', usr.user_name, 'UnDef') "CONTEXT", v.profile_option_value VALUE from fnd_profile_options p, fnd_profile_option_values v, fnd_profile_options_tl n, fnd_user usr, fnd_application app, fnd_responsibility rsp, fnd_nodes svr, hr_operating_units org where p.profile_option_id = v.profile_option_id (+) and p.profile_option_name = n.profile_option_name and usr.user_id (+) = v.level_value and rsp.application_id (+) = v.level_value_application_id and rsp.responsibility_id (+) = v.leve...

SELECT   (SELECT COUNT (1)             FROM v$session a2            WHERE a2.blocking_session IS NOT NULL              AND a2.SID = a1.blocking_session) r_count,          a1.blocking_session,          (SELECT a2.serial#             FROM v$session a2            WHERE a2.SID = a1.blocking_session) blocking_serial, a1.SID,          a1.serial#, a1.wait_class, a1.module, a1.seconds_in_wait, a1.action     FROM v$session a1    WHERE a1.blocking_session IS NOT NULL AND a1.blocking_session <> 1693 ORDER BY 1;  

Note :  GL: Setup / Open/Close AP: Accounting / Control Payables Periods AR: Control / Accounting / Open/Close Periods PO: Setup / Financials / Accounting / Control Purchasing Periods       (Setup / Financials / Accounting / Open and Close Periods) INV: Cost Management / Accounting Close Cycle / Inventory Accounting Periods

1.一進頁面就能看到表中已生成數據，而不用按ctrl+f11進行查詢的語句： 在form級的觸發器when-new-form-instance中， 寫如下代碼： go_block('XXXX'); execute_query; 要查詢的數據塊的名稱 說明： 當使用基於表建立的塊時，可使用塊裡的任何項的GO_BLOCK或者GO_ITEM找到那個塊當查詢數據到一個塊裡去，可通過execute_query進行處理 補充：用DELETE_RECORD從塊中刪除當前記錄，要改變數據庫的記錄，必須使用COMMIT_FORM  2,什麼辦法可以控制form中File菜單中的Export嗎？ 使其失效答： 在FORM中，在FORM級的觸發器EXPORT中， 將其中的代碼app_standard.event('EXPORT');屏蔽掉，改為null;試一試  3當form啟動時，在pre-form觸發器中instantiate你特定的菜單items 調用app_special.instantiate包  procedure APP_SPECIAL.INSTANTIATE(  option_name varchar2, --定義tigger名字  hint varchar2 default null,--定義菜單實體的名字，使用&來定義菜單實體的鍵盤快捷鍵  icon varchar2 default null,--  initially_enabledboolean default true,  separator varchar2 default null);--定義'LINE'為separator 例子  app_special.instantiate(’SPECIAL1’,’&rint Order’,’PRINTORD’);  4,在tool菜單中的check boxes  --option_name與trigger_name包括關鍵字CHECKBOX(例如：SPECIAL3_CHECKBOX)  app_special.instantiate(’SPECIAL3_CHECKBOX’,’Sp...

-- maintain temp table data  delete from sncus.phil_test_temp01 ; select * from sncus.phil_test_temp01 for update; -- check data SELECT a.segment1           po_num           ,b.line_num           po_line_num           ,c.shipment_num       po_shipment_num           ,e.temp1              quot_price           ,b.po_header_id           ,b.po_line_id           ,c.line_location_id           ,b.unit_price         pl_unit_price           ,b.quantity           line_qty           ,d.segment1           ,c.quantity          ...

declare  l_conc_id     number;  l_boolean     boolean := TRUE;  e_add_del     exception;  e_add_layout  exception;  e_set_option  exception;  e_set_repeat  exception;  begin     -- intialize the apps.    -- fnd_global.apps_initialize (10275, 52373, 222);      fnd_global.apps_initialize (10275, 51953, 222);     -- Adding delivery option to send the request output as email     l_boolean := fnd_request.add_delivery_option                     (type => 'E' -- this one to speciy the delivery option as email                     ,p_argument1 => 'Delivery Option Test' -- subject for the mail                     ,p_argument2 => '100385@senao.com' -- from address   ...

DECLARE l_return_status VARCHAR2 (10); CURSOR C_PO_CANCEL is SELECT pha.po_header_id, pha.org_id, pha.segment1 po_number, pha.type_lookup_code, pha.cancel_flag, pha.closed_code FROM po_headers_all pha WHERE 1=1 AND pha.segment1 = '340777'  AND nvl(pha.closed_code,'OPEN') = 'OPEN' AND nvl(pha.cancel_flag, 'N') = 'N' AND approved_flag = 'Y'; BEGIN fnd_global.apps_initialize (user_id =>10275, resp_id => 20707, resp_appl_id => 201); FOR i IN c_po_cancel LOOP mo_global.init ('PO'); mo_global.set_policy_context ('S',i.org_id ); DBMS_OUTPUT.PUT_LINE ('Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents'); po_document_control_pub.control_document (p_api_version => 1.0, -- p_api_version p_init_msg_list => fnd_api.g_true, -- p_init_msg_list p_commit => fnd_api.g_true, -- p_commit x_return_status => l_return_status, -- x_return_status p_doc_ty...

1. select * from wf_item_activity_statuses where ITEM_TYPE in ('POAPPRV') and ACTIVITY_STATUS ='&activity_status' order by item_key ACTIVITY_STATUS  ---------------  SUSPEND          COMPLETE         NOTIFIED         ERROR            ACTIVE           WAITING          DEFERRED   PROMPT **** Attribute Values SELECT NAME attr_name,        NVL (text_value,             NVL (TO_CHAR (number_value), TO_CHAR (date_value))            ) VALUE   FROM wf_item_attribute_values  WHERE item_type = UPPER ('&item_type')    AND item_key = NVL ('&item_key', item_key) / --Count of all workflow deferred activities based SELECT   COUNT (1), was.item_type   ...

SELECT resp.responsibility_name, pr.user_concurrent_program_name,        rg.request_group_name, ex.executable_name, ex.application_name   FROM fnd_executables_form_v ex,        fnd_concurrent_programs_vl pr,        fnd_request_group_units rgu,        fnd_request_groups rg,        fnd_responsibility_vl resp  WHERE 1 = 1    AND pr.executable_id = ex.executable_id    AND rgu.request_group_id = rg.request_group_id    AND rgu.request_unit_id = pr.concurrent_program_id    AND resp.request_group_id = rg.request_group_id    AND pr.user_concurrent_program_name LIKE '%WSH_R_0005%'

select 'RCV' code       ,ri.interface_transaction_id       ,receipt_num       ,null       ,(select segment1 from mtl_system_items_b        where INVENTORY_ITEM_ID = ri.ITEM_ID          and ORGANIZATION_ID =84) item_no       ,destination_type_code||'-'||inspection_status_code       ,primary_quantity       ,transaction_date       ,null       ,null       ,error_message from  rcv_transactions_interface ri,       rcv_shipment_headers rsh,       po_interface_errors pie where ri.shipment_header_id = rsh.shipment_header_id   and pie.batch_id = ri.group_id   and ri.transaction_status_code='ERROR'

select *  from alr_alerts a, alr_actions_v b where a.alert_id = b.alert_id     and subject ='<Alert Name>';

frmcmp_batch <file_nname.fmb> apps/<passord> output_file=$ONT_TOP/forms/<file_name.fmx> 1) Login to application server. 2) Go to the directory $AU_TOP/forms/US 3) ftp “.fmb” file in binary mode 4) Execute the below command to generate “.fmx”. frmcmp_batch userid=apps/<apps_paswd> module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=special EXAMPLE: frmcmp_batch userid=apps/apps module= RCVRCERC.fmb output_file=$AU_TOP/forms/US/ RCVRCERC.fmx  module_type=form batch=no compile_all=special

-- 建立在 FORM 中 --1. PACKAGE FILE_INFO IS   procedure import_file(v_block varchar2                       , v_field varchar2                       , v_col_separate varchar2                       , v_row_separate varchar2);   function  open_file return number; END; --2 PACKAGE BODY FILE_INFO IS    /*  用 途 : 已 Web方 式 開 啟 檔 案  參 數 : 無     傳 回 值 : >1(選 擇 檔 案 且 Upgrade)           0(選 擇 檔 案 但 未 Upgrade)          -1(未 選 擇 檔 案 )     */   function  open_file return number is     access_id ...