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;