--1.0 生成与发票一样的贷项通知单 created by jenrry 20170423

DECLARE

  L_CUSTOMER_TRX_ID      NUMBER;

  L_INVOICE_NUMBER       VARCHAR2(30);

  L_BATCH_SOURCE_REC     AR_INVOICE_API_PUB.BATCH_SOURCE_REC_TYPE;

  L_TRX_HEADER_TBL       AR_INVOICE_API_PUB.TRX_HEADER_TBL_TYPE;

  L_TRX_LINES_TBL        AR_INVOICE_API_PUB.TRX_LINE_TBL_TYPE;

  L_TRX_DIST_TBL         AR_INVOICE_API_PUB.TRX_DIST_TBL_TYPE;

  L_TRX_SALESCREDITS_TBL AR_INVOICE_API_PUB.TRX_SALESCREDITS_TBL_TYPE;

  L_RETURN_STATUS    VARCHAR2(100);

  L_MSG_COUNT        NUMBER;

  L_MSG_DATA         VARCHAR2(2000);

  I                  NUMBER;

  L_MESSAGE_LIST     VARCHAR2(1000);

  L_MSG_INDEX_OUT    NUMBER;

BEGIN

  FND_GLOBAL.APPS_INITIALIZE(USER_ID      => 1111,

                             RESP_ID      => 50737,

                             RESP_APPL_ID => 20005);

  mo_global.init('AR');

  SELECT ra_customer_trx_s.nextval INTO I FROM DUAL;

  mo_global.set_policy_context(p_access_mode => 'S', p_org_id => 142);

  L_BATCH_SOURCE_REC.BATCH_SOURCE_ID      := 1001; --事务处理来源ID

  L_TRX_HEADER_TBL(1).TRX_HEADER_ID       := I;

  L_TRX_HEADER_TBL(1).TRX_DATE            := fnd_conc_date.string_to_date('2017-03-31');

  L_TRX_HEADER_TBL(1).GL_DATE             := fnd_conc_date.string_to_date('2017-03-31');

  L_TRX_HEADER_TBL(1).TRX_CURRENCY        := 'CNY';

  --L_TRX_HEADER_TBL(1).EXCHANGE_RATE_TYPE  := 'Corporate';

  --L_TRX_HEADER_TBL(1).exchange_date       := SYSDATE+1;

  --L_TRX_HEADER_TBL(1).EXCHANGE_RATE       := 7;

  L_TRX_HEADER_TBL(1).trx_number            := '20170425002'; --创建的贷项通知单的trx_number

  L_TRX_HEADER_TBL(1).BILL_TO_CUSTOMER_ID := 1040; --客户

  L_TRX_HEADER_TBL(1).CUST_TRX_TYPE_ID    := 1001; --创建贷项通知单的类型

  L_TRX_HEADER_TBL(1).TERM_ID             := NULL;

  L_TRX_LINES_TBL(1).TRX_HEADER_ID        := I;

  L_TRX_LINES_TBL(1).TRX_LINE_ID          := ra_customer_trx_lines_s.nextval;

  L_TRX_LINES_TBL(1).LINE_NUMBER          := 1; --行

  L_TRX_LINES_TBL(1).DESCRIPTION          := '从高级全球公司间系统导入的发票';--行项目上的摘要

  L_TRX_LINES_TBL(1).QUANTITY_INVOICED    := 11; --开票数量

  L_TRX_LINES_TBL(1).UNIT_SELLING_PRICE   := -11; --开票单单价

  L_TRX_LINES_TBL(1).LINE_TYPE            := 'LINE';

  L_TRX_DIST_TBL(1).TRX_DIST_ID           := ra_cust_trx_line_gl_dist_s.nextval;

  L_TRX_DIST_TBL(1).TRX_HEADER_ID         := I;

  L_TRX_DIST_TBL(1).TRX_LINE_ID           := NULL; --账户类别=“应收账款”时，分配行上不能有TRX_LINE_ID

  L_TRX_DIST_TBL(1).ACCOUNT_CLASS         := 'REV';

  L_TRX_DIST_TBL(1).AMOUNT                := NULL;

  L_TRX_DIST_TBL(1).ACCTD_AMOUNT          := NULL;

  L_TRX_DIST_TBL(1).PERCENT               := 100; --指百分之百的核销

  L_TRX_DIST_TBL(1).CODE_COMBINATION_ID   := 3010; --应收账款的CCID

   L_TRX_DIST_TBL(2).TRX_DIST_ID           := ra_cust_trx_line_gl_dist_s.nextval;

  L_TRX_DIST_TBL(2).TRX_HEADER_ID         := I;

  --L_TRX_DIST_TBL(1).TRX_LINE_ID           := 100;

  L_TRX_DIST_TBL(2).ACCOUNT_CLASS         := 'REC';

  L_TRX_DIST_TBL(2).AMOUNT                := NULL;

  L_TRX_DIST_TBL(2).ACCTD_AMOUNT          := NULL;

  L_TRX_DIST_TBL(2).PERCENT               := 100;

  L_TRX_DIST_TBL(2).CODE_COMBINATION_ID   := 3004;--收入的CCID

  -- mo_global.set_policy_context('S', fnd_global.org_id);  --R12要单OU

  AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE(

                                P_API_VERSION          => 1.0

                               ,P_INIT_MSG_LIST        => 'F'

                               ,P_COMMIT               => 'F'

                               ,P_BATCH_SOURCE_REC     => L_BATCH_SOURCE_REC

                               ,P_TRX_HEADER_TBL       => L_TRX_HEADER_TBL

                               ,P_TRX_LINES_TBL        => L_TRX_LINES_TBL

                               ,P_TRX_DIST_TBL         => L_TRX_DIST_TBL

                               ,P_TRX_SALESCREDITS_TBL => L_TRX_SALESCREDITS_TBL

                               ,X_CUSTOMER_TRX_ID      => L_CUSTOMER_TRX_ID

                               ,X_RETURN_STATUS        => L_RETURN_STATUS

                               ,X_MSG_COUNT            => L_MSG_COUNT

                               ,X_MSG_DATA             => L_MSG_DATA);

  IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR OR L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN

    DBMS_OUTPUT.PUT_LINE('UNEXPECTED ERRORS FOUND!');

  /*ELSE

      DBMS_OUTPUT.PUT_LINE(L_CUSTOMER_TRX_ID);

      SELECT TRX_NUMBER

      INTO L_INVOICE_NUMBER

      FROM RA_CUSTOMER_TRX_ALL

      WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID;

      DBMS_OUTPUT.PUT_LINE(L_INVOICE_NUMBER);*/

  END IF;

  DBMS_OUTPUT.PUT_LINE(L_RETURN_STATUS);

  FOR l_r IN (SELECT * FROM ar_trx_errors_gt)

  LOOP

    DBMS_OUTPUT.PUT_LINE(l_r.error_message);

  END LOOP;

