Oracle Apps R12 Function to get query rows in single variable

CREATE OR REPLACE function APPS.F_CONTACT_MSG(P_DAYS IN NUMBER)

return varchar2  is

CURSOR C1 IS 

SELECT 'TXN_NUMBER' , 'CUSTOMER_DETAILS' FROM DUAL

UNION ALL

SELECT TRX_NUMBER, PARTY_NAME||' '||END_DATE FROM F_AR_TRX_CUST_DETAILS_V

WHERE  END_DT = TRUNC(SYSDATE) + P_DAYS

;       

    

  M_LOCN_CODE        VARCHAR2(20000);

  M_STK_QTY_BU         VARCHAR2(20000);

  --M_REF_DEL_DT      OW_FS.DT%TYPE;

  M_TEMP1           NUMBER  := 0; 

  M_REF_CODE_NO     VARCHAR2(20000);

  

begin

    

    IF C1%ISOPEN THEN

        CLOSE C1;

    END IF;

    

    OPEN C1;

      LOOP

          FETCH C1 INTO M_LOCN_CODE, M_STK_QTY_BU;--, M_REF_DEL_DT;

          EXIT WHEN C1%NOTFOUND;

          M_TEMP1 := M_TEMP1 + 1;

       IF M_TEMP1 = 1 THEN

          M_REF_CODE_NO := M_LOCN_CODE||'     '||M_STK_QTY_BU;       

       ELSE

             M_REF_CODE_NO := M_REF_CODE_NO  || CHR(13)|| CHR(10)|| M_LOCN_CODE||'     '||M_STK_QTY_BU;

       END IF;

      END LOOP;

 

RETURN NVL(M_REF_CODE_NO,'');      

end;

/

USAGE :

In any block, define variable like below and use it.

L_RETURN VARCHAR2(2000):= F_CONTACT_MSG(P_DAYS);

Oracle Apps R12 API to create AR Receipt

