1. 建立XML source file (xxxx.xml)
2. 建立word template file (xxxx.rtf)
3. 在XML Publisher administrator 中定义数据源(Data definitions), 注意code 必须与后面将要建的Concurrent short name 一致
4. 在XML Publisher administrator 中定义模板(Template), 注意code 必须与后面将要建的Concurrent short name 一致
上述两文件须放到客制化应用的reports目录中。
5. 注册Concurrent , short name 须与上述code 一致，执行文件为 XDODTEXE，这是XML Publisher Data Template Executable，设定参数及其它选项。
Below is a XML source file example:
xml version="1.0" encoding="WINDOWS-1252" ?>
- <dataTemplate name="dataTemplateName" description="Template description" version="1.0">
- <properties>
properties>
- <parameters>
parameters>
- <lexicals>
lexicals>
- <dataQuery>
- <sqlStatement name="Q1">
-
SELECT   ooha.order_number ORDER_NUMBER,
         hp.party_name CUSTOMER_NAME,
         ooha.attribute1 ULTIMATE_DESTINATION,
         oola.ordered_item ITEM_NUMBER,
         msit.description ITEM_DESCRIPTION,
         rcta.trx_number INVOICE_NUMBER,
         gcck.concatenated_segments INVOICE_LINE_REVENUE_ACCOUNT,
         TO_CHAR(oola.line_number||'.'||oola.shipment_number) LINE_NUM,
         DECODE (oola.line_category_code,
                      'RETURN', 0 - NVL (oola.ordered_quantity, 0),
                      NVL (oola.ordered_quantity, 0)
                ) QUANTITY,         
         NVL (oola.unit_selling_price, 0) SELL_PRICE,         
         NVL (oola.unit_selling_price, 0)*
                           DECODE (oola.line_category_code,
                                   'RETURN', 0 - NVL (oola.ordered_quantity, 0),
                                   NVL (oola.ordered_quantity, 0)
                                  )  TOTAL_SELL_PRICE,   
         TO_CHAR (oola.pricing_date, 'DD-MM-RRRR') PRICING_DATE,
         TO_CHAR (oola.actual_shipment_date, 'DD-MM-RRRR') SHIPPED_DATE,
         oola.flow_status_code LINE_STATUS,
         oo.description DISCOUNT_NAME,
         oo.name OFFER_NUMBER,
         DECODE(opa.arithmetic_operator, 'AMT', opa.operand, 0) DISCOUNT_AMOUNT,                 
         DECODE(opa.arithmetic_operator, '%', opa.operand, 0) DISCOUNT_PERCENT,                 
         gccka.concatenated_segments ACCRUAL_ACCOUNT,
         gcckb.concatenated_segments SALES_EXPENSE_ACCOUNT,
         ROUND(DECODE(ofuab.utilization_type,'ADJUSTMENT',0,DECODE (
              arithmetic_operator,
              '%',
              (  (opa.operand / 100)
               * NVL (oola.unit_selling_price, 0)
               * NVL (oola.ordered_quantity, 0)),
              'AMT',
              (opa.operand * NVL (oola.ordered_quantity, 0)),
              0
           )),2)
              CALCULATED_AMOUNT,
         ROUND (ofuab.acctd_amount, 2) TRADE_MGMT_EARNED_AMOUNT
    FROM oe_order_lines_all oola,
         oe_order_types_v ootv,
         oe_line_types_v oltv,
         hz_cust_site_uses_all hcsua,
         hz_cust_acct_sites_all hcasa,
         hz_locations hl,
         hz_party_sites hps,
         hz_parties hp,
         mtl_system_items_tl msit,
         mtl_system_items_b_kfv msibk,
         ra_salesreps_all rs,
         oe_sold_to_orgs_v ostov,
         oe_price_adjustments opa,
         qp_list_headers_vl qlhv,
         oe_order_headers_all ooha,
         ra_customer_trx_lines_all rctla,
         ra_customer_trx_all rcta,
         ra_cust_trx_line_gl_dist_all rctlgda,
         gl_code_combinations_kfv gcck,
         ozf_funds_utilized_all_b ofuab,
         ozf_offers_v oo,
         ozf_acctng_events_all oaea,
         ozf_ae_headers_all oaha,
         ozf_ae_lines_all oalaa,
         gl_code_combinations_kfv gccka,
         ozf_ae_lines_all oalab,
         gl_code_combinations_kfv gcckb,
         xla_ae_headers xah,
         xla_ae_lines xal         
   WHERE ootv.order_type_id(+) = ooha.order_type_id
     AND oltv.line_type_id(+) = oola.line_type_id
     AND ooha.salesrep_id = rs.salesrep_id(+)
     AND ooha.ship_to_org_id = hcsua.site_use_id(+)
     AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
     and rs.org_id = oola.org_id
     AND hcasa.party_site_id = hps.party_site_id
     AND hl.location_id = hps.location_id
     AND hps.party_id = hp.party_id
     AND NVL (msibk.organization_id, 0) = (SELECT MASTER_ORGANIZATION_ID
                                             FROM OE_SYSTEM_PARAMETERS_ALL
                                            WHERE org_id = ooha.org_id)
     AND msibk.inventory_item_id = msit.inventory_item_id
     AND msibk.organization_id = msit.organization_id
     AND msit.LANGUAGE = USERENV ('LANG')
     AND oola.inventory_item_id = msibk.inventory_item_id(+)
     AND opa.list_header_id = qlhv.list_header_id
     AND (oola.line_id = opa.line_id OR opa.line_id IS NULL)
     AND oola.header_id = ooha.header_id
     AND NVL (ooha.cancelled_flag, 'N') = 'N'
     AND NVL (oola.cancelled_flag, 'N') = 'N'
     AND ostov.organization_id(+) = ooha.sold_to_org_id
     AND opa.header_id = ooha.header_id
     AND (   opa.list_line_type_code = 'DIS'
          OR opa.list_line_type_code = 'SUR'
          OR opa.list_line_type_code = 'PBH'
         )
     AND opa.applied_flag = 'Y'
     AND NOT EXISTS (
            SELECT 'X'
              FROM oe_price_adj_assocs pas, oe_price_adjustments pa1
             WHERE pas.rltd_price_adj_id = opa.price_adjustment_id
               AND pa1.price_adjustment_id = pas.price_adjustment_id
               AND pa1.list_line_type_code = 'PBH')
     AND TO_CHAR (oola.line_id) = rctla.interface_line_attribute6
     AND rcta.customer_trx_id = rctla.customer_trx_id
     AND oola.flow_status_code = NVL(:pi_line_status,'CLOSED')
     AND rcta.org_id = ooha.org_id
     AND rcta.customer_trx_id = rctlgda.customer_trx_id
     AND rctla.customer_trx_line_id = rctlgda.customer_trx_line_id
     AND rctlgda.account_class = 'REV'     
     AND rcta.interface_header_attribute1(+) = TO_CHAR (ooha.order_number)
     AND oola.line_id = ofuab.order_line_id
     AND qlhv.list_header_id = ofuab.component_id
     AND oola.org_id = ofuab.org_id
     AND ooha.org_id = oola.org_id    
     AND ofuab.gl_posted_flag = 'Y'
     AND oo.list_header_id = qlhv.list_header_id
     AND oaha.ae_header_id = oalaa.ae_header_id
     AND oaha.ae_header_id = oalab.ae_header_id
     AND oaea.accounting_event_id = oaha.accounting_event_id
     AND oaea.source_id = ofuab.utilization_id
     AND opa.price_adjustment_id = ofuab.price_adjustment_id
     AND rctlgda.event_id = xah.event_id
     AND xah.ledger_id = rcta.set_of_books_id
     AND xal.ae_header_id = xah.ae_header_id
     AND xal.accounting_class_code = 'REVENUE'
     AND gcck.code_combination_id = xal.code_combination_id
     AND oalaa.source_table = 'OZF_FUNDS_UTILIZED_ALL_B'
     AND oalab.source_table = 'OZF_FUNDS_UTILIZED_ALL_B'
     AND oalaa.code_combination_id = gccka.code_combination_id
     AND oalab.code_combination_id = gcckb.code_combination_id
     AND oalaa.ae_line_type_code = 'ACCRUAL_LIABILITY'
     AND oalab.ae_line_type_code = 'EXPENSE ACCOUNT'
     AND ofuab.acctd_amount IS NOT NULL 
     AND oo.list_header_id = NVL (:pi_offer_name, oo.list_header_id)
     AND oola.flow_status_code = NVL(:pi_line_status,oola.flow_status_code)
     AND NVL(TRUNC(oola.actual_shipment_date),TRUNC(SYSDATE)-1) BETWEEN NVL(FND_DATE.canonical_to_date(:pi_ship_date_from),'01-JAN-1900')
                                                                AND NVL(FND_DATE.canonical_to_date(:pi_ship_date_to),TRUNC(SYSDATE))   
     AND rcta.customer_trx_id = NVL(:pi_invoice_num,rcta.customer_trx_id)
     AND ooha.order_number = NVL(:pi_order_number,ooha.order_number)
     AND &WHERE_FLEX1
     AND &WHERE_FLEX2 