END;

/

--2.0 贷项通知单和发票的核销

DECLARE

  l_cm_app_rec                ar_cm_api_pub.cm_app_rec_type;

  l_out_rec_application_id    NUMBER;

  l_acctd_amount_applied_from NUMBER;

  l_acctd_amount_applied_to   NUMBER;

  l_return_status varchar2(1);

  l_msg_count number;

  l_msg_data varchar2(1000);

BEGIN

  FND_GLOBAL.APPS_INITIALIZE(USER_ID      => 1111,

                             RESP_ID      => 50737,

                             RESP_APPL_ID => 20005);

  mo_global.init('AR');

  mo_global.set_policy_context(p_access_mode => 'S', p_org_id => 142);

  l_cm_app_rec.cm_customer_trx_id  := 3019;--贷项通知单customer_trx_id

  l_cm_app_rec.inv_customer_trx_id := 1001;--发票customer_trx_id

  l_cm_app_rec.amount_applied := 121;--金额

  --inv_line_number ra_customer_trx_lines.line_number%TYPE

  ar_cm_api_pub.apply_on_account(p_api_version               => 1,

                                 p_init_msg_list             => fnd_api.g_true,

                                 p_commit                    => fnd_api.g_false,

                                 p_cm_app_rec                => l_cm_app_rec,

                                 x_return_status             => l_return_status,

                                 x_msg_count                 => l_msg_count,

                                 x_msg_data                  => l_msg_data,

                                 x_out_rec_application_id    => l_out_rec_application_id,

                                 x_acctd_amount_applied_from => l_acctd_amount_applied_from,

                                 x_acctd_amount_applied_to   => l_acctd_amount_applied_to,

                                 p_org_id                    => 142);

  dbms_output.put_line('l_return_status:' || l_return_status);

  dbms_output.put_line('l_msg_count:' || l_msg_count);

  dbms_output.put_line('l_out_rec_application_id:' || l_out_rec_application_id);

END;