AR - 收款创建、冲销、删除【标准API】

收款创建API

ar_receipt_api_pub.create_cash

以下部分关键代码，仅供学习参考

   begin
   
      v_user_id := fnd_global.user_id;
   

      
         v_currency                  := 币种
         v_receipt_number            := 收款编号;
         v_amount                    := 收款金额;
         v_gl_date                   := GL日期;
         v_account_number            := 客户编号;
         v_receitp_method            := 收款方法名;
         g_curreny                   := 币种;

      
         v_org_id := imp_rpt.org_id;
      
         ---- 获取汇率日期
         begin
            select last_day(add_months(v_gl_date, -1))
              into v_conversion_date
              from dual;
         
            select r.exchange_date
              into v_conversion_date
              from ra_customer_trx_all r
             where r.trx_number = v_receipt_number;
         
         exception
            when others then
               v_return_status := 'E';
               v_error_message := '获取汇率日期异常' || sqlerrm;
               raise fnd_api.g_exc_error;
         end;
      
         ---- 获取汇率
      
         begin
         
            select g.currency_code
              into v_b_currency
              from hr_operating_units h, gl_sets_of_books g
             where h.organization_id = v_org_id
               and h.set_of_books_id = g.set_of_books_id;
         
            if v_b_currency <> g_curreny then
               select v.conversion_rate, v.user_conversion_type
                 into v_conversion_rate, v_rate_type
                 from gl_daily_rates_v v
                where v.from_currency = g_curreny
                  and v.to_currency = v_b_currency
                  and v.user_conversion_type = 'Corporate'
                  and v.conversion_date = v_conversion_date;
            else
               v_rate_type       := null;
               v_conversion_date := null;
            
            end if;
         
         exception
            when others then
               v_return_status := 'E';
               v_error_message := '获取汇率异常' || sqlerrm;
               goto process_not_cny_end;
         end;
      
         ---- 收款客户信息
         begin
            select s.site_use_id     p_customer_site_use_id,
                   s.location        p_location,
                   c.cust_account_id p_customer_site_use_id
              into v_customer_site_use_id, v_location, v_cust_account_id
              from hz_cust_site_uses_all  s,
                   hz_cust_acct_sites_all c,
                   ar_customers           a
             where s.cust_acct_site_id = c.cust_acct_site_id
               and s.status = 'A'
               and c.status = 'A'
               and c.cust_account_id = a.customer_id
               and a.customer_number = v_account_number
               and c.org_id = v_org_id;
         exception
            when others then
               v_return_status := 'E';
               v_error_message := '获取客户信息异常' || sqlerrm;
               goto process_not_cny_end;
         end;
      
         ---- 银行账户信息
         begin
            select u.bank_acct_use_id  p_remittance_bank_account_id,
                   r.receipt_method_id p_receipt_method_id,
                   r.name              p_receipt_method_name
              into v_remittance_bank_account_id,
                   v_receipt_method_id,
                   v_receipt_method_name
              from ce_bank_acct_uses_all          u,
                   ce_bank_accounts               c,
                   ar_receipt_method_accounts_all p,
                   ar_receipt_methods             r
             where r.name = v_receitp_method
               and sysdate between nvl(p.start_date, sysdate) and
                   nvl(p.end_date, sysdate)
               and r.receipt_method_id = p.receipt_method_id
               and p.remit_bank_acct_use_id = u.bank_acct_use_id
               and c.bank_account_id = u.bank_account_id
               and c.currency_code = g_curreny
               and u.org_id = v_org_id;
         exception
            when others then
               v_return_status := 'E';
               v_error_message := '收款方法异常' || sqlerrm;
               goto process_not_cny_end;
         end;
      
         mo_global.set_policy_context('S', v_org_id);
         ar_receipt_api_pub.create_cash(p_api_version                  => '1.0',
                                        p_init_msg_list                => fnd_api.g_false,
                                        p_commit                       => fnd_api.g_true,
                                        p_validation_level             => fnd_api.g_valid_level_full,
                                        x_return_status                => v_return_status,
                                        x_msg_count                    => v_msg_count,
                                        x_msg_data                     => v_error_message,
                                        p_usr_currency_code            => null,
                                        p_currency_code                => g_curreny,
                                        p_usr_exchange_rate_type       => null,
                                        p_exchange_rate_type           => v_rate_type, --'Corporate',
                                        p_exchange_rate                => null,
                                        p_exchange_rate_date           => v_conversion_date,
                                        p_amount                       => v_amount,
                                        p_factor_discount_amount       => null, --0,
                                        p_receipt_number               => v_receipt_number,
                                        p_receipt_date                 => v_gl_date,
                                        p_gl_date                      => v_gl_date,
                                        p_maturity_date                => null,
                                        p_postmark_date                => null,
                                        p_customer_id                  => v_cust_account_id,
                                        p_customer_name                => null,
                                        p_customer_number              => null,
                                        p_customer_bank_account_id     => null,
                                        p_customer_bank_account_num    => null,
                                        p_customer_bank_account_name   => null,
                                        p_payment_trxn_extension_id    => null,
                                        p_location                     => v_location,
                                        p_customer_site_use_id         => v_customer_site_use_id,
                                        p_default_site_use             => null,
                                        p_customer_receipt_reference   => null,
                                        p_override_remit_account_flag  => 'N',
                                        p_remittance_bank_account_id   => v_remittance_bank_account_id,
                                        p_remittance_bank_account_num  => null,
                                        p_remittance_bank_account_name => null,
                                        p_deposit_date                 => null, --SYSDATE,
                                        p_receipt_method_id            => v_receipt_method_id,
                                        p_receipt_method_name          => v_receipt_method_name,
                                        p_doc_sequence_value           => null,
                                        p_ussgl_transaction_code       => null,
                                        p_anticipated_clearing_date    => null,
                                        p_called_from                  => null,
                                        p_attribute_rec                => v_attribute_type,
                                        p_global_attribute_rec         => null,
                                        p_comments                     => null,
                                        p_issuer_name                  => null,
                                        p_issue_date                   => null,
                                        p_issuer_bank_branch_id        => null,
                                        p_org_id                       => v_org_id,
                                        p_installment                  => null,
                                        p_cr_id                        => v_cr_id);
         out_put('v_return_status=' || v_return_status);
         out_put('v_error_message=' || v_error_message);
