How to find the secuity profile id by using the Organization Id in R12 ?

select           psp.SECURITY_PROFILE_ID
                   ,psp.BUSINESS_GROUP_ID
                   ,psp.ORGANIZATION_ID
                   ,psp.SECURITY_PROFILE_NAME
from per_Security_profiles psp
        ,per_security_organizations pso
        ,hr_operating_units hou
where 1=1
and psp.SECURITY_PROFILE_ID = pso.SECURITY_PROFILE_ID
and pso.organization_id = hou.organization_id
and hou.organization_id = 202
and psp.BUSINESS_GROUP_ID = 81;

How to load(Interface) Contract note text into Oracle Apps R12 ?

This Package is used to load the OKL contracts note text into Oracle Apps Base tables.

After loading the data, you can check it in JTF_NOTES_B,JTF_NOTES_TL tables.

--Package Spec
create or replace PACKAGE XX_NOTESCON_PKG AUTHID CURRENT_USER
AS
 PROCEDURE XXEC_OKL_MAIN_PROC (
      p_errbuf_out    OUT   VARCHAR2,
      p_retcode_out   OUT   VARCHAR2
   );
lv_error_location varchar2(2000);
END XX_NOTESCON_PKG;
/
create or replace PUBLIC synonym XXEC_OKL_AGCN_NOTESCON_PKG for XXEC.XXEC_OKL_AGCN_NOTESCON_PKG
/
GRANT ALL ON XXEC.XXEC_OKL_AGCN_NOTESCON_PKG TO APPS
/
SHOW ERRORS
/


--Package Body.

create or replace PACKAGE BODY XXEC_NOTESCON_PKG
AS

 PROCEDURE XXEC_OKL_MAIN_PROC (
      p_errbuf_out    OUT   VARCHAR2,
      p_retcode_out   OUT   VARCHAR2
 )
 IS
/*-------------------------------------------------------
  Defining the Cursor 
 --------------------------------------------------------*/
 --Cursor to get the data from stage table.

      CURSOR notes_data
      IS
         select XNT.CUSTOMER_NAME
               ,XNT.CONTRACT_NUMBER
               ,HAB.ID
               ,HAB.BILL_TO_SITE_USE_ID
               ,to_date(XNT.creation_date,'DD-MM-YY') creation_date
               ,XNT.NOTE_TEXT
               ,XNT.STATUS_FLAG
        from XXEC_OKL_NOTESCON_TMP XNT
            ,OKC_K_HEADERS_ALL_B HAB
        WHERE 1=1
          AND XNT.CONTRACT_NUMBER=HAB.CONTRACT_NUMBER
          ORDER BY XNT.CONTRACT_NUMBER;


 -- Decalring Local Variables.
      l_api_version          NUMBER;
      l_init_msg_list        VARCHAR2 (1);
      l_validation_level     NUMBER;
      l_commit               VARCHAR2 (1);
      l_return_status        VARCHAR2 (1);
      l_msg_count            NUMBER;
      l_msg_data             VARCHAR2 (2000);
      l_jtf_note_id          NUMBER                                   := NULL;
      l_source_object_code   VARCHAR2 (50);
      l_note_status          VARCHAR2 (8);
      l_note_contexts        jtf_notes_pub.jtf_note_contexts_tbl_type;
      l_msg_index            NUMBER;
      l_msg_index_out        NUMBER;
      l_count                NUMBER                                   := 0;
      l_acount               NUMBER                                   := 0;
      l_success  number:=0;
      l_error  number:=0;
      l_error_e number:=0;
      l_notes_err_count      NUMBER                                   := 0;
      l_note_type            VARCHAR2(80) ;
      l_flag                 VARCHAR2(1)  := 'N';