CREATE OR REPLACE PROCEDURE APPS.XXCUS_CRT_ARRECEIPT   ( -- errbuff  OUT VARCHAR2
  --,retcode  OUT VARCHAR2
  P_CUSTOMER_ID         IN NUMBER
  ,p_amount            IN  NUMBER
  ,p_receipt_date      IN  DATE
  ,p_deposit_date      IN  DATE
  ,p_gl_date            IN  DATE
  ,p_maturity_date      IN  DATE
  ,p_remittance_bank_account_id    NUMBER --10003;  -- بنك الفجيره الوطنى
  /*
  select REMIT_BANK_ACCT_USE_ID from ar_receipt_method_accounts_all
                                        where org_id = 145
                                        and receipt_method_id = 7019
  */
  ,P_COMMENTS           IN  VARCHAR2
  --,p_customer_number        IN  VARCHAR2
  --,p_Cust_Name        IN OUT   VARCHAR2
  ,p_receipt_number    OUT   VARCHAR2
  ,W_MID     OUT VARCHAR2
  ,W_Status            OUT VARCHAR2
  ,W_Message            OUT VARCHAR2
 
)IS

  l_user_id                      NUMBER := 26051;
  l_resp_id                      NUMBER := 506011;
  l_resp_appl_id                 NUMBER := 222;
  l_org_id                       NUMBER := 1454;
  --l_receipt_method_id            NUMBER := 70197; --METHOD_NAME
  p_global_attribute_rec          AR_RECEIPT_API_PUB.global_attribute_rec_type;
                  p_attribute_rec                 AR_RECEIPT_API_PUB.attribute_rec_type;
 
    p_currency_code     VARCHAR2(100)                 := 'AED';
                  --  p_receipt_date      DATE               := TRUNC(SYSDATE);
                  --  p_gl_date            DATE               := TRUNC(SYSDATE);
                   -- p_deposit_date        DATE              := TRUNC(SYSDATE);
                    p_override_remit_account_flag   VARCHAR2(100)     := 'N';
                    --p_maturity_date         DATE            := TRUNC(SYSDATE);
                   -- p_comments                  VARCHAR2(100)         := 'FROM WS-4';
                 
                   ---------------LOCAL VARIABLES-----------
                   p_payroll_end_dt                date ;
        p_api_version                   NUMBER;
        p_init_msg_list                 VARCHAR2(240);
        p_commit                        VARCHAR2(240);
        p_validation_level              NUMBER;
        p_usr_currency_code             VARCHAR2(240);
        p_usr_exchange_rate_type        VARCHAR2(240);
        p_exchange_rate_type            VARCHAR2(240);
        p_exchange_rate                 NUMBER; 
        p_exchange_rate_date            DATE;
        p_factor_discount_amount        NUMBER;
        --p_receipt_date                  DATE;
        p_postmark_date                 DATE;
        p_customer_number               VARCHAR2(240); 
        p_customer_bank_account_id      NUMBER;
        p_customer_bank_account_num     VARCHAR2(240); 
        p_customer_bank_account_name    VARCHAR2(240);
        p_location                      VARCHAR2(240);
        p_customer_receipt_reference    VARCHAR2(240); 
        p_remittance_bank_account_num   VARCHAR2(240);
        p_remittance_bank_account_name  VARCHAR2(240);
        p_receipt_method_name           VARCHAR2(240);
        p_doc_sequence_value            NUMBER; 
        p_ussgl_transaction_code        VARCHAR2(240);
        p_anticipated_clearing_date     DATE;   
        p_called_from                   VARCHAR2(240);
        --p_comments                      VARCHAR2(240);
        p_issuer_name                   VARCHAR2(240);
        p_issue_date                    DATE; 
        p_issuer_bank_branch_id         NUMBER; 
        --p_amount                        NUMBER;
        --p_receipt_number                VARCHAR2(240);
        p_receipt_method_id             NUMBER := 7019; --Cاجمالى النقد FM
        p_customer_name                 VARCHAR2(240);
        --p_customer_id                   NUMBER;
        --p_currency_code                 VARCHAR2(10);
        --p_gl_date                       DATE;
        --p_deposit_date                  DATE;
        p_customer_site_use_id          NUMBER;
        --p_override_remit_account_flag   VARCHAR2(1);
        --p_remittance_bank_account_id    NUMBER;
        --p_maturity_date                 DATE;
        x_return_status                 VARCHAR2(1);
        x_msg_count                     NUMBER;
        x_msg_data                      VARCHAR2(240);
        p_cr_id                         NUMBER;
        l_receipt_exists               VARCHAR2(1);
        l_customer_exists              VARCHAR2(1);
        l_exists_receipt_number         VARCHAR2(30);
                 
  BEGIN               

  BEGIN

   fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_id,0);
   mo_global.set_policy_context('S',l_org_id);
    mo_global.init ('AR');
    p_attribute_rec.ATTRIBUTE5          := '1';
   
    SELECT 'Y'
                INTO l_customer_exists
                FROM AR_CUSTOMERS ARC
                where arc.customer_id = p_customer_id;
               
                --where ARC.CUSTOMER_NUMBER = p_customer_number;
               
                EXCEPTION WHEN OTHERS THEN
                l_customer_exists := 'N';
               
                END;
               
        IF l_customer_exists = 'Y' then
       
        p_receipt_number                    := 'WS-'||XX_CUST_CR_REC_S.NEXTVAL;   --rECEIPT NUMBER
       
        AR_RECEIPT_API_PUB.Create_cash (
                p_api_version                   =>  1.0               
                ,p_init_msg_list                =>  FND_API.G_FALSE
                ,p_commit                       =>  FND_API.G_FALSE
                ,p_validation_level             =>  FND_API.G_VALID_LEVEL_FULL
                ,p_usr_currency_code            =>  p_usr_currency_code
                ,p_currency_code                =>  p_currency_code   
                ,p_usr_exchange_rate_type       =>  p_usr_exchange_rate_type
                ,p_exchange_rate_type           =>  p_exchange_rate_type
                ,p_exchange_rate                =>  p_exchange_rate         
                ,p_exchange_rate_date           =>  p_exchange_rate_date
                ,p_amount                       =>  p_amount
                ,p_factor_discount_amount       =>  p_factor_discount_amount
                ,p_receipt_number               =>  p_receipt_number
                ,p_receipt_date                 =>  p_receipt_date
                ,p_gl_date                      =>  p_gl_date
                ,p_maturity_date                =>  p_maturity_date
                ,p_postmark_date                =>  p_postmark_date
                ,p_customer_id                  =>  p_customer_id
                ,p_customer_name                =>  p_customer_name
                ,p_customer_number              =>  p_customer_number
                ,p_customer_bank_account_id     =>  p_customer_bank_account_id
                ,p_customer_bank_account_num    =>  p_customer_bank_account_num
                ,p_customer_bank_account_name   =>  p_customer_bank_account_name
                ,p_location                     =>  p_location
                ,p_customer_site_use_id         =>  p_customer_site_use_id               
                ,p_customer_receipt_reference   =>  p_customer_receipt_reference
                ,p_override_remit_account_flag  =>  p_override_remit_account_flag
                ,p_remittance_bank_account_id   =>  p_remittance_bank_account_id
                ,p_remittance_bank_account_num  =>  p_remittance_bank_account_num 
                ,p_remittance_bank_account_name =>  p_remittance_bank_account_name
                ,p_deposit_date                 =>  p_deposit_date
                ,p_receipt_method_id            =>  p_receipt_method_id 
                ,p_receipt_method_name          =>  p_receipt_method_name
                ,p_doc_sequence_value           =>  p_doc_sequence_value
                ,p_ussgl_transaction_code       =>  p_ussgl_transaction_code
                ,p_anticipated_clearing_date    =>  p_anticipated_clearing_date
                ,p_called_from                  =>  p_called_from 
                ,p_global_attribute_rec         =>  p_global_attribute_rec
                ,p_attribute_rec                =>  p_attribute_rec
                ,p_comments                     =>  p_comments
                ,p_issuer_name                  =>  p_issuer_name 
                ,p_issue_date                   =>  p_issue_date   
                ,p_issuer_bank_branch_id        =>  p_issuer_bank_branch_id   
                ,x_return_status                =>  x_return_status
                ,x_msg_count                    =>  x_msg_count
                ,x_msg_data                     =>  x_msg_data
                ,p_cr_id                        =>  p_cr_id         
                ,p_org_id                       =>  l_org_id                             
);

        IF (x_return_status = 'S') THEN

   COMMIT;
 
    --fnd_file.PUT_LINE(FND_FILE.OUTPUT,'==================MAIN EXCEPTIONS=================');
   
            dbms_output.put_line('SUCCESS ReceiptNum >'||p_receipt_number);
            dbms_output.put_line('Return Status            = '|| SUBSTR (x_return_status,1,255));
            dbms_output.put_line('p_cr_id                  = '||p_cr_id);
            p_receipt_number := p_receipt_number;
               
            BEGIN
            SELECT CASH_RECEIPT_ID INTO W_MID
            --,RECEIPT_NUMBER,RECEIPT_METHOD_ID,ORG_ID,CREATION_DATE
            FROM AR_CASH_RECEIPTS_ALL
            WHERE RECEIPT_NUMBER = p_receipt_number--'98107'
            AND TRUNC(CREATION_DATE) LIKE SYSDATE
            AND RECEIPT_METHOD_ID = p_receipt_method_id
            --and type = 'MISC'
            ;
            EXCEPTION WHEN OTHERS THEN NULL;
            END;
           
           
            --W_MID := 'Created from WS';
            W_Status := 'S';
            W_Message := 'Return Status            = '|| SUBSTR (x_return_status,1,255) ;

    ELSE   

   ROLLBACK;
 
   dbms_output.put_line('Return Status    = '|| SUBSTR (x_return_status,1,255));
   dbms_output.put_line('Message Count     = '|| TO_CHAR(x_msg_count ));
   dbms_output.put_line('Message Data    = '|| SUBSTR (x_msg_data,1,255));
   dbms_output.put_line(APPS.FND_MSG_PUB.Get ( p_msg_index    => APPS.FND_MSG_PUB.G_LAST,   
          p_encoded      => APPS.FND_API.G_FALSE));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Exception-1 Customer Number:'||p_customer_number||'-'||sqlerrm);
            p_receipt_number := NULL;
            W_MID := 'Not Created';
            W_Status := 'N';
            W_Message := 'Message Data    = '|| SUBSTR (x_msg_data,1,255) ;
 