GROUP BY ooha.order_number,
         ofuab.utilization_type,
         ofuab.utilization_id,
         hp.party_name,
         ooha.attribute1,
         oola.ordered_item,
         msit.description,
         rcta.trx_number,
         gcck.concatenated_segments,
         (oola.line_number||'.'||oola.shipment_number),
         oola.line_category_code,
         oola.ordered_quantity,     
         oola.unit_selling_price,                         
         oola.pricing_date,
         oola.actual_shipment_date,
         oola.flow_status_code,
         oo.description, 
         oo.name,
         opa.arithmetic_operator,
         opa.operand,                 
         gccka.concatenated_segments,
         gcckb.concatenated_segments,         
         ofuab.acctd_amount            
UNION ALL
SELECT   ooha.order_number ORDER_NUMBER,
         hp.party_name CUSTOMER_NAME,
         ooha.attribute1 ULTIMATE_DESTINATION,
         oola.ordered_item ITEM_NUMBER,
         msit.description ITEM_DESCRIPTION,
         NULL INVOICE_NUMBER,
         NULL INVOICE_LINE_REVENUE_ACCOUNT,
         to_char(oola.line_number) LINE_NUM,
         DECODE (oola.line_category_code,
                      'RETURN', 0 - NVL (oola.ordered_quantity, 0),
                      NVL (oola.ordered_quantity, 0)
                ) QUANTITY,         
         NVL (oola.unit_selling_price, 0) SELL_PRICE,         
         NVL (oola.unit_selling_price, 0)*
                           DECODE (oola.line_category_code,
                                   'RETURN', 0 - NVL (oola.ordered_quantity, 0),
                                   NVL (oola.ordered_quantity, 0)
                                  )  TOTAL_SELL_PRICE,   
         TO_CHAR (oola.pricing_date, 'DD-MM-RRRR') PRICING_DATE,
         TO_CHAR (oola.actual_shipment_date, 'DD-MM-RRRR') SHIPPED_DATE,
         oola.flow_status_code LINE_STATUS,
         NULL DISCOUNT_NAME,
         NULL OFFER_NUMBER,
         NULL DISCOUNT_AMOUNT,                 
         NULL DISCOUNT_PERCENT,                 
         NULL ACCRUAL_ACCOUNT,
         NULL SALES_EXPENSE_ACCOUNT,
         NULL  CALCULATED_AMOUNT,
         NULL TRADE_MGMT_EARNED_AMOUNT
    FROM oe_order_lines_all oola,
         oe_order_types_v ootv,
         oe_line_types_v oltv,
         hz_cust_site_uses_all hcsua,
         hz_cust_acct_sites_all hcasa,
         hz_locations hl,
         hz_party_sites hps,
         hz_parties hp,
         mtl_system_items_tl msit,
         mtl_system_items_b_kfv msibk,
         ra_salesreps_all rs,
         oe_sold_to_orgs_v ostov,                 
         oe_order_headers_all ooha         
   WHERE ootv.order_type_id(+) = ooha.order_type_id
     AND oltv.line_type_id(+) = oola.line_type_id
     AND ooha.salesrep_id = rs.salesrep_id(+)
     AND ooha.ship_to_org_id = hcsua.site_use_id(+)
     AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
     AND hcasa.party_site_id = hps.party_site_id
     AND hps.party_id = hp.party_id
     AND hl.location_id = hps.location_id
     AND NVL (msibk.organization_id, 0) = (SELECT MASTER_ORGANIZATION_ID
                                             FROM OE_SYSTEM_PARAMETERS_ALL
                                            WHERE org_id = ooha.org_id)
     AND msibk.inventory_item_id = msit.inventory_item_id
     AND msibk.organization_id = msit.organization_id
     AND msit.LANGUAGE = USERENV ('LANG')
     AND oola.inventory_item_id = msibk.inventory_item_id(+)     
     AND oola.header_id = ooha.header_id
     AND NVL (ooha.cancelled_flag, 'N') = 'N'
     AND NVL (oola.cancelled_flag, 'N') = 'N'
     AND ostov.organization_id(+) = ooha.sold_to_org_id 
     AND oola.line_id NOT IN (SELECT line_id FROM oe_price_adjustments a,ozf_offers_v b
                                             WHERE a.list_header_id=b.list_header_id )
     AND oola.flow_status_code = NVL(:pi_line_status,oola.flow_status_code)
     AND oola.flow_status_code != 'CLOSED'    
     AND NVL(TRUNC(oola.actual_shipment_date),TRUNC(SYSDATE)-1) BETWEEN NVL(FND_DATE.canonical_to_date(:pi_ship_date_from),'01-JAN-1900')
                                                                AND NVL(FND_DATE.canonical_to_date(:pi_ship_date_to),TRUNC(SYSDATE))   
     AND ooha.order_number = NVL(:pi_order_number,ooha.order_number)
