发票分配代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】发票分配
作者:Dezai.CN / 发布于2013/1/7/ 1333
--发票分配
SELECT hou.NAME org_name,
       ai.invoice_num,
       ai.invoice_date,
       pv.segment1 vendor,
       pv.vendor_name,
       'AID' source_table,
       aid.invoice_distribution_id source_id, 
       aid.line_type_lookup_code line_type, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
       gcc_ori.code_combination_id orig_ccid,
       gcc_ori.concatenated_segments orig_acct,
       aid.amount,
       (SELECT gcc_new.code_combination_id
          FROM gl_code_combinations_kfv gcc_new
         WHERE gcc_new.segment1 = gcc_ori.segment1
           AND gcc_new.segment2 = gcc_ori.segment2
           AND gcc_new.segment3 = gcc_ori.segment3
           AND gcc_new.segment4 = (CASE WHEN aid.line_type_lookup_code IN ('ITEM') THEN gcc_pay.segment4 WHEN
                aid.line_type_lookup_code IN ('PREPAY') THEN gcc_pre.segment4 END)
           AND gcc_new.segment5 = gcc_ori.segment5
           AND gcc_new.chart_of_accounts_id = gcc_ori.chart_of_accounts_id) new_ccid,
       (CASE
         WHEN aid.line_type_lookup_code IN ('ITEM') THEN
          gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pay.segment4 || '.' ||
          gcc_ori.segment5
         WHEN aid.line_type_lookup_code IN ('PREPAY') THEN
          gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pre.segment4 || '.' ||
          gcc_ori.segment5
       END) new_acct,
       gcc_pay.segment4 pay_acc,
       gcc_pre.segment4 pre_acc
  FROM ap_invoices_all              ai,
       hr_operating_units           hou,
       po_vendors                   pv,
       po_vendor_sites_all          pvs,
       gl_code_combinations_kfv     gcc_pay,
       gl_code_combinations_kfv     gcc_pre,
       ap_invoice_distributions_all aid,
       gl_code_combinations_kfv     gcc_ori
WHERE ai.org_id = hou.organization_id
   AND ai.vendor_id = pv.vendor_id
   AND ai.vendor_site_id = pvs.vendor_site_id
   AND pvs.accts_pay_code_combination_id = gcc_pay.code_combination_id(+)
   AND pvs.prepay_code_combination_id = gcc_pre.code_combination_id(+)
   AND ai.invoice_id = aid.invoice_id
   AND aid.dist_code_combination_id = gcc_ori.code_combination_id
   AND pv.vendor_name = 'xxxxxx'
试试其它关键字
　发票分配　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)