END IF;

ELSE
dbms_output.put_line('customer doesnt exists'||sqlerrm);
       p_receipt_number := NULL;
            W_MID := 'Not Created';
            W_Status := 'N';
            W_Message := 'Customer Not Exists    = '|| SUBSTR (sqlerrm,1,255) ;
 
END IF;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception :'||sqlerrm);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Exception-Last Customer Numebr:'||p_customer_number||'-'||sqlerrm);
       p_receipt_number := NULL;
            W_MID := 'Not Created';
            W_Status := 'N';
            W_Message := 'Message Data    = '|| SUBSTR (sqlerrm,1,255) ;
 

END;  

/

Oracle Apps R12 API to Create AR Invoice

create or replace procedure XXCRT_ARINV
(
P_DATE DATE,
P_BILLING_ID NUMBER,
P_CUSTOMER_ID NUMBER,
W_TRX_NUMBER OUT VARCHAR2,
W_STATUS OUT VARCHAR2,
W_MESSAGE OUT VARCHAR2
)
 as
   L_CNT VARCHAR2(100);
    P_RESULT                  VARCHAR2 (300);
    --P_DATE                    DATE := TRUNC(SYSDATE) +1;
    l_batch_source_id         NUMBER := 1003;                 --Source ???????
    l_cust_trx_type_id        NUMBER := 1001;                --type_id ???????
    v_customer_id             NUMBER := P_CUSTOMER_ID;--8173483;--
    --L_CUSTOMER_TRX_ID         NUMBER := P_CUSTOMER_TRX_ID;--8777389;--
    v_user_id                 NUMBER := 26055; --FND_PROFILE.VALUE('USER_ID'); --2605;
    v_resp_id                 NUMBER := 506035; --FND_PROFILE.VALUE('RESP_ID');--52328;
    v_resp_appl_id            NUMBER := 222;        --fnd_global.resp_appl_id;
    v_org_id                  NUMBER := 1452; --FND_PROFILE.VALUE('ORG_ID');--1930;
    v_date                    DATE := P_DATE;
    v_trx_number              NUMBER;
    l_return_status           VARCHAR2 (1);
    l_msg_count               NUMBER;
    l_msg_data                VARCHAR2 (2000);
    l_batch_id                NUMBER;
    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_trx_contingencies_tbl   ar_invoice_api_pub.trx_contingencies_tbl_type;
    trx_header_id             NUMBER;
    trx_line_id               NUMBER;
    trx_dist_id               NUMBER;
    v_line                    NUMBER := 1;
    v_line_count              NUMBER := 0;
    v_price_total             NUMBER := 0;

    l_currency_code           VARCHAR2 (100) := 'AED';
    l_site_use_id             NUMBER;
    L_EXIST                   VARCHAR2 (100);
    L_MEMO_LINE_ID            NUMBER;
    --W_TRX_NUMBER VARCHAR2 (100);
    --W_STATUS VARCHAR2 (100);
    --W_MESSAGE VARCHAR2 (100);
    --LIST_ERRORS VARCHAR2 (100);
   
            CURSOR
           list_errors is
        SELECT trx_header_id, trx_line_id, trx_salescredit_id, trx_dist_id, 
            trx_contingency_id, error_message, invalid_value
         FROM   ar_trx_errors_gt;
   
    CURSOR cbatch IS
        SELECT customer_trx_id
          FROM ra_customer_trx_all
         WHERE batch_id = l_batch_id;

    CURSOR cvalidtxn IS
        SELECT trx_header_id
          FROM ar_trx_header_gt
         WHERE trx_header_id NOT IN
                   (SELECT trx_header_id FROM ar_trx_errors_gt);
                 
     CURSOR HEADER_DETAILS IS
     --custom table storing the details of header info
     SELECT * FROM
     XX_BILLING_SYS_R
     WHERE BILLING_ID = P_BILLING_ID     ;             

    CURSOR line_items IS
         --custom table storing the details of line info
        SELECT MEMO_LINE_ID,
               UNIT_SELLING_PRICE                PRICE,
               QUANTITY_INVOICED                 QTY,
               UOM_CODE,
                  GL_ID,
               DESCRIPTION NAME
          FROM XX_BILLING_SYS_LINES RCL
         WHERE RCL.BILLING_ID = P_BILLING_ID;
       
       
         --end of cursors