BEGIN
   XXEC_NOTESCON_PKG.lv_error_location:='Starts Execution of Code'; --Intializing value for error location variable.
   fnd_file.put_line (fnd_file.LOG, 'Begin ACCESS NOTES Conversion ');
   fnd_file.put_line (fnd_file.LOG, fnd_profile.VALUE ('ORG_ID'));

   --Intializing the values for Local Variables.
   l_api_version := 1.0;
   l_init_msg_list := fnd_api.g_true;
   l_validation_level := fnd_api.g_valid_level_full;
   l_commit := fnd_api.g_true;
   l_msg_count := 0;
   l_note_status := 'I';
   l_source_object_code := 'OKC_K_HEADER';
   l_note_type := 'AS_USER';

   XXEC_NOTESCON_PKG.lv_error_location:='Before loop1'; --Intializing value for error location variable.
   FOR v_cur_notes_data IN notes_data
   LOOP  --For Loop1
      XXEC_NOTESCON_PKG.lv_error_location:='Inside loop1';  --Intializing value for error location variable.
      fnd_file.put_line (fnd_file.LOG, 'Customer : ' || v_cur_notes_data.customer_name);
      fnd_file.put_line (fnd_file.LOG, 'Cteation Date : ' || v_cur_notes_data.creation_date);
      fnd_file.put_line (fnd_file.LOG, 'Note : ' || v_cur_notes_data.note_text);
      l_flag := 'N';
      l_acount := l_acount + 1;

           l_flag := 'Y';
         /*-------------------------------------------------
           Populating the l_note_contexts of pl/sql table type
           -------------------------------------------------*/
         l_count := l_count + 1;
         fnd_file.put_line (fnd_file.LOG, 'Current Cursor count ' || l_count);
         l_note_contexts (1).note_context_type := 'OKC_K_HEADER';
         l_note_contexts (1).note_context_type_id := v_cur_notes_data.ID;
         l_note_contexts (1).last_update_date := SYSDATE;
         l_note_contexts (1).last_updated_by := fnd_global.user_id;
         l_note_contexts (1).creation_date := SYSDATE;
         l_note_contexts (1).created_by := fnd_global.user_id;
         l_note_contexts (1).last_update_login := fnd_global.login_id;
         /*-------------------------------------------------
                     Calling the API
           ------------------------------------------------*/
         -- fnd_file.put_line (fnd_file.LOG, 'Calling API ');

         XXEC_NOTESCON_PKG.lv_error_location:='Before calling API JTF_NOTES_PUB';  --Intializing value for error location variable.

        jtf_notes_pub.create_note  --Calling API.
                 (p_api_version                => l_api_version,
                  p_init_msg_list              => l_init_msg_list,
                  p_commit                     => l_commit,
                  p_validation_level           => l_validation_level,
                  x_return_status              => l_return_status,
                  x_msg_count                  => l_msg_count,
                  x_msg_data                   => l_msg_data,
                  p_jtf_note_id                => l_jtf_note_id,
                  --p_entered_by                 => 1396,
                  p_entered_date               => v_cur_notes_data.creation_date,
                 -- p_source_object_id           => v_cur_notes_data.bill_to_site_use_id,
                  p_source_object_id           => v_cur_notes_data.ID,
                  p_source_object_code         => l_source_object_code, --OKC_K_HEADER
                  p_notes                      => v_cur_notes_data.note_text,
                  p_notes_detail               => NULL,
                  p_note_type                  => l_note_type,
                  p_note_status                => l_note_status,
                  p_jtf_note_contexts_tab      => l_note_contexts,
                  x_jtf_note_id                => l_jtf_note_id,
                  p_last_update_date           => SYSDATE,
                  p_last_updated_by            => fnd_global.user_id,
                  p_creation_date              => v_cur_notes_data.creation_date,
                  --p_created_by                 => 1396,
                  p_last_update_login          => fnd_global.login_id
                 );
                fnd_file.put_line(fnd_file.LOG,'ACCESS NOTES transferred into Collections and Note ID '|| l_jtf_note_id);
                 XXEC_NOTESCON_PKG.lv_error_location:='After Calling the API JTF_NOTES_PUB';

                 XXEC_NOTESCON_PKG.lv_error_location:='Updating Status Flag in XXEC_OKL_NOTESCON_TMP table';
                 if l_return_status='S' then
                 XXEC_NOTESCON_PKG.lv_error_location:='Counting the Success records';
                   l_success := l_success+1;
                      update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                    fnd_file.put_line(fnd_file.output,'Successed Contract'||v_cur_notes_data.CONTRACT_NUMBER);
                 elsif l_return_status='E' then
                 XXEC_NOTESCON_PKG.lv_error_location:='Counting Error records';
                    l_error := l_error+1;
                      update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                  fnd_file.put_line(fnd_file.log,'Errored Contract Number '||v_cur_notes_data.CONTRACT_NUMBER);
                 else
                  XXEC_NOTESCON_PKG.lv_error_location:='Counting Error records';
                      l_error_e := l_error_e+1;
                       update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                  fnd_file.put_line(fnd_file.log,'l_error_e Errored Contract Number '||v_cur_notes_data.CONTRACT_NUMBER);
                 end if;
                 COMMIT;

         /*----------------------------------------------
           Checking for Errors
           ---------------------------------------------*/
         IF (fnd_msg_pub.count_msg > 0)
         THEN
            FOR i IN 1 .. fnd_msg_pub.count_msg
            LOOP --Loop3

            XXEC_NOTESCON_PKG.lv_error_location:='Inside Loop3';  --Intializing value for error location variable.
               fnd_msg_pub.get (p_msg_index          => i,
                                p_encoded            => 'F',
                                p_data               => l_msg_data,
                                p_msg_index_out      => l_msg_index_out
                               );
               fnd_file.put_line (fnd_file.LOG, 'API ERROR: ' || l_msg_data);
            END LOOP; --end of loop3
            XXEC_NOTESCON_PKG.lv_error_location:='End of Loop3';  --Intializing value for error location variable.
            l_notes_err_count := l_notes_err_count + 1;
         END IF;        
   END LOOP; --end of loop1
   XXEC_NOTESCON_PKG.lv_error_location:='end of Loop1';  --Intializing value for error location variable.
   COMMIT;  --commiting the processed records.

   fnd_file.put_line (fnd_file.LOG, 'Total count of ALL Customer Notes - ' || l_acount); --Printing Total Count of Customer Notes.
   fnd_file.put_line (fnd_file.LOG, 'Total Success Records Count - '|| l_success);
   fnd_file.put_line (fnd_file.log, 'Total error Records Count - '|| l_error);
   fnd_file.put_line (fnd_file.log, 'Total error Records Count e- '|| l_error_e);
   fnd_file.put_line (fnd_file.LOG, 'Total Error count  - ' || l_notes_err_count); --Printing Total error count.
   fnd_file.put_line (fnd_file.LOG, 'END of ACCESS NOTES Conversion ');
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'ERROR: ' || SQLERRM);
         fnd_file.put_line (fnd_file.log,'The error is at '||XXEC_NOTESCON_PKG.lv_error_location);
         p_errbuf_out := SQLERRM;
         p_retcode_out := 2;
 END XXEC_OKL_MAIN_PROC;
END XXEC_NOTESCON_PKG;
/
SHOW ERRORS
/

How to grant tables,views,synonyms and packages to other schema in Oracle Apps R12.2.X ?

TABLES,VIEW,SYNONYM FOR OKL:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
      ,OWNER
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OWNER||'.'||GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


TABLES,VIEW,SYNONYM FOR OKC:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

GRANTING OKL PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


GRANTING OKC PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

How to complie all objects of a schema in Oracle ?

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures,
functions, packages, and triggers in the specified schema.
The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

How to compile an invalid views and triggers by using dynamic sql in oracle ?

--TO COMPILE THE INVALID VIEWS IN APPS&XXEC SCHEMA'S.

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'VIEW'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'VIEW' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

