R12 获取采购行税

SELECT qrslt.trx_id             po_header_id
      ,qrslt.line_trx_user_key1 po_line_num
      ,qrslt.tax_rate_code
      ,tax_rate
  FROM (SELECT lines.application_id
              ,lines.entity_code
              ,lines.event_class_code
              ,lines.trx_id
              ,lines.trx_line_id
              ,lines.delete_flag
              ,lines.summary_tax_line_id
              ,lines.cancel_flag
              ,lines.tax_line_id
              ,lines.trx_line_number
              ,lines.tax_line_number
              ,lines.tax_regime_code
              ,lines.tax
              ,lines.tax_status_code
              ,lines.tax_rate_code
              ,lines.tax_rate
              ,lines.tax_amt
              ,lines.tax_jurisdiction_code
              ,statuslookup.meaning              AS tax_line_status
              ,NULL                              AS delete_switcher_attr
              ,linedetfactors.line_trx_user_key1
              ,linedetfactors.line_trx_user_key2
              ,lines.tax_currency_code
          FROM zx_lines             lines
              ,fnd_lookups          statuslookup
              ,zx_lines_det_factors linedetfactors
         WHERE statuslookup.lookup_type = 'ZX_TAX_LINE_STATUS'
           AND decode(cancel_flag
                     ,'Y'
                     ,'CANCELLED'
                     ,'ACTIVE') = statuslookup.lookup_code
           AND SYSDATE BETWEEN statuslookup.start_date_active AND
               nvl(statuslookup.end_date_active
                  ,SYSDATE)
           AND nvl(statuslookup.enabled_flag
                  ,'N') = 'Y'
           AND nvl(lines.delete_flag
                  ,'N') <> 'Y'
           AND lines.trx_line_id = linedetfactors.trx_line_id
           AND lines.trx_id = linedetfactors.trx_id
           AND lines.application_id = linedetfactors.application_id
           AND lines.entity_code = linedetfactors.entity_code
           AND lines.event_class_code = linedetfactors.event_class_code) qrslt
 WHERE application_id = 201
   AND entity_code = 'PURCHASE_ORDER'
   AND event_class_code = 'PO_PA'
   AND trx_id = 28;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-769641/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13387766/viewspace-769641/