BEGIN


    BEGIN
        SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;
    END;

    BEGIN
     
 SELECT c.site_use_id
          INTO l_site_use_id
          FROM hz_cust_accounts        a,
               hz_cust_acct_sites_all  b,
               hz_cust_site_uses_all   c,
               hz_party_sites          d,
               hz_locations            e
         WHERE     a.cust_account_id = b.cust_account_id
               AND b.cust_acct_site_id = c.cust_acct_site_id
               AND b.party_site_id = d.party_site_id
               AND d.location_id = e.location_id
               AND c.site_use_code = 'BILL_TO'
               AND c.status = 'A'
               AND b.org_id = c.org_id
               AND b.org_id = v_org_id
               AND a.cust_account_id = P_customer_id; --8173483
             
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.PUT_LINE ('ERROR IN SITE--22');
    END;
   
   
   FOR H IN HEADER_DETAILS LOOP
    fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
    mo_global.init ('AR');
    mo_global.set_policy_context ('S', v_org_id);
    xla_security_pkg.set_security_context (v_resp_appl_id);
    DBMS_OUTPUT.put_line ('l_cust_trx_type_id' || l_cust_trx_type_id);
    DBMS_OUTPUT.put_line ('bill_to_customer_id' || p_customer_id);
    DBMS_OUTPUT.put_line ('l_site_use_id' || l_site_use_id);
    l_trx_header_tbl (1).trx_header_id := trx_header_id;
    l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;
    l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;
    l_trx_header_tbl (1).trx_date := TRUNC (p_date);
    l_trx_header_tbl (1).gl_date := TRUNC (p_date);
    l_trx_header_tbl (1).trx_currency := l_Currency_Code;
    l_trx_header_tbl (1).primary_salesrep_id := -3;
    l_trx_header_tbl (1).org_id := v_org_id;
    l_trx_header_tbl (1).comments := 'Test from plsql Comments' || ' ' || SYSDATE;
    l_trx_header_tbl (1).INTERFACE_HEADER_CONTEXT := h.INTERFACE_HEADER_CONTEXT;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE1 := h.INTERFACE_HEADER_ATTRIBUTE1;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE2 := h.INTERFACE_HEADER_ATTRIBUTE2;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE3 := h.INTERFACE_HEADER_ATTRIBUTE3;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE4 := h.INTERFACE_HEADER_ATTRIBUTE4;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE5 := h.INTERFACE_HEADER_ATTRIBUTE5;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE6 :=h.INTERFACE_HEADER_ATTRIBUTE6;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE7 :=h.INTERFACE_HEADER_ATTRIBUTE7;
   
     l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE12 := h.INTERFACE_HEADER_ATTRIBUTE12;--TO_CHAR(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13) + 1,'yyyy/mm/dd hh:mi:ss');--'2019/01/01 00:00:00';
     
     l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE13 := h.INTERFACE_HEADER_ATTRIBUTE13;--TO_CHAR(ADD_MONTHS(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13),12) ,'yyyy/mm/dd hh:mi:ss');--'2019/05/13 00:00:00';
      l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE14 := h.INTERFACE_HEADER_ATTRIBUTE14;
    --l_trx_header_tbl(1).attribute11:=p_Source;
    --l_trx_header_tbl(1).attribute12:=p_trx_number;
    --l_trx_header_tbl(1).attribute13:=TRUNC(SYSDATE);
    --l_trx_header_tbl(1).attribute14:=TRUNC(P_Expiration_Date);
    --l_trx_header_tbl(1).attribute15:=p_Accounting_Rule;
    --l_trx_header_tbl(1).attribute10:=p_Sub_Service;
    --l_trx_header_tbl(1).attribute7:=NVL(substr(p_business_name,0,60),'-');
    l_trx_header_tbl (1).trx_class := 'INV';
    l_trx_header_tbl (1).bill_to_site_use_id := l_site_use_id;
    l_trx_header_tbl (1).term_id := 1000;
    l_batch_source_rec.batch_source_id := l_batch_source_id;
   
    END LOOP;
   
    FOR i IN line_items
    LOOP
        BEGIN
            v_line_count := v_line_count + v_line;
            --v_price_total := v_price_total + (i.price * i.qty);
            l_trx_lines_tbl (v_line_count).trx_header_id := trx_header_id;
            l_trx_lines_tbl (v_line_count).trx_line_id :=
                ra_customer_trx_lines_s.NEXTVAL;              --trx_line_id_v;
            l_trx_lines_tbl (v_line_count).line_number := v_line_count;
            l_trx_lines_tbl (v_line_count).description := i.name;
            l_trx_lines_tbl (v_line_count).quantity_invoiced := i.qty;
            l_trx_lines_tbl (v_line_count).unit_selling_price := i.price;
            l_trx_lines_tbl (v_line_count).line_type := 'LINE';
            --l_trx_lines_tbl (1).tax_exempt_flag := 'E';
            --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
            --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
            l_trx_lines_tbl (v_line_count).memo_line_id := i.memo_line_id;
            --   l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
            --   l_trx_lines_tbl (1).TAX_RATE := 10;
            l_trx_dist_tbl (v_line_count).trx_dist_id :=
                ra_cust_trx_line_gl_dist_s.NEXTVAL;
            l_trx_dist_tbl (v_line_count).trx_line_id :=
                ra_customer_trx_lines_s.CURRVAL;
            l_trx_dist_tbl (v_line_count).account_class := 'REV';
            l_trx_dist_tbl (v_line_count).PERCENT := 100;
            l_trx_dist_tbl (v_line_count).code_combination_id := i.gl_id;
            --DBMS_OUTPUT.put_line ('i value  ->' || i);
            --DBMS_OUTPUT.put_line ('line-   ' || i || ' Amt = ' || i.price);
            --DBMS_OUTPUT.put_line ('REV ID   ' || i || '  = ' || i.gl_id);
            END;
        END LOOP;

        --END;
       
            fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
            mo_global.set_policy_context ('S', v_org_id);

            AR_INVOICE_API_PUB.create_single_invoice (
                p_api_version            => 1.0,
                P_INIT_MSG_LIST          => FND_API.G_FALSE,
                P_COMMIT                 => FND_API.G_FALSE,
                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);
             commit;
                    dbms_output.put_line('l_return_status-'||l_return_status);
                    dbms_output.put_line('l_customer_trx_id-'||l_customer_trx_id);
                    dbms_output.put_line('l_msg_count -'|| l_msg_count);
                   
                    IF l_customer_trx_id > 0 THEN
                    BEGIN
                      SELECT TRX_NUMBER
                      INTO W_TRX_Number
                      FROM ra_customer_trx
                      WHERE customer_trx_id=l_customer_trx_id;
                    END;
                   
                      W_Status:='Success';
  W_Message:='Invoice is Created-> ID = '||l_customer_trx_id;
                      dbms_output.put_line(W_Status);
                      dbms_output.put_line(W_Message);
                      dbms_output.put_line('Generated Oracle Txn Number-->'||W_TRX_Number);
                   
                     
                      END IF;
 
                      IF l_return_status = fnd_api.g_ret_sts_error OR
                         l_return_status = fnd_api.g_ret_sts_unexp_error THEN
                        NULL;
                         ELSE
                          SELECT count(*) 
                          INTO   l_cnt
                          FROM   ar_trx_errors_gt;
                         
                          IF l_cnt = 0 THEN
                        NULL;
                         ELSE
                         NULL;
                          FOR i in list_errors LOOP
                          NULL;
                           END LOOP;                         
                          END IF;
                END IF;
   COMMIT;
