常用资料整理

     本问将整理常用的内容，以防止长时间不用而忘记。   

     1.查找xml publisher输出xml的路径，以便查出数据xml产生情况，便于调试 

     SELECT outfile_name FROM fnd_concurrent_requests where request_id= 4212253,

2.FND: Personalization Region Link Enabled, Personalize Self-service Defn:控制OAF个性化,如果设置后不能生效，则需要重启ap

3.公司的cost manager已经罢工2次了，而且2次都等到月底结账时才发现，造成月底很忙的时候，还要让cost manager计算很多数据。下面是查询cost manager状态的代码，以便，每周都查看一下，防止再次出现类似问题。

SELECT request_id RequestId,
request_date RequestDt,
phase_code Phase,
status_code Status

FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
WHERE fcp.application_id = 702 AND
fcp.concurrent_program_name = 'CMCTCM' AND
fcr.concurrent_program_id = fcp.concurrent_program_id AND
fcr.program_application_id = 702

4.库存交易后查看成本信息的代码：

select *  from  CST_INV_DISTRIBUTION_V cidv
where cidv.transaction_id=44733943

5.在SO中抓Ship to,bill to的一段脚本

select bill_to.LOCATION,
       bill_to.CUSTOMER_NAME bill_to_customer,
       bill_to.ADDRESS_LINE_1 bill_to_address ,      
       ship_to.LOCATION,
       ship_to.CUSTOMER_NAME bill_to_customer,
       ship_to.ADDRESS_LINE_1 bill_to_address ,   
       tt.invoice_to_org_id  ,
          tt.sold_to_org_id
    , tt.ship_to_org_id
   , tt.sold_to_org_id ,
       tt.*
  from OE_ORDER_LINES_all tt,
       (SELECT /*+ INDEX(ACCT_SITE,HZ_CUST_ACCT_SITES_N2) */
         SITE.LOCATION,
         PARTY.PARTY_NAME CUSTOMER_NAME,
         SITE.SITE_USE_ID ORGANIZATION_ID,
         SITE.LOCATION LOCATION_CODE,
         LOC.ADDRESS1 ADDRESS_LINE_1,
         LOC.ADDRESS2 ADDRESS_LINE_2,
         LOC.ADDRESS3 ADDRESS_LINE_3,
         decode(LOC.CITY, null, null, LOC.CITY || ', ') ||
         decode(LOC.state, null, LOC.province || ', ', LOC.state || ', ') ||
         decode(LOC.postal_code, null, null, LOC.postal_code || ', ') ||
         decode(LOC.country, null, null, LOC.country) address_line_4,
         LOC.ADDRESS4 address_line_5,
         CUST_ACCT.CUST_ACCOUNT_ID customer_id,
         cust_acct.account_number customer_Number
          FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
               HZ_PARTY_SITES         PARTY_SITE,
               HZ_LOCATIONS           LOC,
               HZ_CUST_SITE_USES_ALL  SITE,
               HZ_PARTIES             PARTY,
               HZ_CUST_ACCOUNTS       CUST_ACCT
         WHERE SITE.SITE_USE_CODE = 'BILL_TO'
           AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
           AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
           AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
         --  and acct_site.status = 'A'
              --and   ACCT_SITE.CUST_ACCOUNT_ID =  --:parameter.lov_num_param1
           AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
           AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
             
         --  AND CUST_ACCT.status = 'A'
         --  AND site.status = 'A'
        --and SITE.LOCATION='HK/MGN'
        -- and cust_acct.account_number='1277'
          -- and SITE.SITE_USE_ID=1262 --2524  --2584
          -- and CUST_ACCT.CUST_ACCOUNT_ID = 1098   ---customer_id
         ORDER BY SITE.LOCATION) bill_to,
        
(
SELECT   /*+ INDEX(ACCT_SITE,HZ_CUST_ACCT_SITES_N2) */
 SITE.LOCATION,PARTY.PARTY_NAME CUSTOMER_NAME,SITE.SITE_USE_ID
ORGANIZATION_ID,SITE.LOCATION LOCATION_CODE,LOC.ADDRESS1 ADDRESS_LINE_1,
LOC.ADDRESS2 ADDRESS_LINE_2,LOC.ADDRESS3 ADDRESS_LINE_3,
decode(LOC.CITY,null, null, LOC.CITY|| ', ')
||decode(LOC.state, null, LOC.province || ', ', LOC.state || ', ') ||
decode(LOC.postal_code,null, null, LOC.postal_code || ', ') ||
decode(LOC.country, null, null, LOC.country) address_line_4 ,
LOC.ADDRESS4 ADDRESS_LINE_5,
CUST_ACCT.CUST_ACCOUNT_ID customer_id,
cust_acct.account_number customer_Number
FROM
     HZ_CUST_ACCT_SITES_ALL     ACCT_SITE,
     HZ_PARTY_SITES             PARTY_SITE,
     HZ_LOCATIONS               LOC,
     HZ_CUST_SITE_USES_ALL      SITE,
     HZ_PARTIES           PARTY,
     HZ_CUST_ACCOUNTS      CUST_ACCT
WHERE
      SITE.SITE_USE_CODE         = 'SHIP_TO'
AND   SITE.CUST_ACCT_SITE_ID     = ACCT_SITE.CUST_ACCT_SITE_ID
AND   ACCT_SITE.PARTY_SITE_ID    = PARTY_SITE.PARTY_SITE_ID
AND   PARTY_SITE.LOCATION_ID     = LOC.LOCATION_ID
and    acct_site.status='A'
--and   ACCT_SITE.CUST_ACCOUNT_ID=:parameter.lov_num_param1
AND ACCT_SITE.CUST_ACCOUNT_ID=CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
 
AND CUST_ACCT.status='A'
AND site.status='A'
order by site.location
) ship_to

 where tt.line_id = 4051505
   and bill_to.organization_id = tt.invoice_to_org_id --1262
  -- and bill_to.customer_id(+) = tt.sold_to_org_id  --1098   1051
    and ship_to.organization_id = tt.ship_to_org_id
  -- and ship_to.customer_id(+) = tt.sold_to_org_id