事务处理及收款常用更新

1：事务处理更新客户

      此更新系统未提供API，如果事务的的科目与客户相关，请先确认更新客户是否会与科目产生影响，如果有影响，请科目一起更新；

      事务处理中与客户相关有2个表：分别是ra_customer_trx_all 、ar_payment_schedules_all 

 1.获取客户信息

  select distinct hca.cust_account_id,
                  hca.cust_account_id,
                  hcua.site_use_id,
                  hcua.site_use_id,
                  hca.cust_account_id,
                  hcua.site_use_id
    into l_sold_to_customer_id,
         l_bill_to_customer_id,
         l_bill_to_site_use_id,
         l_bill_to_address_id,
         l_paying_customer_id,
         l_paying_site_use_id
    from hz_cust_accounts       hca,
         hz_party_sites         hps,
         hz_cust_acct_sites_all hcaa,
         hz_cust_site_uses_all  hcua,
         hz_parties             hp
   where hca.party_id = hps.party_id
     and hps.party_site_id = hcaa.party_site_id
     and hcaa.cust_acct_site_id = hcua.cust_acct_site_id
     and hcua.status = hca.status
     and hcaa.status = hca.status
     and hps.status = hca.status
     and hca.status = 'A'
     and hp.party_id = hca.party_id
     and hcua.org_id = l_org_id
     and hp.known_as = l_known_as;

2、更新 ra_customer_trx_all

update ra_customer_trx_all rcta
   set rcta.sold_to_customer_id = l_sold_to_customer_id,
       rcta.bill_to_customer_id = l_bill_to_customer_id,
       rcta.bill_to_site_use_id = l_bill_to_site_use_id,
       rcta.bill_to_address_id  = l_bill_to_address_id,
       rcta.paying_customer_id  = l_paying_customer_id,
       rcta.paying_site_use_id  = l_paying_site_use_id
 where rcta.customer_trx_id = l_customer_trx_id;

3、更新 AR_PAYMENT_SCHEDULES_ALL

 update ar_payment_schedules_all apsa
    set apsa.customer_id          = l_paying_customer_id,
        apsa.customer_site_use_id = l_paying_site_use_id
  where apsa.customer_trx_id = l_customer_trx_id;

2：事务处理更新GL日期

     GL日期与期间相关，更新前请确认该发票是否传送至总账，GL日期的变化是否会对总账的日记账发送额产生影响；

     与GL日期相关的表：

    ra_customer_trx_all、

   ar_payment_schedules_all、

   ra_cust_trx_line_gl_dist_all、

   xla_events

  1、更新 ra_customer_trx_all 表

 update ra_customer_trx_all r
    set r.creation_date    = rpt_u.md_gl_date,
        r.last_update_date = rpt_u.md_gl_date,
        r.trx_date         = rpt_u.md_gl_date
  where r.customer_trx_id = rpt_u.customer_trx_id;

  2、更新 ar_payment_schedules_all表

 update ar_payment_schedules_all p
    set p.creation_date       = rpt_u.md_gl_date,
        p.last_update_date    = rpt_u.md_gl_date,
        p.trx_date            = rpt_u.md_gl_date,
        p.gl_date             = rpt_u.md_gl_date,
        p.program_update_date = rpt_u.md_gl_date
  where p.customer_trx_id = rpt_u.customer_trx_id;

  3、更新 ra_cust_trx_line_gl_dist_all表

 update ra_cust_trx_line_gl_dist_all p
    set p.creation_date    = rpt_u.md_gl_date,
        p.last_update_date = rpt_u.md_gl_date,
        p.gl_date          = rpt_u.md_gl_date
  where p.customer_trx_id = rpt_u.customer_trx_id;

  4、更新 xla_events表

 update xla_events p
    set p.creation_date       = rpt_u.md_gl_date,
        p.last_update_date    = rpt_u.md_gl_date,
        p.event_date          = rpt_u.md_gl_date,
        p.program_update_date = rpt_u.md_gl_date,
        p.transaction_date    = rpt_u.md_gl_date
  where p.event_id = rpt_u.event_id;

3：收款更新客户

     客户涉及以下表：ar_cash_receipts_all 、 ar_payment_schedules_all 、 ar_distributions_all具体更新逻辑如下