--TO COMPILE THE INVALID TRIIGERS IN APPS&XXFIN SCHEMAS

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'TRIGGER'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'TRIGGER' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

How to compile an invalid Package Spec and Package Body by using dynamic sql in oracle ?

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   all_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  AND    owner IN ('APPS')
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

how to alter invalid objects by using altercommand in Oracle ?

alter MATERIALIZED VIEW APPS.OE_ITEMS_MV compile;
alter procedure XXFIN.XXFIN_PROC COMPILE;
ALTER FUNCTION XXFIN_XXFIN_FUN COMPILE;
alter view APPS.XXFIN_VIEW COMPILE;
ALTER SYNONYM XXEC.OKL_STRM_TYPE_V COMPILE;
alter type APPS.CSI_ITEM_INSTANCE_OBJ compile;
alter package APPS.ARW_SEARCH_CUSTOMERS compile body;
alter package APPS.CSI_WEB_SERVICE_PKG compile body;
alter trigger APPS.XXFIN_TRX_TRIGGER COMPILE;

How to compile an invalid objects in Oracle?

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

How to find an invalid objects count in Oracle Apps ?

select  COUNT(*),owner, object_type
from    all_objects
where STATUS = 'INVALID'
GROUP BY owner, object_type
ORDER BY owner, object_type;

how to create a receipt and apply the receipt in AR by using AR_RECEIPT_API_PUB API ?

create or replace PACKAGE body xxfin_cus_form_pkg
IS

--CREATING PROCEDURE TO CREATE MULTIPLE RECEIPT WITH SAME RECEIPT NUMBER FOR DIFFERENT ORG'S.
PROCEDURE xxfin_cus_form_rec_prc(
    errbuff OUT VARCHAR2 ,
    retcode OUT NUMBER ,
    ip_receipt_num VARCHAR2 ,
    ip_cus_num     VARCHAR2 )
IS
  l_return_status   VARCHAR2(1);
  l_msg_count       NUMBER;
  lv_trx_number     VARCHAR2(200);
  l_msg_data        VARCHAR2(240);
  l_cash_receipt_id NUMBER;
  p_count           NUMBER := 0;
  l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
  l_customer_trx_id         NUMBER;
  gc_user_name              VARCHAR2 (100);
  gc_responsibility_name    VARCHAR2 (100);
  gc_application_short_name VARCHAR2 (100);
  gc_org_id                 NUMBER;
  l_error_msg               VARCHAR2 (500);
  l_org_id                  NUMBER;
  l_return_status1_apply    VARCHAR2(240);
  l_status                  VARCHAR2(30);
  lv_cr_id ar_cash_receipts_all.cash_receipt_id%TYPE;
  ln_user_id           NUMBER;
  lv_receipt_method    VARCHAR2(150);
  ln_receipt_method_id NUMBER(15);
  --ln_doc_seq_value number(30);
  CURSOR recipt_create_stg
  IS
    SELECT SUM(xt.AMOUNT_APPLIED) amount,
      xt.INVOICE_CURRENCY_CODE,
      xr.RECEIPT_NUMBER,
      xr.GL_DATE,
      xt.account_number,
      xr.receipt_method,
      xr.RECEIPT_METHOD_ID,
      xr.comments,
      xr.ATTRIBUTE_CATEGORY,
      xr.ATTRIBUTE1,
      xr.ATTRIBUTE2,
      xr.ATTRIBUTE3,
      xr.ATTRIBUTE4,
      xr.attribute5,
      xt.org_id
    FROM xxfin_custom_receipt xr,
      xxfin_cust_trx xt
    WHERE xr.receipt_number=xt.receipt_number
    AND xt.rec_status      ='Y'
    AND xt.receipt_number  =ip_receipt_num
    AND xt.customer_num    =ip_cus_num
    GROUP BY xt.org_id,
      xt.INVOICE_CURRENCY_CODE,
      xr.RECEIPT_NUMBER,
      xr.GL_DATE,
      xt.account_number,
      xr.RECEIPT_METHOD_ID,
      xr.ATTRIBUTE_CATEGORY,
      xr.ATTRIBUTE1,
      xr.ATTRIBUTE2,
      xr.ATTRIBUTE3,
      xr.ATTRIBUTE4,
      xr.ATTRIBUTE5,
      xr.comments,
      xr.receipt_method;
  CURSOR cu_login_variables
  IS
    SELECT fu.user_id user_id,
      frv.responsibility_id resp_id,
      fav.application_id resp_appl_id
    FROM fnd_application_vl fav,
      fnd_responsibility_vl frv,
      fnd_user fu
    WHERE fu.user_id               = ln_user_id
    AND frv.responsibility_name    = gc_responsibility_name
    AND fav.application_short_name = gc_application_short_name;
  lr_login_variables cu_login_variables%ROWTYPE;
  lv_receipt_number VARCHAR2(200);
