Copy AR invoice and import to interface, generated CM invoice

CREATE OR REPLACE PACKAGE BODY ARXKTRCCM_BG_1 AS

  PROCEDURE Copy_Transactions(ERRBUF         OUT varchar2,
                              RETCODE        OUT number,
                              p_org_id       IN NUMBER,
                              p_trx_num_from IN VARCHAR2,
                              p_trx_num_to   IN VARCHAR2) is
    CURSOR c_trx is
      select trx.*,
             rs.name soure_name,
             typ.type,
             typ.name trx_type_name,
             dist.gl_date,
             0 open_amt
        from ra_cust_trx_types_all        typ,
             ra_customer_trx_all          trx,
             ra_batch_sources_all         rs,
             ra_cust_trx_line_gl_dist_all dist
       where 1 = 1
         and rs.batch_source_id = trx.batch_source_id
         ---and dist.gl_posted_date is not null
         and dist.customer_trx_id = trx.customer_trx_id
         and dist.account_class = 'REC'
         and dist.latest_rec_flag = 'Y'
         and dist.account_set_flag = 'N'
         and trx.complete_flag = 'Y'
         and typ.cust_trx_type_id = trx.cust_trx_type_id
         and typ.type != 'CM'
         and typ.name='INV(BY OE)-JIT'
         and trx.trx_number between nvl(p_trx_num_from, trx.trx_number) and
             nvl(p_trx_num_to, trx.trx_number)
         and trx.org_id = p_org_id
         and nvl(TRX.Attribute1,'N')!='Y'; --prevent duplicate import
 
    rec               c_trx%ROWTYPE;
    rec_line          ra_customer_trx_lines_all%rowtype;
    rec_salesreps     ra_cust_trx_line_salesreps_all%rowtype;
    v_cnt             number := 0;
    v_err_cnt         number := 0;
    v_customer_trx_id number;
 
    v_batch_source_id   ra_batch_sources_all.batch_source_id%TYPE;
    v_batch_source_name ra_batch_sources_all.name%TYPE;
    v_new_trx_type_id   ra_cust_trx_types_all.cust_trx_type_id%TYPE;
    v_new_trx_type_name ra_cust_trx_types_all.name%TYPE;
 
    v_bill_to_addr_id hz_cust_site_uses_all.cust_acct_site_id%TYPE;
    v_ship_to_addr_id hz_cust_site_uses_all.cust_acct_site_id%TYPE;
 
    cursor c_line is
      select *
        from ra_customer_trx_lines_all
       where customer_trx_id = v_customer_trx_id
       and line_type = 'LINE'
       order by line_number;
 
  BEGIN
 
    --Retrieve Batch Source?
    BEGIN
      select batch_source_id, name
        into v_batch_source_id, v_batch_source_name
        from ra_batch_sources_all
       where org_id = p_org_id
         and name = 'EXPORT JIT HUB';
   
    EXCEPTION
      WHEN OTHERS THEN
        ERRBUF := 'Error when get Transaction Source: ' || sqlerrm;
        GOTO END_PROC;
    END;
 
    --Retrieve Customer Trx Type ID
    BEGIN
      select cust_trx_type_id, name
        into v_new_trx_type_id, v_new_trx_type_name
        from ra_cust_trx_types_all
       where type = 'CM'
         and name = 'CM-OVERSEAS-JIT' --CM-OVERSEAS-JIT
         and org_id = p_org_id;
    EXCEPTION
      WHEN OTHERS THEN
        ERRBUF := 'Error when get Transaction Type: ' || sqlerrm;
        GOTO END_PROC;
    END;
    ----
   
    OPEN c_trx;
    LOOP
      FETCH c_trx
        INTO rec;
      EXIT WHEN c_trx%NOTFOUND;
   
      --Retrieve Bill to/Ship to Address ID
      BEGIN
        select cust_acct_site_id
          into v_bill_to_addr_id
          from hz_cust_site_uses_all
         where site_use_id = rec.BILL_TO_SITE_USE_ID
           and org_id = p_org_id
           and site_use_code = 'BILL_TO';
     
        begin
          select cust_acct_site_id
            into v_ship_to_addr_id
            from hz_cust_site_uses_all
           where site_use_id = rec.SHIP_TO_SITE_USE_ID
             and org_id = p_org_id
             and site_use_code = 'SHIP_TO';
        exception
          when OTHERS then
            v_ship_to_addr_id := to_number(null);
        end;
     
      EXCEPTION
        WHEN OTHERS THEN
          rollback;
          ERRBUF    := ERRBUF || CHR(10) ||
                       'Error when copy BILL/SHIP TO Address ID: ' ||
                       rec.trx_number || ': ' || sqlerrm;
          v_err_cnt := v_err_cnt + 1;
          GOTO NEXT_REC;
      END;
     
     v_customer_trx_id := rec.customer_trx_id;
    open c_line;
        loop
          fetch c_line
            into rec_line;
          EXIT WHEN c_line%NOTFOUND;
      BEGIN
      
        --insert RA_INTERFACE_LINES_ALL
        INSERT INTO RA_INTERFACE_LINES_ALL
          (INTERFACE_LINE_ID,
           INTERFACE_LINE_CONTEXT,
           INTERFACE_LINE_ATTRIBUTE1,
           INTERFACE_LINE_ATTRIBUTE2,
           INTERFACE_LINE_ATTRIBUTE3,
           INTERFACE_LINE_ATTRIBUTE4,
           INTERFACE_LINE_ATTRIBUTE5,
           INTERFACE_LINE_ATTRIBUTE6,
           INTERFACE_LINE_ATTRIBUTE7,
           INTERFACE_LINE_ATTRIBUTE8,
           BATCH_SOURCE_NAME,
           SET_OF_BOOKS_ID,
           LINE_TYPE,
           DESCRIPTION,
           CURRENCY_CODE,
           AMOUNT,
           CUST_TRX_TYPE_NAME,
           CUST_TRX_TYPE_ID,
           TERM_NAME,
           TERM_ID,
           ORIG_SYSTEM_BATCH_NAME,
           ORIG_SYSTEM_BILL_CUSTOMER_REF,
           ORIG_SYSTEM_BILL_CUSTOMER_ID,
           ORIG_SYSTEM_BILL_ADDRESS_REF,
           ORIG_SYSTEM_BILL_ADDRESS_ID,
           ORIG_SYSTEM_BILL_CONTACT_REF,
           ORIG_SYSTEM_BILL_CONTACT_ID,
           ORIG_SYSTEM_SHIP_CUSTOMER_REF,
           ORIG_SYSTEM_SHIP_CUSTOMER_ID,
           ORIG_SYSTEM_SHIP_ADDRESS_REF,
           ORIG_SYSTEM_SHIP_ADDRESS_ID,
           ORIG_SYSTEM_SHIP_CONTACT_REF,
           ORIG_SYSTEM_SHIP_CONTACT_ID,
           ORIG_SYSTEM_SOLD_CUSTOMER_REF,
           ORIG_SYSTEM_SOLD_CUSTOMER_ID,
           LINK_TO_LINE_ID,
           LINK_TO_LINE_CONTEXT,
           LINK_TO_LINE_ATTRIBUTE1,
           LINK_TO_LINE_ATTRIBUTE2,
           LINK_TO_LINE_ATTRIBUTE3,
           LINK_TO_LINE_ATTRIBUTE4,
           LINK_TO_LINE_ATTRIBUTE5,
           LINK_TO_LINE_ATTRIBUTE6,
           LINK_TO_LINE_ATTRIBUTE7,
           RECEIPT_METHOD_NAME,
           RECEIPT_METHOD_ID,
           CONVERSION_TYPE,
           CONVERSION_DATE,
           CONVERSION_RATE,
           CUSTOMER_TRX_ID,
           TRX_DATE,
           GL_DATE,
           DOCUMENT_NUMBER,
           TRX_NUMBER,
           LINE_NUMBER,
           QUANTITY,
           QUANTITY_ORDERED,
           UNIT_SELLING_PRICE,
           UNIT_STANDARD_PRICE,
           PRINTING_OPTION,
           INTERFACE_STATUS,
           REQUEST_ID,
           RELATED_BATCH_SOURCE_NAME,
           RELATED_TRX_NUMBER,
           RELATED_CUSTOMER_TRX_ID,
           PREVIOUS_CUSTOMER_TRX_ID,
           CREDIT_METHOD_FOR_ACCT_RULE,
           CREDIT_METHOD_FOR_INSTALLMENTS,
           REASON_CODE,
           TAX_RATE,
           TAX_CODE,
           TAX_PRECEDENCE,
           EXCEPTION_ID,
           EXEMPTION_ID,
           SHIP_DATE_ACTUAL,
           FOB_POINT,
           SHIP_VIA,
           WAYBILL_NUMBER,
           INVOICING_RULE_NAME,
           INVOICING_RULE_ID,
           ACCOUNTING_RULE_NAME,
           ACCOUNTING_RULE_ID,
           ACCOUNTING_RULE_DURATION,
           RULE_START_DATE,
           PRIMARY_SALESREP_NUMBER,
           PRIMARY_SALESREP_ID,
           SALES_ORDER,
           SALES_ORDER_LINE,
           SALES_ORDER_DATE,
           SALES_ORDER_SOURCE,
           SALES_ORDER_REVISION,
           PURCHASE_ORDER,
           PURCHASE_ORDER_REVISION,
           PURCHASE_ORDER_DATE,
           AGREEMENT_NAME,
           AGREEMENT_ID,
           MEMO_LINE_NAME,
           MEMO_LINE_ID,
           INVENTORY_ITEM_ID,
           MTL_SYSTEM_ITEMS_SEG1,
           MTL_SYSTEM_ITEMS_SEG2,
           MTL_SYSTEM_ITEMS_SEG3,
           MTL_SYSTEM_ITEMS_SEG4,
           MTL_SYSTEM_ITEMS_SEG5,
           MTL_SYSTEM_ITEMS_SEG6,
           MTL_SYSTEM_ITEMS_SEG7,
           MTL_SYSTEM_ITEMS_SEG8,
           MTL_SYSTEM_ITEMS_SEG9,
           MTL_SYSTEM_ITEMS_SEG10,
           MTL_SYSTEM_ITEMS_SEG11,
           MTL_SYSTEM_ITEMS_SEG12,
           MTL_SYSTEM_ITEMS_SEG13,
           MTL_SYSTEM_ITEMS_SEG14,
           MTL_SYSTEM_ITEMS_SEG15,
           MTL_SYSTEM_ITEMS_SEG16,
           MTL_SYSTEM_ITEMS_SEG17,
           MTL_SYSTEM_ITEMS_SEG18,
           MTL_SYSTEM_ITEMS_SEG19,
           MTL_SYSTEM_ITEMS_SEG20,
           REFERENCE_LINE_ID,
           REFERENCE_LINE_CONTEXT,
           REFERENCE_LINE_ATTRIBUTE1,
           REFERENCE_LINE_ATTRIBUTE2,
           REFERENCE_LINE_ATTRIBUTE3,
           REFERENCE_LINE_ATTRIBUTE4,
           REFERENCE_LINE_ATTRIBUTE5,
           REFERENCE_LINE_ATTRIBUTE6,
           REFERENCE_LINE_ATTRIBUTE7,
           TERRITORY_ID,
           TERRITORY_SEGMENT1,
           TERRITORY_SEGMENT2,
           TERRITORY_SEGMENT3,
           TERRITORY_SEGMENT4,
           TERRITORY_SEGMENT5,
           TERRITORY_SEGMENT6,
           TERRITORY_SEGMENT7,
           TERRITORY_SEGMENT8,
           TERRITORY_SEGMENT9,
           TERRITORY_SEGMENT10,
           TERRITORY_SEGMENT11,
           TERRITORY_SEGMENT12,
           TERRITORY_SEGMENT13,
           TERRITORY_SEGMENT14,
           TERRITORY_SEGMENT15,
           TERRITORY_SEGMENT16,
           TERRITORY_SEGMENT17,
           TERRITORY_SEGMENT18,
           TERRITORY_SEGMENT19,
           TERRITORY_SEGMENT20,
           ATTRIBUTE_CATEGORY,
           ATTRIBUTE1,
           ATTRIBUTE2,
           ATTRIBUTE3,
           ATTRIBUTE4,
           ATTRIBUTE5,
           ATTRIBUTE6,
           ATTRIBUTE7,
           ATTRIBUTE8,
           ATTRIBUTE9,
           ATTRIBUTE10,
           ATTRIBUTE11,
           ATTRIBUTE12,
           ATTRIBUTE13,
           ATTRIBUTE14,
           ATTRIBUTE15,
           HEADER_ATTRIBUTE_CATEGORY,
           HEADER_ATTRIBUTE1,
           HEADER_ATTRIBUTE2,
           HEADER_ATTRIBUTE3,
           HEADER_ATTRIBUTE4,
           HEADER_ATTRIBUTE5,
           HEADER_ATTRIBUTE6,
           HEADER_ATTRIBUTE7,
           HEADER_ATTRIBUTE8,
           HEADER_ATTRIBUTE9,
           HEADER_ATTRIBUTE10,
           HEADER_ATTRIBUTE11,
           HEADER_ATTRIBUTE12,
           HEADER_ATTRIBUTE13,
           HEADER_ATTRIBUTE14,
           HEADER_ATTRIBUTE15,
           COMMENTS,
           INTERNAL_NOTES,
           INITIAL_CUSTOMER_TRX_ID,
           USSGL_TRANSACTION_CODE_CONTEXT,
           USSGL_TRANSACTION_CODE,
           ACCTD_AMOUNT,
           CUSTOMER_BANK_ACCOUNT_ID,
           CUSTOMER_BANK_ACCOUNT_NAME,
           UOM_CODE,
           UOM_NAME,
           DOCUMENT_NUMBER_SEQUENCE_ID,
           LINK_TO_LINE_ATTRIBUTE10,
           LINK_TO_LINE_ATTRIBUTE11,
           LINK_TO_LINE_ATTRIBUTE12,
           LINK_TO_LINE_ATTRIBUTE13,
           LINK_TO_LINE_ATTRIBUTE14,
           LINK_TO_LINE_ATTRIBUTE15,
           LINK_TO_LINE_ATTRIBUTE8,
           LINK_TO_LINE_ATTRIBUTE9,
           REFERENCE_LINE_ATTRIBUTE10,
           REFERENCE_LINE_ATTRIBUTE11,
           REFERENCE_LINE_ATTRIBUTE12,
           REFERENCE_LINE_ATTRIBUTE13,
           REFERENCE_LINE_ATTRIBUTE14,
           REFERENCE_LINE_ATTRIBUTE15,
           REFERENCE_LINE_ATTRIBUTE8,
           REFERENCE_LINE_ATTRIBUTE9,
           INTERFACE_LINE_ATTRIBUTE10,
           INTERFACE_LINE_ATTRIBUTE11,
           INTERFACE_LINE_ATTRIBUTE12,
           INTERFACE_LINE_ATTRIBUTE13,
           INTERFACE_LINE_ATTRIBUTE14,
           INTERFACE_LINE_ATTRIBUTE15,
           INTERFACE_LINE_ATTRIBUTE9,
           VAT_TAX_ID,
           REASON_CODE_MEANING,
           LAST_PERIOD_TO_CREDIT,
           PAYING_CUSTOMER_ID,
           PAYING_SITE_USE_ID,
           TAX_EXEMPT_FLAG,
           TAX_EXEMPT_REASON_CODE,
           TAX_EXEMPT_REASON_CODE_MEANING,
           TAX_EXEMPT_NUMBER,
           SALES_TAX_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_DATE,
           LAST_UPDATE_LOGIN,
           LOCATION_SEGMENT_ID,
           MOVEMENT_ID,
           ORG_ID,
           AMOUNT_INCLUDES_TAX_FLAG,
           HEADER_GDF_ATTR_CATEGORY,
           HEADER_GDF_ATTRIBUTE1,
           HEADER_GDF_ATTRIBUTE2,
           HEADER_GDF_ATTRIBUTE3,
           HEADER_GDF_ATTRIBUTE4,
           HEADER_GDF_ATTRIBUTE5,
           HEADER_GDF_ATTRIBUTE6,
           HEADER_GDF_ATTRIBUTE7,
           HEADER_GDF_ATTRIBUTE8,
           HEADER_GDF_ATTRIBUTE9,
           HEADER_GDF_ATTRIBUTE10,
           HEADER_GDF_ATTRIBUTE11,
           HEADER_GDF_ATTRIBUTE12,
           HEADER_GDF_ATTRIBUTE13,
           HEADER_GDF_ATTRIBUTE14,
           HEADER_GDF_ATTRIBUTE15,
           HEADER_GDF_ATTRIBUTE16,
           HEADER_GDF_ATTRIBUTE17,
           HEADER_GDF_ATTRIBUTE18,
           HEADER_GDF_ATTRIBUTE19,
           HEADER_GDF_ATTRIBUTE20,
           HEADER_GDF_ATTRIBUTE21,
           HEADER_GDF_ATTRIBUTE22,
           HEADER_GDF_ATTRIBUTE23,
           HEADER_GDF_ATTRIBUTE24,
           HEADER_GDF_ATTRIBUTE25,
           HEADER_GDF_ATTRIBUTE26,
           HEADER_GDF_ATTRIBUTE27,
           HEADER_GDF_ATTRIBUTE28,
           HEADER_GDF_ATTRIBUTE29,
           HEADER_GDF_ATTRIBUTE30,
           LINE_GDF_ATTR_CATEGORY,
           LINE_GDF_ATTRIBUTE1,
           LINE_GDF_ATTRIBUTE2,
           LINE_GDF_ATTRIBUTE3,
           LINE_GDF_ATTRIBUTE4,
           LINE_GDF_ATTRIBUTE5,
           LINE_GDF_ATTRIBUTE6,
           LINE_GDF_ATTRIBUTE7,
           LINE_GDF_ATTRIBUTE8,
           LINE_GDF_ATTRIBUTE9,
           LINE_GDF_ATTRIBUTE10,
           LINE_GDF_ATTRIBUTE11,
           LINE_GDF_ATTRIBUTE12,
           LINE_GDF_ATTRIBUTE13,
           LINE_GDF_ATTRIBUTE14,
           LINE_GDF_ATTRIBUTE15,
           LINE_GDF_ATTRIBUTE16,
           LINE_GDF_ATTRIBUTE17,
           LINE_GDF_ATTRIBUTE18,
           LINE_GDF_ATTRIBUTE19,
           LINE_GDF_ATTRIBUTE20,
           RESET_TRX_DATE_FLAG,
           PAYMENT_SERVER_ORDER_NUM,
           APPROVAL_CODE,
           ADDRESS_VERIFICATION_CODE,
           WAREHOUSE_ID,
           TRANSLATED_DESCRIPTION,
           CONS_BILLING_NUMBER,
           PROMISED_COMMITMENT_AMOUNT,
           PAYMENT_SET_ID,
           ORIGINAL_GL_DATE,
           CONTRACT_LINE_ID,
           CONTRACT_ID,
           SOURCE_DATA_KEY1,
           SOURCE_DATA_KEY2,
           SOURCE_DATA_KEY3,
           SOURCE_DATA_KEY4,
           SOURCE_DATA_KEY5,
           INVOICED_LINE_ACCTG_LEVEL)
        VALUES
          (null, --INTERFACE_LINE_ID
           nvl(rec_line.INTERFACE_LINE_CONTEXT, 'ORDER ENTRY'), --'ORDER ENTRY'
           trim(/*rec_line.INTERFACE_LINE_ATTRIBUTE1 || 'XX' ||*/rec.trx_number),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE2, 1),
           trim(/*rec_line.INTERFACE_LINE_ATTRIBUTE3 || 'XX' ||*/rec.trx_number),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE4, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE5, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE6, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE7, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE8, 1),
           v_batch_source_name, --rec.soure_name, --BATCH_SOURCE_NAME
           rec.set_of_books_id, --sob
           rec_line.line_type, --rec_line.line_type,v_batch_source_id ,v_batch_source_name
           nvl(rec_line.DESCRIPTION,v_batch_source_name), --v_batch_source_name,
           rec.INVOICE_CURRENCY_CODE,
           -rec_line.EXTENDED_AMOUNT, --AMOUNT
           v_new_trx_type_name, --rec.trx_type_name, --CUST_TRX_TYPE_NAME
           v_new_trx_type_id, --rec.cust_trx_type_id,--CUST_TRX_TYPE_ID
           null, --TERM_NAME
           null, --rec.TERM_ID,
           null, --ORIG_SYSTEM_BATCH_NAME
           null, --ORIG_SYSTEM_BILL_CUSTOMER_REF
           rec.BILL_TO_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_ID
           null, --ORIG_SYSTEM_BILL_ADDRESS_REF
           v_bill_to_addr_id, --ORIG_SYSTEM_BILL_ADDRESS_ID
           null, --ORIG_SYSTEM_BILL_CONTACT_REF
           rec.BILL_TO_CONTACT_ID, --ORIG_SYSTEM_BILL_CONTACT_ID
           null, --ORIG_SYSTEM_SHIP_CUSTOMER_REF
           rec.SHIP_TO_CUSTOMER_ID, --ORIG_SYSTEM_SHIP_CUSTOMER_ID
           null, --ORIG_SYSTEM_SHIP_ADDRESS_REF
           v_ship_to_addr_id, --ORIG_SYSTEM_SHIP_ADDRESS_ID
           null, --ORIG_SYSTEM_SHIP_CONTACT_REF
           rec.SHIP_TO_CONTACT_ID, --ORIG_SYSTEM_SHIP_CONTACT_ID
           null, --ORIG_SYSTEM_SOLD_CUSTOMER_REF
           rec.SOLD_TO_CUSTOMER_ID, --ORIG_SYSTEM_SOLD_CUSTOMER_ID
           null, --LINK_TO_LINE_ID
           null, --LINK_TO_LINE_CONTEXT
           null, --LINK_TO_LINE_ATTRIBUTE1
           null,
           null,
           null,
           null,
           null,
           null,
           null, --RECEIPT_METHOD_NAME
           rec.RECEIPT_METHOD_ID,
           nvl(rec.EXCHANGE_RATE_TYPE, 'User'), --CONVERSION_TYPE
           rec.EXCHANGE_DATE, --CONVERSION_DATE
           decode(nvl(rec.EXCHANGE_RATE_TYPE, 'User'),
                  'User',
                  nvl(rec.EXCHANGE_RATE, 1),
                  null), --CONVERSION_RATE
           rec.CUSTOMER_TRX_ID,
           rec.TRX_DATE,
           rec.gl_date, --GL_DATE
           rec.doc_sequence_value, --DOCUMENT_NUMBER
           null, --rec.TRX_NUMBER||'T',----
           rec_line.line_number, --line_number,
           -rec_line.QUANTITY_INVOICED, --nvl(rec_line.QUANTITY_INVOICED,rec_line.QUANTITY_CREDITED), --QUANTITY
           -rec_line.QUANTITY_ORDERED, --rec_line.QUANTITY_ORDERED, --QUANTITY_ORDERED
           rec_line.UNIT_SELLING_PRICE, --abs(rec_line.UNIT_SELLING_PRICE),
           rec_line.unit_standard_price, --abs(rec_line.UNIT_STANDARD_PRICE),
           rec.PRINTING_OPTION,
           null, --INTERFACE_STATUS
           NULL, --REQUEST_ID
           null, --RELATED_BATCH_SOURCE_NAME
           null, --RELATED_TRX_NUMBER
           rec.RELATED_CUSTOMER_TRX_ID,
           null, --PREVIOUS_CUSTOMER_TRX_ID,
           rec.CREDIT_METHOD_FOR_RULES,
           rec.CREDIT_METHOD_FOR_INSTALLMENTS,
           null, --rec.REASON_CODE,
          (select tax.tax_rate
          from Ar_Vat_Tax_All_b tax
         where tax.vat_tax_id =rec_line.vat_tax_id
           and tax.set_of_books_id = rec.set_of_books_id), --rec_line.TAX_RATE,
          
          (select tax.tax_code
          from Ar_Vat_Tax_All_b tax
         where tax.vat_tax_id =rec_line.vat_tax_id
           and tax.set_of_books_id =  rec.set_of_books_id), --TAX_CODE
           rec_line.TAX_PRECEDENCE,
           rec_line.ITEM_EXCEPTION_RATE_ID, --EXCEPTION_ID
           rec_line.TAX_EXEMPTION_ID, --EXEMPTION_ID
           rec.SHIP_DATE_ACTUAL,
           rec.FOB_POINT,
           rec.SHIP_VIA,
           rec.WAYBILL_NUMBER,
           null, --INVOICING_RULE_NAME
           rec.INVOICING_RULE_ID,
           null, --ACCOUNTING_RULE_NAME
           rec_line.ACCOUNTING_RULE_ID,
           rec_line.ACCOUNTING_RULE_DURATION,
           rec_line.RULE_START_DATE,
           (select salesrep_number
              from RA_SALESREPS_ALL
             where salesrep_id = -3
               and org_id = p_org_id), --PRIMARY_SALESREP_NUMBER
           rec.PRIMARY_SALESREP_ID,
           rec_line.SALES_ORDER,
           rec_line.SALES_ORDER_LINE,
           rec_line.SALES_ORDER_DATE,
           rec_line.SALES_ORDER_SOURCE,
           rec_line.SALES_ORDER_REVISION,
           rec.PURCHASE_ORDER,
           rec.PURCHASE_ORDER_REVISION,
           rec.PURCHASE_ORDER_DATE,
           null, --AGREEMENT_NAME
           rec.AGREEMENT_ID,
           null, --MEMO_LINE_NAME
           rec_line.MEMO_LINE_ID,
           null, --rec_line.INVENTORY_ITEM_ID,
           null, --MTL_SYSTEM_ITEMS_SEG1
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null, --REFERENCE_LINE_ID
           null, --REFERENCE_LINE_CONTEXT
           null, --REFERENCE_LINE_ATTRIBUTE1
           null,
           null,
           null,
           null,
           null,
           null,
           null, --TERRITORY_ID
           null, --TERRITORY_SEGMENT1
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           rec_line.ATTRIBUTE_CATEGORY, --Line
           rec_line.ATTRIBUTE1, --ATTRIBUTE1
           rec_line.ATTRIBUTE2,
           rec_line.ATTRIBUTE3,
           rec_line.ATTRIBUTE4,
           rec_line.ATTRIBUTE5,
           rec_line.ATTRIBUTE6,
           rec_line.ATTRIBUTE7,
           rec_line.ATTRIBUTE8,
           rec_line.ATTRIBUTE9,
           rec_line.ATTRIBUTE10,
           rec_line.ATTRIBUTE11,
           rec_line.ATTRIBUTE12,
           rec_line.ATTRIBUTE13,
           rec_line.ATTRIBUTE14,
           rec_line.ATTRIBUTE15,
           rec.ATTRIBUTE_CATEGORY, --Header
           rec.ATTRIBUTE1, --HEADER_ATTRIBUTE1
           rec.ATTRIBUTE2,
           rec.ATTRIBUTE3,
           rec.ATTRIBUTE4,
           rec.ATTRIBUTE5,
           rec.ATTRIBUTE6,
           rec.ATTRIBUTE7,
           rec.ATTRIBUTE8,
           rec.ATTRIBUTE9,
           rec.ATTRIBUTE10,
           rec.ATTRIBUTE11,
           rec.ATTRIBUTE12,
           rec.ATTRIBUTE13,
           rec.ATTRIBUTE14,
           rec.ATTRIBUTE15,
           rec.COMMENTS,
           rec.INTERNAL_NOTES,
           null, --INITIAL_CUSTOMER_TRX_ID,
           null, --USSGL_TRANSACTION_CODE_CONTEXT
           null, --USSGL_TRANSACTION_CODE
           null, --ACCTD_AMOUNT
           rec.CUSTOMER_BANK_ACCOUNT_ID, --rec.CUSTOMER_BANK_ACCOUNT_ID,
           (select bank_account_name
              from AP_BANK_ACCOUNTS_ALL
             where bank_account_id = rec.CUSTOMER_BANK_ACCOUNT_ID
               and org_id = p_org_id), --CUSTOMER_BANK_ACCOUNT_NAME
           rec_line.UOM_CODE,
           null, --UOM_NAME
           rec.DOC_SEQUENCE_ID,
           null, --LINK_TO_LINE_ATTRIBUTE10
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null, --REFERENCE_LINE_ATTRIBUTE10
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE10, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE11, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE12, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE13, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE14, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE15, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE9, 1),
          
           rec_line.vat_tax_id, --VAT_TAX_ID,
           null, --REASON_CODE_MEANING
           rec_line.LAST_PERIOD_TO_CREDIT,
           null, --rec.PAYING_CUSTOMER_ID,
           null, --rec.PAYING_SITE_USE_ID,
           rec_line.TAX_EXEMPT_FLAG,
           rec_line.TAX_EXEMPT_REASON_CODE,
           null, --TAX_EXEMPT_REASON_CODE_MEANING
           rec_line.TAX_EXEMPT_NUMBER,
           rec_line.SALES_TAX_ID,
           rec.CREATED_BY, --fnd_global.USER_ID
           rec.CREATION_DATE,
           rec.LAST_UPDATED_BY,
           rec.LAST_UPDATE_DATE,
           rec.LAST_UPDATE_LOGIN,
           rec_line.LOCATION_SEGMENT_ID,
           rec_line.MOVEMENT_ID,
           p_org_id, --ORG_ID
           rec_line.AMOUNT_INCLUDES_TAX_FLAG,
           null, --HEADER_GDF_ATTR_CATEGORY
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null,
           null, --LINE_GDF_ATTRIBUTE20
           null, --RESET_TRX_DATE_FLAG
           rec.PAYMENT_SERVER_ORDER_NUM,
           rec.APPROVAL_CODE,
           null, --ADDRESS_VERIFICATION_CODE
           rec_line.warehouse_id, --WAREHOUSE_ID
           rec_line.TRANSLATED_DESCRIPTION,
           null, --CONS_BILLING_NUMBER
           null, --PROMISED_COMMITMENT_AMOUNT
           rec_line.PAYMENT_SET_ID,
           null, --ORIGINAL_GL_DATE
           rec_line.CONTRACT_LINE_ID,
           rec.CONTRACT_ID,
           rec_line.SOURCE_DATA_KEY1,
           rec_line.SOURCE_DATA_KEY2,
           rec_line.SOURCE_DATA_KEY3,
           rec_line.SOURCE_DATA_KEY4,
           rec_line.SOURCE_DATA_KEY5,
           rec_line.INVOICED_LINE_ACCTG_LEVEL);
      EXCEPTION
        WHEN OTHERS THEN
          rollback;
          ERRBUF    := ERRBUF || CHR(10) || 'Error when copy AR Lines: ' ||
                       rec.trx_number || ': ' || sqlerrm;
          v_err_cnt := v_err_cnt + 1;
          GOTO NEXT_REC;
          dbms_output.put_line(ERRBUF);
      END;
   
      BEGIN
        --Retrieve sales credit records for each trx line
        --select * into rec_salesreps from ra_cust_trx_line_salesreps_all
        --where customer_trx_id = rec_line.customer_trx_id
        --and customer_trx_line_id = rec_line.customer_trx_line_id;
     
        --insert RA_INTERFACE_SALESCREDITS_ALL
        INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
          (INTERFACE_SALESCREDIT_ID,
           INTERFACE_LINE_ID,
           INTERFACE_LINE_CONTEXT,
           INTERFACE_LINE_ATTRIBUTE1,
           INTERFACE_LINE_ATTRIBUTE2,
           INTERFACE_LINE_ATTRIBUTE3,
           INTERFACE_LINE_ATTRIBUTE4,
           INTERFACE_LINE_ATTRIBUTE5,
           INTERFACE_LINE_ATTRIBUTE6,
           INTERFACE_LINE_ATTRIBUTE7,
           INTERFACE_LINE_ATTRIBUTE8,
           SALESREP_NUMBER,
           SALESREP_ID,
           SALES_CREDIT_TYPE_NAME,
           SALES_CREDIT_TYPE_ID,
           SALES_CREDIT_AMOUNT_SPLIT,
           SALES_CREDIT_PERCENT_SPLIT,
           INTERFACE_STATUS,
           REQUEST_ID,
           ATTRIBUTE_CATEGORY,
           ATTRIBUTE1,
           ATTRIBUTE2,
           ATTRIBUTE3,
           ATTRIBUTE4,
           ATTRIBUTE5,
           ATTRIBUTE6,
           ATTRIBUTE7,
           ATTRIBUTE8,
           ATTRIBUTE9,
           ATTRIBUTE10,
           ATTRIBUTE11,
           ATTRIBUTE12,
           ATTRIBUTE13,
           ATTRIBUTE14,
           ATTRIBUTE15,
           INTERFACE_LINE_ATTRIBUTE10,
           INTERFACE_LINE_ATTRIBUTE11,
           INTERFACE_LINE_ATTRIBUTE12,
           INTERFACE_LINE_ATTRIBUTE13,
           INTERFACE_LINE_ATTRIBUTE14,
           INTERFACE_LINE_ATTRIBUTE15,
           INTERFACE_LINE_ATTRIBUTE9,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_DATE,
           LAST_UPDATE_LOGIN,
           ORG_ID)
        VALUES
          (null, --INTERFACE_SALESCREDIT_ID
           null, --INTERFACE_LINE_ID
           nvl(rec_line.INTERFACE_LINE_CONTEXT, 'ORDER ENTRY'),
           trim(/*rec_line.INTERFACE_LINE_ATTRIBUTE1 || 'XX' || */rec.trx_number),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE2, 1),
           trim(/*rec_line.INTERFACE_LINE_ATTRIBUTE3 || 'XX' || */rec.trx_number),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE4, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE5, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE6, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE7, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE8, 1),
           (select salesrep_number
              from RA_SALESREPS_ALL
             where salesrep_id = -3
               and org_id = p_org_id), --SALESREP_NUMBER
           rec.PRIMARY_SALESREP_ID,
           null, --SALES_CREDIT_TYPE_NAME
           '1', --SALES_CREDIT_TYPE_ID
           null, --rec_salesreps.REVENUE_AMOUNT_SPLIT,--null
           100, --rec_salesreps.REVENUE_PERCENT_SPLIT,--100
           null, --INTERFACE_STATUS
           null, --REQUEST_ID
           null, --ATTRIBUTE_CATEGORY,
           null, --rec_salesreps.ATTRIBUTE1,
           null, --rec_salesreps.ATTRIBUTE2,
           null, --rec_salesreps.ATTRIBUTE3,
           null, --rec_salesreps.ATTRIBUTE4,
           null, --rec_salesreps.ATTRIBUTE5,
           null, --rec_salesreps.ATTRIBUTE6,
           null, --rec_salesreps.ATTRIBUTE7,
           null, --rec_salesreps.ATTRIBUTE8,
           null, --rec_salesreps.ATTRIBUTE9,
           null, --rec_salesreps.ATTRIBUTE10,
           null, --rec_salesreps.ATTRIBUTE11,
           null, --rec_salesreps.ATTRIBUTE12,
           null, --rec_salesreps.ATTRIBUTE13,
           null, --rec_salesreps.ATTRIBUTE14,
           null, --rec_salesreps.ATTRIBUTE15,
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE10, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE11, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE12, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE13, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE14, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE15, 1),
           nvl(rec_line.INTERFACE_LINE_ATTRIBUTE9, 1),
           fnd_global.USER_ID, --rec_salesreps.CREATED_BY,
           sysdate, --rec_salesreps.CREATION_DATE,
           fnd_global.USER_ID, --rec_salesreps.LAST_UPDATED_BY,
           sysdate, --rec_salesreps.LAST_UPDATE_DATE,
           null, --rec_salesreps.LAST_UPDATE_LOGIN,
           p_org_id);
      EXCEPTION
        WHEN OTHERS THEN
          rollback;
          ERRBUF    := ERRBUF || CHR(10) ||
                       'Error when copy AR sales credit Lines: ' ||
                       rec.trx_number || ': ' || sqlerrm;
          v_err_cnt := v_err_cnt + 1;
          GOTO NEXT_REC;
          dbms_output.put_line(ERRBUF);
      END;
      v_cnt := v_cnt + 1;
     END LOOP; 
      ---remarked the ar invoice was generated CM--    
      update RA_CUSTOMER_TRX_ALL TRX
         set TRX.Attribute1 = 'Y'
       WHERE 1 = 1
         and trx.org_id = p_org_id
         and trx.customer_trx_id = v_customer_trx_id; --
   
      commit;
   
      <>
      null;
    END LOOP; --rec
 
    <>
    ERRBUF  := ERRBUF || CHR(10) || 'Copy AR  Trx to Interface Cnt:' ||
               v_cnt;
    ERRBUF  := ERRBUF || CHR(10) || 'Copy AR  Trx to Interface Error Cnt:' ||
               v_err_cnt;
    RETCODE := 0;
    fnd_file.put_line(FND_FILE.LOG, RETCODE || ': ' || ERRBUF);
    dbms_output.put_line(ERRBUF);
  EXCEPTION
    WHEN OTHERS THEN
      rollback;
      ERRBUF  := ERRBUF || CHR(10) || 'Error when copy AR Trx to Interface';
      RETCODE := 1;
      dbms_output.put_line(ERRBUF);
  END;

END ARXKTRCCM_BG_1;
 

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

转载于:http://blog.itpub.net/9182041/viewspace-682151/