public override void VerifyRenderingInServerForm(Control control)
    {
        //required to avoid the runtime error "
        //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
    }
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
    DECLARE @columns NVARCHAR(MAX);
    DECLARE @sql NVARCHAR(MAX);
    -- 動態生成要顯示的題目欄位
    SELECT @columns = COALESCE(@columns + ', ', '') + QUOTENAME(QUESTION) FROM (
        SELECT  A.LID, A.QUESTION, a.[RANK]
        FROM QUESTIONNAIRE_L A
        --LEFT JOIN QUESTIONNAIRE_UA B ON A.LID = B.LID
        WHERE A.QID = @QID
    ) AS questions
    ORDER BY [RANK];
    
    -- 動態生成 SQL 查詢
    SET @sql = N'
        SELECT ORG_ID, EMP_NO, EMP_NAME, ' + @columns + ', SCORE, CREATION_DATE
        FROM (
            SELECT A.QUESTION, B.EMP_NO, [OPTION].[OPTION], 
                                         (select EMP.ORG_ID from ACCService.dbo.EMP_DATA_ALL EMP where EMP.EMP_NO = B.EMP_NO) ORG_ID,
                                         (select EMP.EMP_NAME from ACCService.dbo.EMP_DATA_ALL EMP where EMP.EMP_NO = B.EMP_NO) EMP_NAME,
                                         (select us.SCORE from QUESTIONNAIRE_US us where us.QID = A.QID and us.EMP_NO = B.EMP_NO) SCORE,
                                         (select max(ua.CREATION_DATE) from QUESTIONNAIRE_UA ua where ua.QID = A.QID) CREATION_DATE
            FROM QUESTIONNAIRE_L A
            LEFT JOIN QUESTIONNAIRE_UA B ON A.LID = B.LID
            LEFT JOIN (select distinct outer_list.LID, outer_list.EMP_NO ,STUFF((select '';'' + inner_list. [OPTION]
                        from QUESTIONNAIRE_UA as inner_list
                       where inner_list.LID = outer_list.LID
                         and inner_list.EMP_NO = outer_list.EMP_NO
                         for xml path('''')),
                      1,
                      1,
                      '''') as [OPTION]
             from QUESTIONNAIRE_UA as outer_list ) [OPTION]  on B.LID = [OPTION].LID and B.EMP_NO = [OPTION].EMP_NO 
            WHERE A.QID = '+ cast(@QID as varchar(50)) + ' 
        ) AS src
        PIVOT (
            MAX([OPTION]) -- 或者使用 SUM、MIN、MAX，根據實際情況
            FOR QUESTION IN (' + @columns + ')
        ) AS pivoted';
        
    -- 執行動態生成的 SQL 查詢
    EXEC sp_executesql @sql;
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
SELECT hcasa.org_id,
       role_acct.account_number,
       AR.CUSTOMER_NAME,
       hcsua.tax_reference,       
       party.person_first_name || party.person_middle_name || party.person_last_name contact_first_name,
       cont_point.phone_area_code || ' ' || cont_point.phone_number phone,
       cont_point.email_address,
       hcsua.site_use_code
  FROM hz_contact_points       cont_point,
       hz_cust_account_roles   acct_role,
       hz_parties              party,
       hz_parties              rel_party,
       hz_relationships        rel,
       hz_org_contacts         org_cont,
       hz_cust_accounts        role_acct,
       hz_contact_restrictions cont_res,
       hz_person_language      per_lang,
       hz_cust_acct_sites_all  hcasa,
       hz_cust_site_uses_all   hcsua,
       AR_CUSTOMERS            AR
 WHERE acct_role.party_id = rel.party_id
   and hcasa.Cust_acct_site_id = hcsua.Cust_acct_site_id
   and hcsua.site_use_code = 'BILL_TO'
   and acct_role.role_type = 'CONTACT'
   and org_cont.party_relationship_id = rel.relationship_id
   and rel.subject_id = party.party_id
   and rel_party.party_id = rel.party_id
   and cont_point.owner_table_id(+) = rel_party.party_id
   and cont_point.contact_point_type(+) IN ('EMAIL', 'PHONE')
   and cont_point.primary_flag(+) = 'Y'
   and acct_role.cust_account_id = role_acct.cust_account_id
   and acct_role.cust_account_id = AR.CUSTOMER_ID
   and role_acct.party_id = rel.object_id
   and party.party_id = per_lang.party_id(+)
   and per_lang.native_language(+) = 'Y'
   and party.party_id = cont_res.subject_id(+)
   and cont_res.subject_table(+) = 'HZ_PARTIES'
   and role_acct.cust_account_id = hcasa.cust_account_id
   and hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
   and hcasa.org_id = :P_ORG_ID
  order by role_acct.account_number
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
 
create table select * from bkp_ra_cust_trx_184210
as select * from ra_customer_trx_all where customer_trx_id=184210
and org_id=202;
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
查詢Conncurrent LOG FILE位置
SELECT FCPP.CONCURRENT_REQUEST_ID REQ_ID,
       FCP.NODE_NAME,
       FCP.LOGFILE_NAME
  FROM FND_CONC_PP_ACTIONS      FCPP,
       FND_CONCURRENT_PROCESSES FCP
 WHERE FCPP.PROCESSOR_ID = FCP.CONCURRENT_PROCESS_ID
   AND FCPP.ACTION_TYPE = 6
   AND FCPP.CONCURRENT_REQUEST_ID = 12271664;
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
                var process = new Process
                {
                    StartInfo = new ProcessStartInfo
                    {
                       FileName = "C:\\Debug\\ConsoleTest.exe",                    // 執行檔路徑
                        Arguments = "",                                                              // 執行時傳入的引數
                        UseShellExecute = false,                                                 // 表示是否要使用作業系統 shell 來啟動處理程序
                        CreateNoWindow = false,                                               // 表示是否要在新視窗中啟動處理程序
                        WindowStyle = ProcessWindowStyle.Hidden,                // 表示新視窗的顯示方式
                        RedirectStandardOutput = true,                                     // 表示應用程式的文字輸出是否寫入至 StandardOutput 資料流
                        RedirectStandardError = true,                                         // 表示應用程式的錯誤輸出是否寫入至 StandardError 資料流
                    },
                };
                //process.OutputDataReceived += new DataReceivedEventHandler(OutputHandler);
                //process.ErrorDataReceived += new DataReceivedEventHandler(OutputHandler);
                
                process.Start();
                process.BeginOutputReadLine();
                process.BeginErrorReadLine();
                process.WaitForExit();
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
image
SELECT TO_CHAR(add_months(add_months(sysdate, -12), ROWNUM - 1), 'MON-yy') AS yearmonth
  FROM DUAL
CONNECT BY ROWNUM <=
           (SELECT months_between(sysdate, add_months(sysdate, -12))
              FROM dual);
 
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
今天遇到一個問題,
外包工單在release 後未產生PR,
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
select prh.segment1 "PR NUM",
       trunc(prh.creation_date) "PO Creation Date",
       trunc(prl.creation_date) "Line Creation Date",
       prl.line_num "Seq #",
       msi.segment1 "Item Num",
       prl.item_description "Description",
       prl.quantity "Qty",
       trunc(prl.need_by_date) "Required By",
       ppf1.full_name "REQUESTOR",
       ppf2.agent_name "BUYER"
  from po.po_requisition_headers_all prh,
       po.po_requisition_lines_all prl,
       apps.per_people_f ppf1,
       (select distinct agent_id, agent_name from apps.po_agents_v) ppf2,
       po.po_req_distributions_all prd,
       inv.mtl_system_items_b msi,
       po.po_line_locations_all pll,
       po.po_lines_all pl,
       po.po_headers_all ph
 Where prh.requisition_header_id = prl.requisition_header_id
   and prl.requisition_line_id = prd.requisition_line_id
   and ppf1.person_id = prh.preparer_id
   and prh.creation_date between ppf1.effective_start_date and
       ppf1.effective_end_date
   and ppf2.agent_id(+) = msi.buyer_id
   and msi.inventory_item_id = prl.item_id
   and msi.organization_id = prl.destination_organization_id
   and pll.line_location_id(+) = prl.line_location_id
   and pll.po_header_id = ph.po_header_id(+)
   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
   AND PLL.LINE_LOCATION_ID IS NULL
   AND PRL.CLOSED_CODE IS NULL
   AND NVL(PRL.CANCEL_FLAG, 'N') <> 'Y'
 orDER BY 1, 2
 
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
select mp.organization_code "ORG",
       pha.segment1 "PO NO",
       asp.vendor_name "Supply",
       assa.vendor_site_code "Site",
       pla.line_num "Line",
       msib.segment1 "Item",
       msib.description "Description",
       pla.unit_meas_lookup_code "UOM",
       pla.unit_price "Unit Price",
       pla.quantity "QTY",
       pla.quantity * pla.unit_price "Amount",
       pha.currency_code "Currency",
       PRF.FULL_NAME "Buyer",
       prha.segment1 "PR No"
  from po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       po_distributions_all pda,
       po_requisition_headers_all prha,
       po_requisition_lines_all prla,
       po_req_distributions_all prda,
       ap_suppliers asp,
       ap_supplier_sites_all assa,
       mtl_system_items_b msib,
       mtl_parameters mp,
       PER_PEOPLE_F PRF,
       PO_AGENTS POA
 where pha.po_header_id = pla.po_header_id
   and asp.vendor_id = assa.vendor_id
   and pha.vendor_id = asp.vendor_id
   and pha.vendor_site_id =  assa.vendor_site_id
   and pha.po_header_id = pda.po_header_id
   and pla.item_id = msib.inventory_item_id
   and plla.po_line_id = pla.po_line_id
   and plla.line_location_id = pda.line_location_id
   and plla.ship_to_organization_id = msib.organization_id
   and mp.organization_id = msib.organization_id
   and PRF.PERSON_ID = POA.AGENT_ID
   and pha.agent_id = POA.AGENT_ID
   and prha.requisition_header_id(+) = prla.requisition_header_id
   and prla.requisition_line_id(+) = prda.requisition_line_id
   and prda.distribution_id(+) = pda.req_distribution_id
   and pha.org_id = XXX
   and pha.segment1 = 'XXX'
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
Select RQH.SEGMENT1 REQ_NUM,
       RQL.LINE_NUM,
       RQL.REQUISITION_HEADER_ID,
       RQL.REQUISITION_LINE_ID,
       RQL.ITEM_ID,
       RQL.UNIT_MEAS_LOOKUP_CODE,
       RQL.UNIT_PRICE,
       RQL.QUANTITY,
       RQL.QUANTITY_CANCELLED,
       RQL.QUANTITY_DELIVERED,
       RQL.CANCEL_FLAG,
       RQL.SOURCE_TYPE_CODE,
       RQL.SOURCE_ORGANIZATION_ID,
       RQL.DESTINATION_ORGANIZATION_ID,
       RQH.TRANSFERRED_TO_OE_FLAG
  from PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
 where RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
   and RQL.SOURCE_TYPE_CODE ='INVENTORY'
   and RQL.SOURCE_ORGANIZATION_ID is not null
   and not exists
 (select 'existing internal order'
          from OE_ORDER_LINES_ALL LIN
         where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
           and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
 orDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;
 
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
declare
v_invoice_no varchar2(25):='invoice';
v_term       varchar2(30):='payment term';
v_trx_id     number;
v_term_id    number;
v_date       date;
v_billing_cycle_id number;
begin
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
1 2 3 4 5
Blog Stats
⚠️

成人內容提醒

本部落格內容僅限年滿十八歲者瀏覽。
若您未滿十八歲，請立即離開。

已滿十八歲者，亦請勿將內容提供給未成年人士。