END;

Oracle Apps R12 api to Create AR Invoice from existing AR Invoice

DECLARE
V_OUT NUMBER;
BEGIN
 XXCRT_ARINV_FRM_OLDINV(8173483,8777389);
DBMS_OUTPUT.PUT_LINE(V_OUT);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


create or replace procedure XXCRT_ARINV_FRM_OLDINV
(
P_CUSTOMER_ID NUMBER,
P_CUSTOMER_TRX_ID NUMBER
--,P_CUSTOMER_TRX_ID_OUT OUT NUMBER
)
 as
--DECLARE
    P_RESULT                  VARCHAR2 (300);
    P_DATE                    DATE := TRUNC(SYSDATE);
    l_batch_source_id         NUMBER := 1003;                 --Source
    l_cust_trx_type_id        NUMBER := 1001;                --type_id
    v_customer_id             NUMBER := P_CUSTOMER_ID;
    L_CUSTOMER_TRX_ID         NUMBER := P_CUSTOMER_TRX_ID;
    v_user_id                 NUMBER := 26099; --FND_PROFILE.VALUE('USER_ID');
    v_resp_id                 NUMBER := 50611; --FND_PROFILE.VALUE('RESP_ID');
    v_resp_appl_id            NUMBER := 222;        --fnd_global.resp_appl_id;
    v_org_id                  NUMBER := 1455; --FND_PROFILE.VALUE('ORG_ID');--1930;
    v_date                    DATE := P_DATE;
    v_trx_number              NUMBER;
    l_return_status           VARCHAR2 (1);
    l_msg_count               NUMBER;
    l_msg_data                VARCHAR2 (2000);
    l_batch_id                NUMBER;
    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_trx_contingencies_tbl   ar_invoice_api_pub.trx_contingencies_tbl_type;
    trx_header_id             NUMBER;
    trx_line_id               NUMBER;
    trx_dist_id               NUMBER;
    v_line                    NUMBER := 1;
    v_line_count              NUMBER := 0;
    v_price_total             NUMBER := 0;

    l_currency_code           VARCHAR2 (100) := 'USD';
    l_site_use_id             NUMBER;
    L_EXIST                   VARCHAR2 (100);
    L_MEMO_LINE_ID            NUMBER;

    CURSOR cbatch IS
        SELECT customer_trx_id
          FROM ra_customer_trx_all
         WHERE batch_id = l_batch_id;

    CURSOR cvalidtxn IS
        SELECT trx_header_id
          FROM ar_trx_header_gt
         WHERE trx_header_id NOT IN
                   (SELECT trx_header_id FROM ar_trx_errors_gt);
                 
     CURSOR HEADER_DETAILS IS
     SELECT * FROM
     RA_CUSTOMER_TRX_ALL
     WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID     ;             

    CURSOR line_items IS
        SELECT MEMO_LINE_ID,
               UNIT_SELLING_PRICE                PRICE,
               QUANTITY_INVOICED                 QTY,
               UOM_CODE,
               (SELECT GL_ID_REV
                  FROM AR_MEMO_LINES_ALL_VL
                 WHERE     MEMO_LINE_ID = RCL.MEMO_LINE_ID
                       AND (UOM_CODE = RCL.UOM_CODE OR RCL.UOM_CODE IS NULL)
                       AND ORG_ID = V_ORG_ID)    GL_ID,
               (SELECT DESCRIPTION
                  FROM AR_MEMO_LINES_ALL_VL
                 WHERE     MEMO_LINE_ID = RCL.MEMO_LINE_ID
                       AND (UOM_CODE = RCL.UOM_CODE OR RCL.UOM_CODE IS NULL)
                       AND ORG_ID = V_ORG_ID)    NAME
          FROM RA_CUSTOMER_TRX_LINES_ALL RCL
         WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID;
       
         --end of cursors