BEGIN
  ln_user_id:=fnd_global.user_id;
  DELETE FROM xxfin_cust_trx WHERE rec_status='N';
  COMMIT;
  --    l_org_id:=fnd_profile.value(l_org_id);
  FOR cur_create_stg IN recipt_create_stg
  LOOP
    -- xxfin_cust_trx_prc_apply(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,cur_create_stg.TRX_NUMBER,'Step0',l_msg_data);
    BEGIN
      SELECT lookup_code,
        meaning,
        description,
        tag
      INTO gc_org_id,
        gc_user_name,
        gc_responsibility_name,
        gc_application_short_name
      FROM fnd_lookup_values_vl
      WHERE enabled_flag = 'Y'
      AND lookup_code    = cur_create_stg.org_id--'329'--rec_ar_dt.org_id
      AND lookup_type    = 'XXIMS_APPLICATION_INITIATION12';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_error_msg := 'select for XXIMS_APPLICATION_INITIATION12 failed. ';
      fnd_file.put_line (fnd_file.LOG, l_error_msg);
      RAISE;
    WHEN TOO_MANY_ROWS THEN
      l_error_msg := 'select for XXIMS_APPLICATION_INITIATION12 failed due to too many rows. ';
      fnd_file.put_line (fnd_file.LOG, l_error_msg);
      RAISE;
    END;
    OPEN cu_login_variables;
    FETCH cu_login_variables INTO lr_login_variables;
    CLOSE cu_login_variables;
    /*   fnd_global.apps_initialize (-1, lr_login_variables.resp_id, lr_login_variables.resp_appl_id );
    mo_global.init (gc_application_short_name);
    mo_global.set_policy_context ('S', cur_trx.org_id);--'329'
    */
    -- 1) Set the applications context
    fnd_global.apps_initialize (lr_login_variables.user_id,lr_login_variables.resp_id, lr_login_variables.resp_appl_id );
    mo_global.init(gc_application_short_name);
    mo_global.set_policy_context('S',cur_create_stg.org_id);
    -- fnd_global.apps_initialize(1011902, 50559, 222,0);
    -----------------------validating for duplicate receipt.--------------------------------
    DECLARE
      lc_receipt_count NUMBER(3);
      lv_error_msg     VARCHAR2(500);
    BEGIN
      SELECT COUNT(receipt_number)
      INTO lc_receipt_count
      FROM ar_cash_receipts_all
      WHERE receipt_number = cur_create_stg.receipt_number
      AND org_id           = cur_create_stg.org_id;
      IF lc_receipt_count  >0 THEN
        lv_error_msg      := 'Error: Receipt Number ' || cur_create_stg.receipt_number || ' already in the System for '||cur_create_stg.org_id;
        --fnd_file.put_line (fnd_file.LOG, p_error_msg);
        UPDATE xxfin_cust_trx
        SET ERRBUF          =lv_error_msg
        WHERE RECEIPT_NUMBER=cur_create_stg.receipt_number
          -- AND TRX_NUMBER      =cur_create_stg.trx_number
        AND ORG_ID =cur_create_stg.org_id;
        DBMS_OUTPUT.put_line (lv_error_msg);
      ELSE
        NULL;
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      UPDATE xxfin_cust_trx
      SET ERRBUF          ='Receipt Values not found in custom receipt table'
      WHERE RECEIPT_NUMBER=cur_create_stg.receipt_number
        --AND TRX_NUMBER      =cur_create_stg.trx_number
      AND ORG_ID =cur_create_stg.org_id;
    END;
    ------------------------end of  duplicate receipt validation.--------------------------------
    BEGIN
      lv_receipt_number := cur_create_stg.RECEIPT_NUMBER;
      ----------------------------------changing the receipt method internally---------------
      BEGIN
        lv_receipt_method:=cur_create_stg.receipt_method;
        IF lv_receipt_method LIKE '%Cash%' THEN
          ln_receipt_method_id:=15002;
        elsif lv_receipt_method LIKE '%Cheque%' THEN
          ln_receipt_method_id:=15003;
        elsif lv_receipt_method LIKE '%PDC%' THEN
          ln_receipt_method_id:=15003;
          --elsif lv_receipt_method like '%Intercompany Bank Transfer acc%' then  --COMMENTED 17-OCT-16
          -- ln_receipt_method_id:=5005;  --COMMENTED 17-OCT-16
        elsif lv_receipt_method LIKE '%Bank Transfer%' THEN
          ln_receipt_method_id:=5005;
        ELSE
          ln_receipt_method_id:=cur_create_stg.RECEIPT_METHOD_ID;
        END IF;
      END;
      /*
      begin
      select xxfin_doc_sequence.nextval into ln_doc_seq_value from dual;
      end; */
      ----------------------------------------------------------------------------------------
      l_cash_receipt_id                 := NULL;
      l_attribute_rec.attribute_category:=cur_create_stg.attribute_category;
      l_attribute_rec.attribute2        :=cur_create_stg.attribute1;
      l_attribute_rec.attribute3        :=cur_create_stg.attribute2;
      l_attribute_rec.attribute4        :=cur_create_stg.attribute4;
      l_attribute_rec.attribute5        :=cur_create_stg.attribute3;
      l_attribute_rec.attribute8        :=cur_create_stg.attribute5;
      -- 2) Call the API
      AR_RECEIPT_API_PUB.CREATE_CASH ( p_api_version => 1.0, p_init_msg_list => FND_API.G_TRUE, p_commit => FND_API.G_TRUE, p_validation_level => FND_API.G_VALID_LEVEL_FULL, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_currency_code => cur_create_stg.INVOICE_CURRENCY_CODE,--'AED',
      p_amount => cur_create_stg.amount,                                                                                                                                                                                                                                                                                        --'450',
      p_receipt_number => cur_create_stg.RECEIPT_NUMBER,                                                                                                                                                                                                                                                                        --'T-149',
      p_receipt_date => TRUNC(SYSDATE),                                                                                                                                                                                                                                                                                         --'11-JUN-2016',
      p_gl_date => cur_create_stg.GL_DATE,                                                                                                                                                                                                                                                                                      --'11-JUN-2016',
      p_customer_number => cur_create_stg.account_number,                                                                                                                                                                                                                                                                       --'1231',
      p_receipt_method_id => ln_receipt_method_id,                                                                                                                                                                                                                                                                              --'11001',
      p_comments => cur_create_stg.comments, P_ORG_ID => cur_create_stg.org_id,                                                                                                                                                                                                                                                 --'329',
      p_attribute_rec => l_attribute_rec, p_cr_id => l_cash_receipt_id );
      COMMIT;
    END;
    -- 3) Review the API output
    dbms_output.put_line('Status ' || l_return_status);
    dbms_output.put_line('Cash Receipt id ' || l_cash_receipt_id );
    dbms_output.put_line('Message count ' || l_msg_count);
    l_status:='Receipt Not Created';
    --  xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
    COMMIT;
    IF l_return_status='S'
      --and l_cash_receipt_id IS NOT NULL
      --  xxfin_cust_trx_prc(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,l_status,l_msg_data);
      THEN
      BEGIN
        l_status:='Receipt Created';
        xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status,l_msg_data,cur_create_stg.org_id,ip_cus_num);
        --UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
        --  WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
        --     AND rec_status='Y';
        --  xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
        COMMIT;
        dbms_output.put_line('Message Apply: '|| cur_create_stg.org_id );
        xxfin_cus_form_pkg.xxfin_cus_form_rec_apply (ip_cash_receipt_id => l_cash_receipt_id , ip_cus_num => ip_cus_num , ip_receipt_num => cur_create_stg.RECEIPT_NUMBER , ip_org_id => cur_create_stg.org_id , op_return_status1 =>l_return_status1_apply );
        dbms_output.put_line('Message Apply2: '|| l_return_status1_apply );
      END;
      --exception--
      l_status:='Receipt Applied';
      --UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
      --   WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
      --     AND rec_status='Y';
      --xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status);  commented today
      COMMIT;
    ELSIF l_return_status='E' THEN
      l_status          :='Receipt UnApplied Error';
      xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status,l_msg_data, cur_create_stg.org_id,ip_cus_num);
      --  xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
      COMMIT;
    END IF;
    IF l_msg_count = 1 THEN
      dbms_output.put_line('l_msg_data  '||l_msg_data|| cur_create_stg.org_id|| 'Org_id');
      l_status :='Receipt UnApplied EE';
      --UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
      --   WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
      --    AND rec_status='Y';
      --  xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
      xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status,l_msg_data, cur_create_stg.org_id,ip_cus_num);
      COMMIT;
    elsif l_msg_count > 1 THEN
      LOOP
        p_count    := p_count + 1;
        l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
        l_status   :='Receipt UnApplied EEE';
        xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status,l_msg_data, cur_create_stg.org_id,ip_cus_num);
        -- UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
        --     WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
        --    AND rec_status='Y';
        --  xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status);
        COMMIT;
        IF l_msg_data IS NULL THEN
          EXIT;
        END IF;
        dbms_output.put_line('Message ' || p_count ||'. '||l_msg_data);
      END LOOP;
    END IF;
    COMMIT;
  END LOOP;
  COMMIT;
  -- Have to call miscellaneous receipt creation api here
  -- xxfin_cus_form_pkg.xxfin_misc_receipt_submit_proc(ip_receipt_num);
  DECLARE --added 06-oct-16 12:02 pm
    lv_err_msg        VARCHAR2(2000);
    lv_ret_code       VARCHAR2(2000);
    lv_receipt_number VARCHAR2(30);
  BEGIN
    lv_receipt_number:=ip_receipt_num;
    xxfin_create_misc_rec_prc(lv_err_msg,lv_ret_code,lv_receipt_number);
    dbms_output.put_line('receipt number is '||lv_receipt_number);
    dbms_output.put_line('error message is '||lv_err_msg);
    dbms_output.put_line('error code is '||lv_ret_code);
  END;