1、获取客户信息

 select c.cust_account_id, -- 收款客户ID
        ua.site_use_id -- 获得地点ID
   into l_cust_account_id, l_site_user_id
   from hz_cust_accounts       c,
        hz_customer_profiles   cp,
        hz_parties             party,
        hz_cust_acct_sites_all sa,
        hz_cust_site_uses_all  ua
  where sa.cust_acct_site_id = ua.cust_acct_site_id
    and sa.status = ua.status
    and sa.status = 'A' --已启用
    and ua.site_use_code = 'BILL_TO'
    and sa.cust_account_id = c.cust_account_id
    and c.cust_account_id = cp.cust_account_id(+)
    and cp.site_use_id is null
    and c.party_id = party.party_id
    and ua.org_id = l_org_id
    and c.account_number = l_customer_number;

2、更新ar_cash_receipts_all 表

 update ar_cash_receipts_all a
    set a.pay_from_customer    = l_cust_account_id,
        a.customer_site_use_id = l_site_user_id
  where a.cash_receipt_id = l_cash_receipt_id
    and a.pay_from_customer = l_customer_id;

3、获取 ar_distributions_all表

 update ar_distributions_all d
    set d.third_party_id     = l_cust_account_id,
        d.third_party_sub_id = l_site_user_id
  where d.third_party_id = l_customer_id
    and exists
  (select 1
           from ar_receivable_applications_all araa
          where araa.receivable_application_id = d.source_id
            and to_char(araa.creation_date, 'YYYY-MM-DD') =
                to_char(d.creation_date, 'YYYY-MM-DD')
            and araa.cash_receipt_id = l_cash_receipt_id);
 
 update ar_distributions_all d
    set d.third_party_id     = l_cust_account_id,
        d.third_party_sub_id = l_site_user_id
  where d.third_party_id = l_customer_id
    and exists
  (select 1
           from ar_receivable_applications_all araa
          where araa.cash_receipt_history_id = d.source_id
            and to_char(araa.creation_date, 'YYYY-MM-DD') =
                to_char(d.creation_date, 'YYYY-MM-DD')
            and araa.cash_receipt_id = l_cash_receipt_id);

4：收款更新GL日期

    收款GL日期涉及以下表：

    ar_cash_receipts_all、ar_cash_receipt_history_all、ar_payment_schedules_all、ar_receivable_applications_all、xla_events

1：更新 ar_cash_receipts_all 表

         update ar_cash_receipts_all acra
            set acra.receipt_date              = l_receipt_date,
                acra.anticipated_clearing_date = l_receipt_date,
                acra.deposit_date              = l_receipt_date
          where acra.cash_receipt_id = l_cash_receipt_id;

2：更新 ar_cash_receipt_history_all表

 update ar_cash_receipt_history_all acha
    set acha.trx_date = l_receipt_date, acha.gl_date = l_receipt_date
  where acha.cash_receipt_id = l_cash_receipt_id;

3：更新 ar_payment_schedules_all 表

update ar_payment_schedules_all apsa
   set apsa.actual_date_closed = l_receipt_date,
       apsa.trx_date           = l_receipt_date,
       apsa.gl_date_closed     = l_receipt_date,
       apsa.gl_date            = l_receipt_date
 where apsa.cash_receipt_id = l_cash_receipt_id;

4：更新 ar_receivable_applications_all 表

 update ar_receivable_applications_all araa
    set araa.apply_date = l_receipt_date, araa.gl_date = l_receipt_date
 
  where araa.cash_receipt_id = l_cash_receipt_id;

5：更新 xla_events表

 update xla_events xe
    set xe.transaction_date = l_receipt_date,
        xe.event_date       = l_receipt_date
  where exists
  (select 1
           from ar_cash_receipts_all acra, ar_cash_receipt_history_all acha
          where xe.event_id = acha.event_id
            and acra.cash_receipt_id = acha.cash_receipt_id
            and acra.cash_receipt_id = l_cash_receipt_id);

5：收款更新收款编号

     收款编号涉及：ar_cash_receipts_all、xla.xla_transaction_entities、ar_payment_schedules_all

 update ar_cash_receipts_all acra
    set acra.receipt_number = l_receipt_number
  where acra.cash_receipt_id = l_cash_receipt_id;
 

 update xla.xla_transaction_entities xte
    set xte.transaction_number = l_receipt_number
  where xte.entity_code = 'RECEIPTS'
    and xte.source_id_int_1 = l_cash_receipt_id;
 

 update ar_payment_schedules_all apsa
    set apsa.trx_number = l_receipt_number
  where apsa.cash_receipt_id = l_cash_receipt_id;