BEGIN


    BEGIN
        SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;
    END;

    BEGIN
     
 SELECT c.site_use_id
          INTO l_site_use_id
          FROM hz_cust_accounts        a,
               hz_cust_acct_sites_all  b,
               hz_cust_site_uses_all   c,
               hz_party_sites          d,
               hz_locations            e
         WHERE     a.cust_account_id = b.cust_account_id
               AND b.cust_acct_site_id = c.cust_acct_site_id
               AND b.party_site_id = d.party_site_id
               AND d.location_id = e.location_id
               AND c.site_use_code = 'BILL_TO'
               AND c.status = 'A'
               AND b.org_id = c.org_id
               AND b.org_id = v_org_id
               AND a.cust_account_id = v_customer_id; --8173483
             
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.PUT_LINE ('ERROR IN SITE--22');
    END;
   
   
   FOR H IN HEADER_DETAILS LOOP
    fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
    mo_global.init ('AR');
    mo_global.set_policy_context ('S', v_org_id);
    xla_security_pkg.set_security_context (v_resp_appl_id);
    DBMS_OUTPUT.put_line ('l_cust_trx_type_id' || l_cust_trx_type_id);
    DBMS_OUTPUT.put_line ('bill_to_customer_id' || v_customer_id);
    DBMS_OUTPUT.put_line ('l_site_use_id' || l_site_use_id);
    l_trx_header_tbl (1).trx_header_id := trx_header_id;
    l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;
    l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;
    l_trx_header_tbl (1).trx_date := TRUNC (p_date);
    l_trx_header_tbl (1).gl_date := TRUNC (p_date);
    l_trx_header_tbl (1).trx_currency := l_Currency_Code;
    l_trx_header_tbl (1).primary_salesrep_id := -3;
    l_trx_header_tbl (1).org_id := v_org_id;
    l_trx_header_tbl (1).comments := 'Test from plsql' || ' ' || SYSDATE;
    l_trx_header_tbl (1).INTERFACE_HEADER_CONTEXT := h.INTERFACE_HEADER_CONTEXT;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE1 := h.INTERFACE_HEADER_ATTRIBUTE1;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE2 := h.INTERFACE_HEADER_ATTRIBUTE2;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE3 := h.INTERFACE_HEADER_ATTRIBUTE3;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE4 := h.INTERFACE_HEADER_ATTRIBUTE4;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE5 := h.INTERFACE_HEADER_ATTRIBUTE5;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE6 :=h.INTERFACE_HEADER_ATTRIBUTE6;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE7 :=h.INTERFACE_HEADER_ATTRIBUTE7;
   
     l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE12 := TO_CHAR(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13) + 1,'yyyy/mm/dd hh:mi:ss');--'2019/01/01 00:00:00';
     
     l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE13 := TO_CHAR(ADD_MONTHS(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13),12) ,'yyyy/mm/dd hh:mi:ss');--'2019/05/13 00:00:00';
      l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE14 := h.INTERFACE_HEADER_ATTRIBUTE14;
    --l_trx_header_tbl(1).attribute11:=p_Source;
    --l_trx_header_tbl(1).attribute12:=p_trx_number;
    --l_trx_header_tbl(1).attribute13:=TRUNC(SYSDATE);
    --l_trx_header_tbl(1).attribute14:=TRUNC(P_Expiration_Date);
    --l_trx_header_tbl(1).attribute15:=p_Accounting_Rule;
    --l_trx_header_tbl(1).attribute10:=p_Sub_Service;
    --l_trx_header_tbl(1).attribute7:=NVL(substr(p_business_name,0,60),'-');
    l_trx_header_tbl (1).trx_class := 'INV';
    l_trx_header_tbl (1).bill_to_site_use_id := l_site_use_id;
    l_trx_header_tbl (1).term_id := 1000;
    l_batch_source_rec.batch_source_id := l_batch_source_id;
   
    END LOOP;
   
    FOR i IN line_items
    LOOP
        BEGIN
            v_line_count := v_line_count + v_line;
            --v_price_total := v_price_total + (i.price * i.qty);
            l_trx_lines_tbl (v_line_count).trx_header_id := trx_header_id;
            l_trx_lines_tbl (v_line_count).trx_line_id :=
                ra_customer_trx_lines_s.NEXTVAL;              --trx_line_id_v;
            l_trx_lines_tbl (v_line_count).line_number := v_line_count;
            l_trx_lines_tbl (v_line_count).description := i.name;
            l_trx_lines_tbl (v_line_count).quantity_invoiced := i.qty;
            l_trx_lines_tbl (v_line_count).unit_selling_price := i.price;
            l_trx_lines_tbl (v_line_count).line_type := 'LINE';
            --l_trx_lines_tbl (1).tax_exempt_flag := 'E';
            --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
            --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
            l_trx_lines_tbl (v_line_count).memo_line_id := i.memo_line_id;
            --   l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
            --   l_trx_lines_tbl (1).TAX_RATE := 10;
            l_trx_dist_tbl (v_line_count).trx_dist_id :=
                ra_cust_trx_line_gl_dist_s.NEXTVAL;
            l_trx_dist_tbl (v_line_count).trx_line_id :=
                ra_customer_trx_lines_s.CURRVAL;
            l_trx_dist_tbl (v_line_count).account_class := 'REV';
            l_trx_dist_tbl (v_line_count).PERCENT := 100;
            l_trx_dist_tbl (v_line_count).code_combination_id := i.gl_id;
            --DBMS_OUTPUT.put_line ('i value  ->' || i);
            --DBMS_OUTPUT.put_line ('line-   ' || i || ' Amt = ' || i.price);
            --DBMS_OUTPUT.put_line ('REV ID   ' || i || '  = ' || i.gl_id);
            END;
        END LOOP;

        --END;
       
            fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
            mo_global.set_policy_context ('S', v_org_id);

            AR_INVOICE_API_PUB.create_single_invoice (
                p_api_version            => 1.0,
                P_INIT_MSG_LIST          => FND_API.G_FALSE,
                P_COMMIT                 => FND_API.G_FALSE,
                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);
             commit;
                    DBMS_OUTPUT.put_line (l_return_status||'-'||l_customer_trx_id);
   DBMS_OUTPUT.put_line (l_msg_data);
   P_RESULT:= l_return_status||'-'||l_msg_data;
   --P_CUSTOMER_TRX_ID_OUT := l_customer_trx_id;
   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
      FOR cvalidtxnrec IN cvalidtxn
      LOOP
         IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
         THEN
            DBMS_OUTPUT.put_line ('Invoice(s) suceessfully created!');
            DBMS_OUTPUT.put_line (   'Batch ID: '
                                  || ar_invoice_api_pub.g_api_outputs.batch_id
                                 );
            l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
            FOR cbatchrec IN cbatch
            LOOP
               DBMS_OUTPUT.put_line (   'Cust Trx Id '
                                     || cbatchrec.customer_trx_id
                                    );
               v_trx_number := cbatchrec.customer_trx_id;
               DBMS_OUTPUT.PUT_LINE('INVOICE_ID->'||v_trx_number||'-INVOICE_NUMBER->'||trx_header_id);
               --P_INVOICE_ID := v_trx_number;
               --P_INVOICE_NUM := cbatchrec.TRX_NUMBER;--trx_header_id_v;
                /*UPDATE XX_FIA_MT
                 SET INVOICE_ID = v_trx_number , TAX_INVOICE_NO = trx_header_id_v
                 WHERE FLIGHT_REPORT_NO = P_FLIGHT_REPORT_NO;*/
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line ('Errors found!');
       
         END IF;
      END LOOP;
   END IF;
   COMMIT;