GROUP BY ooha.order_number,
         hp.party_name, 
         ooha.attribute1,
         oola.ordered_item,
         msit.description,      
         oola.line_number,
         oola.line_category_code,
         oola.ordered_quantity,     
         oola.unit_selling_price,                         
         oola.pricing_date,
         oola.actual_shipment_date,
         oola.flow_status_code
UNION ALL
SELECT   ooha.order_number ORDER_NUMBER,
         hp.party_name CUSTOMER_NAME,
         ooha.attribute1 ULTIMATE_DESTINATION,
         oola.ordered_item ITEM_NUMBER,
         msit.description ITEM_DESCRIPTION,
         NULL INVOICE_NUMBER,
         NULL INVOICE_LINE_REVENUE_ACCOUNT,
         TO_CHAR(oola.line_number) LINE_NUM,
         DECODE (oola.line_category_code,
                      'RETURN', 0 - NVL (oola.ordered_quantity, 0),
                      NVL (oola.ordered_quantity, 0)
                ) QUANTITY,         
         NVL (oola.unit_selling_price, 0) SELL_PRICE,         
         NVL (oola.unit_selling_price, 0)*
                           DECODE (oola.line_category_code,
                                   'RETURN', 0 - NVL (oola.ordered_quantity, 0),
                                   NVL (oola.ordered_quantity, 0)
                                  )  TOTAL_SELL_PRICE,   
         TO_CHAR (oola.pricing_date, 'DD-MM-RRRR') PRICING_DATE,
         TO_CHAR (oola.actual_shipment_date, 'DD-MM-RRRR') SHIPPED_DATE,
         oola.flow_status_code LINE_STATUS,
         oo.description DISCOUNT_NAME,
         oo.name OFFER_NUMBER,
         DECODE(opa.arithmetic_operator, 'AMT', opa.operand, 0) DISCOUNT_AMOUNT,                 
         DECODE(opa.arithmetic_operator, '%', opa.operand, 0) DISCOUNT_PERCENT,                 
         NULL ACCRUAL_ACCOUNT,
         NULL SALES_EXPENSE_ACCOUNT,
         DECODE(arithmetic_operator, 
         '%',((opa.operand/100) * NVL (oola.unit_selling_price, 0) * NVL (oola.ordered_quantity, 0)),
         'AMT',(opa.operand * NVL (oola.ordered_quantity, 0)),0)  CALCULATED_AMOUNT,
         NULL TRADE_MGMT_EARNED_AMOUNT
    FROM oe_order_lines_all oola,
         oe_order_types_v ootv,
         oe_line_types_v oltv,
         hz_cust_site_uses_all hcsua,
         hz_cust_acct_sites_all hcasa,
         hz_locations hl,
         hz_party_sites hps,
         hz_parties hp,
         mtl_system_items_tl msit,
         mtl_system_items_b_kfv msibk,
         ra_salesreps_all rs,
         oe_sold_to_orgs_v ostov,        
         qp_list_headers_vl qlhv,
         oe_order_headers_all ooha,
         oe_price_adjustments opa,
         ozf_offers_v oo             
   WHERE ootv.order_type_id(+) = ooha.order_type_id
     AND oltv.line_type_id(+) = oola.line_type_id
     AND ooha.salesrep_id = rs.salesrep_id(+)
     AND ooha.ship_to_org_id = hcsua.site_use_id(+)
     AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
     AND hcasa.party_site_id = hps.party_site_id
     AND hps.party_id = hp.party_id
     AND hl.location_id = hps.location_id
     AND NVL (msibk.organization_id, 0) = (SELECT MASTER_ORGANIZATION_ID
                                             FROM OE_SYSTEM_PARAMETERS_ALL
                                            WHERE org_id = ooha.org_id)
     AND msibk.inventory_item_id = msit.inventory_item_id
     AND msibk.organization_id = msit.organization_id
     AND msit.LANGUAGE = USERENV ('LANG')
     AND oola.inventory_item_id = msibk.inventory_item_id(+)     
     AND oola.header_id = ooha.header_id
     AND NVL (ooha.cancelled_flag, 'N') = 'N'
     AND NVL (oola.cancelled_flag, 'N') = 'N'
     AND ostov.organization_id(+) = ooha.sold_to_org_id 
     AND opa.list_header_id = qlhv.list_header_id
     AND (opa.line_id = oola.line_id OR opa.line_id IS NULL)
     AND NVL(opa.line_id,oola.line_id) = oola.line_id
     AND opa.header_id = ooha.header_id(+)
     AND (   opa.list_line_type_code = 'DIS'
          OR opa.list_line_type_code = 'SUR'
          OR opa.list_line_type_code = 'PBH'
         )
     AND opa.applied_flag = 'Y'
     AND NOT EXISTS (
            SELECT 'X'
              FROM oe_price_adj_assocs pas, oe_price_adjustments pa1
             WHERE pas.rltd_price_adj_id = opa.price_adjustment_id
               AND pa1.price_adjustment_id = pas.price_adjustment_id
               AND pa1.list_line_type_code = 'PBH')  
     AND oo.list_header_id = qlhv.list_header_id            
     AND oola.flow_status_code = NVL(:pi_line_status,oola.flow_status_code)
     AND oola.flow_status_code != 'CLOSED'    
     AND NVL(TRUNC(oola.actual_shipment_date),TRUNC(SYSDATE)-1) BETWEEN NVL(FND_DATE.canonical_to_date(:pi_ship_date_from),'01-JAN-1900')
                                                                AND NVL(FND_DATE.canonical_to_date(:pi_ship_date_to),TRUNC(SYSDATE))   
     AND ooha.order_number = NVL(:pi_order_number,ooha.order_number)    
GROUP BY ooha.order_number,
         hp.party_name,   
         ooha.attribute1,
         oola.ordered_item,
         msit.description,    
         oola.line_number,
         oola.line_category_code,
         oola.ordered_quantity,     
         oola.unit_selling_price,                         
         oola.pricing_date,
         oola.actual_shipment_date,
         oola.flow_status_code,
         oo.description,
         oo.name,
         opa.arithmetic_operator,
         opa.operand,ooha.org_id
ORDER BY 1 ASC,        
         2 ASC,
         3 ASC,
         5 ASC,
         8 ASC,
         6 ASC,
         7 ASC,
         9 ASC,
         14 ASC,
         10 ASC,
         12 ASC,
         17 ASC,
         16 ASC,
         15 ASC,
         11 ASC
]]> f(clean);
sqlStatement>
dataQuery>
- <dataStructure>
- <group name="Q1" source="Q1">
group>
dataStructure>
dataTemplate>
ITPub博客

R12 中利用XML source file 建立XML Publisher reports

最新文章