EXCEPTION
WHEN OTHERS THEN
  xxfin_cust_trx_prc_apply(l_cash_receipt_id,lv_receipt_number,lv_TRX_NUMBER,'Step2'||SQLERRM,l_msg_data,ip_cus_num);
END xxfin_cus_form_rec_prc;

--END OF  PROCEDURE XXFIN_CUS_FORM_REC_PRC.

--CREATING A PROCEDURE TO APPLY THE RECEIPTS, WHICH IS CREATED BY THE XXFIN_CUS_FORM_REC_PRC PROCEDURE.

PROCEDURE xxfin_cus_form_rec_apply(
    ip_cash_receipt_id IN NUMBER ,
    ip_cus_num         IN NUMBER ,
    ip_receipt_num     IN VARCHAR2 ,
    ip_org_id          IN NUMBER ,
    op_return_status1 OUT VARCHAR2 )
IS
  l_error_msg               VARCHAR2 (500);
  l_return_status           VARCHAR2(1);
  l_msg_count               NUMBER;
  l_msg_data                VARCHAR2(240);
  l_cash_receipt_id         NUMBER;
  p_count                   NUMBER := 0;
  L_ATTRIBUTE_REC           VARCHAR2(150);
  l_customer_trx_id         NUMBER;
  gc_user_name              VARCHAR2 (100);
  gc_responsibility_name    VARCHAR2 (100);
  gc_application_short_name VARCHAR2 (100);
  gc_org_id                 NUMBER;
  l_status                  VARCHAR2(30);
  lv_error_message          VARCHAR2(2000);
  ln_user_id                NUMBER;
  CURSOR recipt_apply_stg
  IS
    SELECT xt.AMOUNT_APPLIED,
      xr.RECEIPT_NUMBER,
      xt.org_id,
      xt.TRX_NUMBER,
      xt.apply_date
    FROM xxfin_custom_receipt xr,
      xxfin_cust_trx xt
    WHERE xr.receipt_number=xt.receipt_number
    AND xt.rec_status      ='Y'
    AND xt.receipt_number  =ip_receipt_num
    AND xt.customer_num    =ip_cus_num
    AND xt.org_id          =ip_org_id;
  CURSOR cu_login_variables
  IS
    SELECT fu.user_id user_id,
      frv.responsibility_id resp_id,
      fav.application_id resp_appl_id
    FROM fnd_application_vl fav,
      fnd_responsibility_vl frv,
      fnd_user fu
    WHERE fu.user_id               = ln_user_id
    AND frv.responsibility_name    = gc_responsibility_name
    AND fav.application_short_name = gc_application_short_name;
  lr_login_variables cu_login_variables%ROWTYPE;
  lv_trx_number NUMBER;