END;

Oracle Apps R12 Customer Phone Number, Email and AR Invoice Details Query

SELECT
CUSTOMER_TRX_ID,SET_OF_BOOKS_ID,TRX_NUMBER,
TRX_DATE,
BILL_TO_CUSTOMER_ID,
fnd_conc_date.string_to_date(INTERFACE_HEADER_ATTRIBUTE12)ST_DT,
fnd_conc_date.string_to_date(INTERFACE_HEADER_ATTRIBUTE13)END_DT,
SUBSTR(INTERFACE_HEADER_ATTRIBUTE13,1,10) END_DATE,
INTERFACE_HEADER_ATTRIBUTE14,
RAC.ORG_ID,PARTY_NAME, ACCOUNT_NUMBER,
(select
     hcp.PHONE_COUNTRY_CODE||hcp.phone_number phone_number
  from apps.hz_cust_accounts hca
     , apps.hz_parties       hp
     , apps.hz_contact_points hcp
 where hca.party_id          = hp.party_id
   and hp.party_id          = hcp.owner_table_id
   and hcp.owner_table_name = 'HZ_PARTIES'
   and CONTACT_POINT_PURPOSE = 'BUSINESS'
   and hcp.contact_point_type = 'PHONE'
   and hca.CUST_ACCOUNT_ID = BILL_TO_CUSTOMER_ID
   )phone_number,
