AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT

 

create or replace PROCEDURE      XX_AR_RCPT_APPLY_ONACCOUNT(errbuff out varchar2,retcode out number)

IS

--DECLARE

   p_api_version                    NUMBER;

   p_init_msg_list                  VARCHAR2 (200);

   p_commit                         VARCHAR2 (200);

   p_validation_level               NUMBER;

   x_return_status                  VARCHAR2 (200);

   x_msg_count                      NUMBER;

   x_msg_data                       VARCHAR2 (200);

  -- p_receipt_number                 ar_cash_receipts.receipt_number%TYPE;

   p_ussgl_transaction_code         ar_receivable_applications.ussgl_transaction_code%TYPE;

   p_attribute_rec                  AR_RECEIPT_API_PUB.attribute_rec_type;

   p_global_attribute_rec           AR_RECEIPT_API_PUB.global_attribute_rec_type;

   p_comments                       ar_receivable_applications.comments%TYPE;

   p_application_ref_num            ar_receivable_applications.application_ref_num%TYPE;

   p_secondary_application_ref_id   ar_receivable_applications.secondary_application_ref_id%TYPE;

   p_customer_reference             ar_receivable_applications.customer_reference%TYPE;

   p_called_from                    VARCHAR2 (200);

   p_customer_reason                ar_receivable_applications.customer_reason%TYPE;

   p_secondary_app_ref_type         ar_receivable_applications.secondary_application_ref_type%TYPE;

   p_secondary_app_ref_num          ar_receivable_applications.secondary_application_ref_num%TYPE;

   l_cash_receipt_id                ar_cash_receipts.cash_receipt_id%TYPE;

   l_receipt_number                 ar_cash_receipts.receipt_number%TYPE;

   l_amount_applied                 ar_receivable_applications.amount_applied%TYPE;

   l_apply_date                     ar_receivable_applications.apply_date%TYPE;

   l_apply_gl_date                  ar_receivable_applications.gl_date%TYPE;

--Query to get the details of receipts which are unapplied for receipt methods

CURSOR c_get_details

IS 

SELECT acra.cash_receipt_id,

       acra.receipt_number,

       sum(araa.amount_applied) amount_applied

FROM ar_cash_receipts_all acra ,

     ar_receivable_applications_all araa

WHERE acra.cash_receipt_id = araa.cash_receipt_id

AND acra.type ='CASH' 

AND acra.status = 'UNAPP'

AND araa.status = 'UNAPP'

AND acra.receipt_method_id in ('37029','42028','15025','12024')  

--AND acra.cash_receipt_id in ('1330277')

GROUP BY acra.cash_receipt_id,acra.receipt_number;

BEGIN

   mo_global.SET_POLICY_CONTEXT ('S', 103);                     -- your org id

   mo_global.init ('AR');

  FOR j in c_get_details

  LOOP

  l_cash_receipt_id := j.cash_receipt_id;

  l_amount_applied  := j.amount_applied;

  l_receipt_number  := j.receipt_number;

  l_apply_date      := TRUNC(SYSDATE);

  l_apply_gl_date   := TRUNC(SYSDATE);

  --Passing Cash_receipt_id and Amount_Applied to Apply On Account API 

   AR_RECEIPT_API_PUB.Apply_on_account (

      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,

      x_return_status                  => x_return_status,

      x_msg_count                      => x_msg_count,

      x_msg_data                       => x_msg_data,

      p_cash_receipt_id                => l_cash_receipt_id,

      p_receipt_number                 => l_receipt_number,

      p_amount_applied                 => l_amount_applied,

      p_apply_date                     => l_apply_date,

      p_apply_gl_date                  => l_apply_gl_date,

      p_org_id                         => 103, ------ <Org id >

      p_ussgl_transaction_code         => p_ussgl_transaction_code,

      p_attribute_rec                  => p_attribute_rec,

      p_global_attribute_rec           => p_global_attribute_rec,

      p_comments                       => p_comments,

      p_application_ref_num            => p_application_ref_num,

      p_secondary_application_ref_id   => p_secondary_application_ref_id,

      p_customer_reference             => p_customer_reference,

      p_called_from                    => p_called_from,

      p_customer_reason                => p_customer_reason,

      p_secondary_app_ref_type         => p_secondary_app_ref_type,

      p_secondary_app_ref_num          => p_secondary_app_ref_num

   );

   IF (x_return_status = 'S')

   THEN

      COMMIT;

    --  DBMS_OUTPUT.put_line ('SUCCESS');

      FND_FILE.PUT_LINE(FND_FILE.LOG,'SUCCESS -> '||' Receipt Number'||' '||l_receipt_number||' Applied OnAccount');

      --DBMS_OUTPUT.put_line ('Return Status            = ' || SUBSTR (x_return_status, 1, 255));

      --FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status            = ' || SUBSTR (x_return_status, 1, 255));

      --DBMS_OUTPUT.put_line ('Message Count             = ' || x_msg_count);

      --FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Count             = ' || x_msg_count);

      --DBMS_OUTPUT.put_line ('Message Data            = ' || x_msg_data);

      --FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Data            = ' || x_msg_data);

   ELSE

      ROLLBACK;

      DBMS_OUTPUT.put_line ('Return Status    = ' || SUBSTR (x_return_status, 1, 255));

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status            = ' || SUBSTR (x_return_status, 1, 255)||' '||l_receipt_number);

      DBMS_OUTPUT.put_line ('Message Count     = ' || TO_CHAR (x_msg_count));

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Count             = ' || x_msg_count);

      DBMS_OUTPUT.put_line ('Message Data    = ' || SUBSTR (x_msg_data, 1, 255));

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Data            = ' || x_msg_data);

      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));

      IF x_msg_count >= 0

      THEN

         FOR I IN 1 .. 10

         LOOP

            FND_FILE.PUT_LINE(FND_FILE.LOG,I || '. '|| SUBSTR (FND_MSG_PUB.Get (p_encoded   => FND_API.G_FALSE),1,255));

         END LOOP;

      END IF;

   END IF;