BEGIN
  ln_user_id:=fnd_global.user_id;
  -- l_org_id:=fnd_profile.value(l_org_id);
  FOR cur_apply_stg IN recipt_apply_stg
  LOOP
    BEGIN
      /*mo_global.init(gc_application_short_name);
      mo_global.set_policy_context('S',ip_org_id);
      fnd_global.apps_initialize (lr_login_variables.user_id, lr_login_variables.resp_id, lr_login_variables.resp_appl_id );*/

      BEGIN
        ar_receipt_api_pub.Apply ( p_api_version => 1.0 , p_init_msg_list => FND_API.G_TRUE , p_commit => FND_API.G_TRUE , p_validation_level => FND_API.G_VALID_LEVEL_FULL , p_cash_receipt_id => ip_cash_receipt_id--l_cash_receipt_id,
        ,x_return_status => l_return_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data , p_trx_number => cur_apply_stg.trx_number                                                                     ----'553533',
        , p_customer_trx_id => l_customer_trx_id , p_amount_applied => cur_apply_stg.AMOUNT_APPLIED                                                                                                                  --'450',--recpt_rec.amount,
        , p_org_id => ip_org_id                                                                                                                                                                                      --cur_apply_stg.org_id--'329'--l_org_id
        , p_apply_date => cur_apply_stg.apply_date                                                                                                                                                                   --added 12-09-16.
        ,p_show_closed_invoices => 'Y' );
        COMMIT;
      END ;
      IF l_return_status='S' THEN
        l_status       :='Receipt Applied';
        xxfin_cust_trx_prc_apply(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,cur_apply_stg.TRX_NUMBER,l_status,l_msg_data,ip_cus_num);
      ELSE--if l_return_status='E' then
        l_status:='Receipt UnApplied';
        xxfin_cust_trx_prc_apply(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,cur_apply_stg.TRX_NUMBER,l_status,l_msg_data,ip_cus_num);
      END IF;
     
    END;
  END LOOP;
  xxfin_applied_amount(ip_receipt_num);--calling the procedure to update the applied_amount and un applied amount  in custom table.
EXCEPTION
WHEN OTHERS THEN
  lv_error_message := sqlerrm;
  UPDATE xxfin_cust_trx
  SET errbuf          =lv_error_message,
    status            = 'Step6',
    CASH_RECEIPT_ID   =l_cash_receipt_id
  WHERE receipt_number=ip_receipt_num
  AND rec_status      ='Y';
  COMMIT;
END xxfin_cus_form_rec_apply;

--END OF CREATING XXFIN_CUS_FORM_REC_APPLY.

END xxfin_cus_form_pkg;
/

How to create a miscellaneous receipt by using AR_RECEIPT_API_PUB API in AR ?

create or replace PROCEDURE xxfin_create_misc_prc(
    errbuff OUT VARCHAR2,
    retcode OUT VARCHAR2,
    ip_receipt_num IN VARCHAR2)
  -- op_return_status2 OUT VARCHAR2)
AS
  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_usr_currency_code            VARCHAR2(200);
  p_currency_code                VARCHAR2(200);
  p_usr_exchange_rate_type       VARCHAR2(200);
  p_exchange_rate_type           VARCHAR2(200);
  p_exchange_rate                NUMBER;
  p_exchange_rate_date           DATE;
  p_amount                       NUMBER;
  p_org_id                       NUMBER;
  p_receipt_number               VARCHAR2(200);
  p_receipt_date                 DATE;
  p_gl_date                      DATE;
  p_receivables_trx_id           NUMBER;
  p_activity                     VARCHAR2(200) DEFAULT NULL;
  p_misc_payment_source          VARCHAR2(200):='Created by AR RECEIPT API PUB';
  p_tax_code                     VARCHAR2(200);
  p_vat_tax_id                   VARCHAR2(200);
  p_tax_rate                     NUMBER;
  p_tax_amount                   NUMBER DEFAULT NULL;
  p_deposit_date                 DATE;
  p_reference_type               VARCHAR2(200);
  p_reference_num                VARCHAR2(200);
  p_reference_id                 NUMBER;
  p_remittance_bank_account_id   NUMBER;
  p_remittance_bank_account_num  VARCHAR2(200);
  p_remittance_bank_account_name VARCHAR2(200);
  p_receipt_method_id            NUMBER;
  p_receipt_method_name          VARCHAR2(200);
  p_doc_sequence_value           NUMBER;
  p_ussgl_transaction_code       VARCHAR2(200);
  p_anticipated_clearing_date    DATE;
  p_attribute_record AR_RECEIPT_API_PUB.attribute_rec_type;
  p_global_attribute_record AR_RECEIPT_API_PUB.global_attribute_rec_type;
  p_comments                VARCHAR2(200);
  p_misc_receipt_id         NUMBER;
  p_called_from             VARCHAR2(200);
  gc_user_name              VARCHAR2 (100);
  gc_responsibility_name    VARCHAR2 (100);
  gc_application_short_name VARCHAR2 (100);
  gc_org_id                 NUMBER;
  l_error_msg               VARCHAR2 (2000);
  l_receipt_id              NUMBER;
  l_bank_ref_number         VARCHAR2(100);
  lv_receipt_method         VARCHAR2(150);
  ln_receipt_method_id      NUMBER(15);
  lv_receipt_num            VARCHAR2(30);
  ln_user_id                NUMBER:=fnd_global.user_id;
  l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
  CURSOR cu_login_variables
  IS
    SELECT fu.user_id user_id,
      frv.responsibility_id resp_id,
      fav.application_id resp_appl_id
    FROM fnd_application_vl fav,
      fnd_responsibility_vl frv,
      fnd_user fu
    WHERE fu.user_id               = ln_user_id
    AND frv.responsibility_name    = gc_responsibility_name
    AND fav.application_short_name = gc_application_short_name;
  lr_login_variables cu_login_variables%ROWTYPE;
  CURSOR misc_receipts
  IS
    SELECT SUM(xct.amount_applied) amount ,
      xcr.comments ,
      xcr.ATTRIBUTE_CATEGORY ,
      xcr.ATTRIBUTE1 ,
      xcr.ATTRIBUTE2 ,
      xcr.ATTRIBUTE3 ,
      xcr.ATTRIBUTE4 ,
      xcr.attribute5 ,
      xcr.receipt_date ,
      xcr.gl_date ,
      xct.invoice_currency_code ,
      XCR.RECEIPT_METHOD_ID ,
      XCR.RECEIPT_METHOD ,
      xcr.receipt_number
    FROM xxfin_custom_receipt xcr ,
      xxfin_cust_trx xct
    WHERE xcr.receipt_number=xct.receipt_number
      AND xcr.receipt_number=ip_receipt_num
    GROUP BY xcr.comments,
      xcr.ATTRIBUTE_CATEGORY,
      xcr.ATTRIBUTE1,
      xcr.ATTRIBUTE2,
      xcr.ATTRIBUTE3,
      xcr.ATTRIBUTE4,
      xcr.attribute5,
      xcr.receipt_date,
      xcr.gl_date,
      XCT.INVOICE_CURRENCY_CODE,
      XCR.RECEIPT_METHOD_ID,
      XCR.RECEIPT_METHOD,
      xcr.receipt_number;