(select
     hcp.EMAIL_ADDRESS
  from apps.hz_cust_accounts hca
     , apps.hz_parties       hp
     , apps.hz_contact_points hcp
 where hca.party_id          = hp.party_id
   and hp.party_id          = hcp.owner_table_id
   and hcp.owner_table_name = 'HZ_PARTIES'
   and CONTACT_POINT_PURPOSE = 'BUSINESS'
   and hcp.contact_point_type = 'EMAIL'
    and hca.CUST_ACCOUNT_ID = BILL_TO_CUSTOMER_ID)Email_Address
FROM  RA_CUSTOMER_TRX_ALL RAC
      ,apps.hz_cust_accounts hca
     , apps.hz_parties       hp
WHERE
hca.CUST_ACCOUNT_ID = RAC.BILL_TO_CUSTOMER_ID
AND hca.party_id          = hp.party_id
AND RAC.ORG_ID = 145
AND RAC.CUST_TRX_TYPE_ID = 1001 --type_id ???????
AND RAC.BATCH_SOURCE_ID = 1003 --Source ???????
AND RAC.INTERFACE_HEADER_ATTRIBUTE12 IS NOT NULL --ST_DT
AND RAC.INTERFACE_HEADER_ATTRIBUTE13 IS NOT NULL --END_DT
AND TRX_NUMBER = '61843'