END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Exception :' || SQLERRM);

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception :' || SQLERRM);

END;

Oracle Apps using SOAP Request.

 create or replace PROCEDURE XX_ESB_SOAP_REQUEST(

      ERRBUFF           OUT VARCHAR2 ,

      RETCODE           OUT NUMBER ,

      P_IN_MODULE       IN VARCHAR2,

      P_IN_KEY_VALUE1   IN VARCHAR2,

  P_IN_KEY_VALUE2   IN VARCHAR2)

IS

/******************************************************************************************

--HEADER: XXESBREQ.prc

--Description: This Procedure contains the HTTP SOAP trigger to ESB.

********************************************************************************************/

    v_soap_request  VARCHAR2(3000);

    v_soap_response VARCHAR2(1500);

    http_req        utl_http.req;

    http_resp       utl_http.resp;

v_len           NUMBER;

    v_txt           VARCHAR2(1500);

v_error_text    VARCHAR2(1500);

v_key_value1    VARCHAR2(100);

v_key_value2    VARCHAR2(100);

v_curr_date     VARCHAR2(100);

v_esb_soap_url  VARCHAR2(100);

BEGIN

--Initialization

v_soap_request := NULL;

v_soap_response := NULL;

v_len  := 0;

v_txt  := NULL;

v_error_text := NULL;

v_esb_soap_url := NULL;

  IF TRIM(P_IN_MODULE) = 'MDM_ORCL_CUST_INT' THEN

    v_key_value1 :=  TRIM(P_IN_KEY_VALUE1); --MDM_PARTY_ID

--ESB Status Code from the staging table.

--Modified for ver1.1

--v_key_value2 :=  'MAP-REQ';

v_key_value2 := TRIM(P_IN_KEY_VALUE2);

v_curr_date  :=  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MM:SS');

  END IF;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Processing Request....... ');

FND_FILE.PUT_LINE(FND_FILE.LOG,'P_IN_MODULE: '||P_IN_MODULE);

    FND_FILE.PUT_LINE(FND_FILE.LOG,'MDM_PARTY_ID: '||v_key_value1);

FND_FILE.PUT_LINE(FND_FILE.LOG,'ESB_STATUS_CODE: '||v_key_value2);

--Build SOAP Request

    v_soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'||

                   '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v1="http://companyname.net/controller/v1.0">

                <soapenv:Header/>

                <soapenv:Body>

                                <v1:notify>

                                                <eventRequest>

                                                                <events>

                                                                                <userID>EBSService</userID>

                                                                                <passPhrase></passPhrase>

                                                                                <creationTime>'||v_curr_date||'</creationTime>

                                                                                <action>CREATE</action>

                                                                                <entity>MDM_CUSTOMERMASTER</entity>

                                                                                <recordCount>1</recordCount>

                                                                                <primaryKey>'||v_key_value1||'</primaryKey>

                                                                                <transactionType>SINGLE</transactionType>

                                                                                <sourceSystem>EBS</sourceSystem>