BEGIN
  BEGIN
    SELECT lookup_code,
      meaning,
      description,
      tag
    INTO gc_org_id,
      gc_user_name,
      gc_responsibility_name,
      gc_application_short_name
    FROM fnd_lookup_values_vl
    WHERE enabled_flag = 'Y'
    AND lookup_code    = 367
    AND lookup_type    = 'XXIMS_APPLICATION_INIT';
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    l_error_msg := 'select for XXIMS_APPLICATION_INIT failed. ';
    fnd_file.put_line (fnd_file.LOG, l_error_msg);
    RAISE;
  WHEN TOO_MANY_ROWS THEN
    l_error_msg := 'select for XXIMS_APPLICATION_INIT failed due to too many rows. ';
    fnd_file.put_line (fnd_file.LOG, l_error_msg);
    RAISE;
  WHEN OTHERS THEN
    l_error_msg := 'select for XXIMS_APPLICATION_INIT failed due to other reasons ';
    fnd_file.put_line (fnd_file.LOG, l_error_msg);
    RAISE;
  END;
  OPEN cu_login_variables;
  FETCH cu_login_variables INTO lr_login_variables;
  CLOSE cu_login_variables;
  BEGIN
    FOR rec_misc IN misc_receipts
    LOOP
      ----------------------------changin receipt method internally------------------------------------------
      BEGIN
        lv_receipt_num   :=rec_misc.receipt_number;
        lv_receipt_method:=rec_misc.receipt_method;
        IF lv_receipt_method LIKE '%Cash%' THEN
          ln_receipt_method_id:=15062;
        elsif lv_receipt_method LIKE '%Cheque%' THEN
          ln_receipt_method_id:=15063;
        elsif lv_receipt_method LIKE '%PDC%' THEN
          ln_receipt_method_id:=15063;
        elsif lv_receipt_method LIKE '%Bank Transfer%' THEN
          ln_receipt_method_id:=50065;
        ELSE
          ln_receipt_method_id:=rec_misc.receipt_method_id;
        END IF;
      END;
      ---------------------------------------end receipt method changing internally---------------------------
      fnd_global.apps_initialize ( lr_login_variables.user_id --   -1
      , lr_login_variables.resp_id , lr_login_variables.resp_appl_id );
      mo_global.init (gc_application_short_name);
      mo_global.set_policy_context ('S', 367);
      fnd_file.put_line(fnd_file.log,'Application Code :' ||gc_application_short_name);
   
      --p_receipt_number        := rec_misc.receipt_number;
      p_receipt_date                    := SYSDATE;
      p_gl_date                         := rec_misc.gl_date ;
      p_misc_receipt_id                 := NULL;
      l_attribute_rec.attribute_category:=rec_misc.attribute_category;
      l_attribute_rec.attribute2        :=rec_misc.attribute1;
      l_attribute_rec.attribute3        :=rec_misc.attribute2;
      l_attribute_rec.attribute4        :=rec_misc.attribute4;
      l_attribute_rec.attribute5        :=rec_misc.attribute3;
      l_attribute_rec.attribute8        :=NVL(rec_misc.attribute5,'000000');
      SELECT receivables_trx_id
      INTO p_receivables_trx_id
      FROM AR_RECEIVABLES_TRX_ALL
      WHERE name IN
        (SELECT meaning
        FROM ar_lookups
        WHERE lookup_type = 'XXIMD_MISC_RECEIPT_ACTIVITY'
        ) ;
   
   
      AR_RECEIPT_API_PUB.create_misc ( p_api_version => 1.0, p_init_msg_list => FND_API.G_TRUE,
      p_commit => FND_API.G_TRUE, 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_usr_currency_code => p_usr_currency_code, p_currency_code => rec_misc.invoice_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 => rec_misc.amount, p_receipt_number => lv_receipt_num, p_receipt_date => TRUNC(SYSDATE), p_gl_date => rec_misc.gl_date , p_receivables_trx_id => p_receivables_trx_id, p_activity => p_activity, p_misc_payment_source => p_misc_payment_source, p_tax_code => p_tax_code , p_vat_tax_id => p_vat_tax_id , p_tax_rate => p_tax_rate , p_tax_amount => p_tax_amount , p_deposit_date => TRUNC(SYSDATE) , p_reference_type => p_reference_type , p_reference_num => p_reference_num , p_reference_id => p_reference_id , 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_receipt_method_id => ln_receipt_method_id , p_receipt_method_name => lv_receipt_method , 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_comments => rec_misc.comments,
      p_attribute_record => l_attribute_rec, p_misc_receipt_id => p_misc_receipt_id, p_called_from => p_called_from, P_Org_Id => 367
      );
      IF (x_return_status = 'S') THEN
        COMMIT;
        fnd_file.put_line(fnd_file.log,'SUCCESS');
     
      ELSE
        ROLLBACK;
     
        fnd_file.put_line(fnd_file.log,'ERROR');
        fnd_file.put_line(fnd_file.log,'Return Status    = '|| SUBSTR (x_return_status,1,255)||','||x_msg_data);
        fnd_file.put_line(fnd_file.log,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;
    fnd_file.put_line(fnd_file.log,'After end loop');
  EXCEPTION
  WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.log,'Exception :'||sqlerrm);
  END;
  COMMIT;