exception
  when others then
    null;
end ;

 

收款冲销API:

         mo_global.set_policy_context('S', l_org_id);
         ar_receipt_api_pub.reverse(p_api_version            => 1.0,
                                    p_init_msg_list          => fnd_api.g_true,
                                    p_commit                 => fnd_api.g_true,
                                    p_validation_level       => fnd_api.g_valid_level_full,
                                    x_return_status          => l_return_status,
                                    x_msg_count              => l_msg_count,
                                    x_msg_data               => l_msg_data,
                                    p_cash_receipt_id        => l_cash_receipt_id,
                                    p_reversal_gl_date       => l_stop_gl_date,
                                    p_reversal_date          => l_stop_gl_date,
                                    p_org_id                 => l_org_id,
                                    p_reversal_category_code => l_reversal_category_code,
                                    p_reversal_reason_code   => l_reversal_reason_code,
                                    p_reversal_reason_name   => l_reversal_reason_name);

收款删除API（非标准）:

收款删除没有标准的API，但我依稀摸索出需要删除哪些表；

删除条件（必须是单一的，独立的收退款）未传送至总账且未核销过的收退款；

需要删除以下表：

xla_ae_headers(如果已创建会计科目但未传总账）
gl_import_references(如果已创建会计科目但未传总账）
xla_ae_lines(如果已创建会计科目但未传总账）
xla_events
xla.xla_transaction_entities
ar_distributions_all
ar_receivable_applications_all
ar_cash_receipt_history_all
ar_payment_schedules_all
ar_cash_receipts_all
ar_trx_summary_hist

AR_MISC_CASH_DISTRIBUTIONS_ALL（杂项）