<eventType>'||v_key_value2||'</eventType>

                                                                </events>

                                                </eventRequest>

                                </v1:notify>

                </soapenv:Body>

</soapenv:Envelope>';

--Connecting to ESB Master Controller

-- Query to retrieve EBS URL Based on Oracle EBS Instance

BEGIN

SELECT FFVV.DESCRIPTION

      INTO v_esb_soap_url

      FROM FND_FLEX_VALUE_SETS FFVS

         , FND_FLEX_VALUES_VL FFVV

     WHERE FFVV.FLEX_VALUE = (SELECT INSTANCE_NAME FROM V$INSTANCE)

       And FFVS.FLEX_VALUE_SET_ID = FFVV.FLEX_VALUE_SET_ID

       AND FFVS.FLEX_VALUE_SET_NAME = 'XX_ESB_URL_LIST';

EXCEPTION

WHEN NO_DATA_FOUND THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR NO ESB Controller URL defined. '||SUBSTR(SQLERRM,1,100));

WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR while retrieving ESB Controller URL. '||SUBSTR(SQLERRM,1,100));

END;

    http_req:= utl_http.begin_request

              ( v_esb_soap_url

              , 'POST'

              , 'HTTP/1.1'

              );

    UTL_HTTP.SET_HEADER(HTTP_REQ, 'Content-Type', 'text/xml charset=UTF-8');

    UTL_HTTP.SET_HEADER(HTTP_REQ, 'Content-Length', LENGTH(v_soap_request));

UTL_HTTP.SET_HEADER(HTTP_REQ, 'SOAPAction','"MP-ORCL-MDM"');    -- SOAP Request HEader Attribute added 

    UTL_HTTP.WRITE_TEXT(http_req, v_soap_request);

    http_resp:= UTL_HTTP.GET_RESPONSE(http_req);

-- Obtain the length of the response

utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1);

   BEGIN

        utl_http.read_text(http_resp, v_txt, v_len);

        v_soap_response := v_txt; --Read the response

  EXCEPTION

WHEN Utl_Http.End_Of_Body THEN NULL;

WHEN OTHERS THEN

  FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While parsing the SOAP response. '||SUBSTR(SQLERRM,1,100));

  END;

    utl_http.end_response(http_resp);

FND_FILE.PUT_LINE(FND_FILE.LOG,'soap_response: '||v_soap_response);

--Update the SOAP response

IF v_soap_response IS NOT NULL THEN

  FND_FILE.PUT_LINE(FND_FILE.LOG,'Oracle - ESB trigger successful. ');

  BEGIN

UPDATE XX_MDM_CUSTOMER_STG_ALL

SET ESB_RESPONSE = v_soap_response

    ,ERROR_CODE  = 'TRIGGER_SUCCESS'||' '||ERROR_CODE

        ,LAST_UPDATED_BY = 'ARCUSTOMER'

        ,LAST_UPDATE_DATE = SYSDATE

WHERE MDM_PARTY_ID = v_key_value1

AND ESB_STATUS_CODE = v_key_value2

AND NVL(PROCESSED_FLAG,'N') = 'Y';

FND_FILE.PUT_LINE(FND_FILE.LOG,'Response successfully updated on the staging table for Party Id: '||v_key_value1);

  EXCEPTION

  WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Updating the ESB Response on the staging table. '||SUBSTR(SQLERRM,1,100));

  END;

END IF;

--Procedure Level exception

EXCEPTION

WHEN OTHERS THEN

v_error_text := SQLERRM;

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Triggering ESB.....'||SUBSTR(SQLERRM,1,100));

  BEGIN

UPDATE XX_MDM_CUSTOMER_STG_ALL

SET ESB_RESPONSE = v_error_text

    ,ERROR_CODE = 'TRIGGER_FAILURE'||' '||ERROR_CODE

,ERROR_DESCRIPTION = v_error_text||' '||ERROR_DESCRIPTION

,LAST_UPDATED_BY   = 'ARCUSTOMER'

,LAST_UPDATE_DATE  = SYSDATE

WHERE MDM_PARTY_ID = v_key_value1

AND ESB_STATUS_CODE = v_key_value2

AND NVL(PROCESSED_FLAG,'N') = 'Y';

  EXCEPTION

  WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Updating the TRIGGER_FAILURE exception Details. '||SUBSTR(SQLERRM,1,100));

  END;

END;