END xxfin_create_misc_prc;
/

How to Update Customer Profile by using API

CREATE OR REPLACE procedure APPS.UPDATE_CUST_COLL_PROF(ERRBUF OUT VARCHAR2
                                                  ,RETCODE OUT VARCHAR2)
   AS
   l_customer_profile_rec_type   hz_customer_profile_v2pub.customer_profile_rec_type;
   l_object_version_number        NUMBER;
   l_return_status               VARCHAR2 (10);
   l_msg_count                   NUMBER;
   l_msg_data                    VARCHAR2 (2000);
   l_customer_number             NUMBER;
   l_customer_name                VARCHAR2(100);
   l_collector_name                VARCHAR2(100);
   l_CUST_ACCOUNT_PROFILE_ID      NUMBER;
   l_process VARCHAR2(3)     :='N';
   l_cust_account_id            VARCHAR2(100);
   l_collector_id               VARCHAR2(100);
   CURSOR   cur_coll          
   IS
   SELECT xct.*,rowid row_id
   FROM temp_custgrcol_tab xct
   WHERE 1=1;

  BEGIN

 
   fnd_global.apps_initialize (user_id           => 1318,
                               resp_id           => 50559,
                              resp_appl_id      => 222
                              );
   mo_global.set_policy_context ('M','');
 
   for rec_coll in cur_coll
   loop
--   UPDATE_CUST_PROFILE.collector_name
   --START VALIDATION FOR THE REQUIRED COLUMNS
  -- when u r doing the validation, assign the value for l_return_status like ('S' FOR SUCCESS, 'E' FOR ERROR).
   --END VALIDATION FOR THE REQUIRED COLUMNS
    begin
         select hcp.CUST_ACCOUNT_PROFILE_ID,hca.CUST_ACCOUNT_ID
          into l_CUST_ACCOUNT_PROFILE_ID , l_cust_account_id
           from hz_customer_profiles hcp,
               hz_cust_accounts hca
          where hcp.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
          and hca.account_number =rec_coll.customer_number
          and hcp.site_use_id is null;
             dbms_output.put_line('Id is:'||l_CUST_ACCOUNT_PROFILE_ID);
          exception
          when no_data_found then
          l_process :='Y';
          when others then
          l_process :='Y';          
         end;
       
         /* Collector ID Receiving...*/
       
           begin
           select collector_id
           into l_collector_id
           from ar_collectors where 1=1
           and name = (select  collector_name FROM temp_custgrcol_tab xct
           WHERE 1=1
           and xct.CUSTOMER_NUMBER=rec_coll.customer_number);
           exception
           when others then
           dbms_output.put_line('Id is not received for '||rec_coll.customer_number);
           end;
/*    Initializing the Mandatory API parameters     */
   l_customer_profile_rec_type.cust_account_profile_id := l_CUST_ACCOUNT_PROFILE_ID;
   l_customer_profile_rec_type.cust_account_id := l_cust_account_id;
   l_customer_profile_rec_type.collector_id :=l_collector_id;

   SELECT   object_version_number
   INTO     l_object_version_number
   from     hz_customer_profiles
   WHERE    cust_account_profile_id = l_cust_account_profile_id;
   fnd_file.put_line(fnd_file.log,'Calling the API hz_customer_profile_v2pub.update_customer_profile');
   hz_customer_profile_v2pub.update_customer_profile
                (
                 p_init_msg_list              => fnd_api.g_true,
                 p_customer_profile_rec       => l_customer_profile_rec_type,
                 p_object_version_number      => l_object_version_number,
                 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_success
      THEN
      COMMIT;
      fnd_file.put_line(fnd_file.log, 'Updation of Customer Profile is Successful '||l_cust_account_profile_id);
      fnd_file.put_line(fnd_file.log, 'Output information ....');
      fnd_file.put_line(fnd_file.log,  'Object Version Number = '||l_object_version_number );

      fnd_file.put_line(fnd_file.log, 'Updation of Customer Profile is Successful '||l_cust_account_profile_id);
      fnd_file.put_line(fnd_file.log, 'Output information ....');
      fnd_file.put_line(fnd_file.log,  'Object Version Number = '||l_object_version_number  );

                      update  temp_custgrcol_tab
                               set STATUS_FLAG = 'S'
                           where rowid = rec_coll.row_id;

   ELSE
      fnd_file.put_line (  fnd_file.log, 'Updation of Customer Profile got failed:'
                            || l_msg_data
                           );
        fnd_file.put_line(fnd_file.log,   'Updation of Customer Profile got failed:'
                            || l_msg_data
                           );              
      ROLLBACK;
      update  temp_custgrcol_tab
                           set STATUS_FLAG = 'E'
                           where rowid = rec_coll.row_id;


   END IF;
 
   END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
  fnd_file.put_line(fnd_file.log,SQLCODE||','||SQLERRM);
   fnd_file.put_line (fnd_file.log,'Completion of API');
END UPDATE_CUST_COLL_PROF;
/