DELETE AR INVOICE API EBS R12

 DECLARE

   CURSOR cur_all_trx

   IS

     

    SELECT ROWID,

             NULL PARTY_NUMBER,

             RCT.ORG_ID,

             RCT.CUSTOMER_TRX_ID,

             RCT.TRX_NUMBER

        FROM XX_DELETE_TRANSACTION RCT

       WHERE NVL (V_RET_STATUS, 0) <> 'S'

         AND TRX_NUMBER = 'inv';

       


   xv_msg_data     VARCHAR2 (4000) := NULL;

   xv_msg_count    NUMBER := 0;

   v_msg_index     NUMBER := 0;

   xv_ret_status   VARCHAR2 (1) := NULL;

   v_message_tbl   arp_trx_validate.message_tbl_type;

   v_res           VARCHAR2 (4000) := NULL;

   v_res_name      VARCHAR2 (4000) := NULL;

   v_app           VARCHAR2 (4000) := NULL;

   v_user          NUMBER := 1110;

BEGIN

   DBMS_OUTPUT.put_line ('Detele Transaction...');


   FOR c_rec IN cur_all_trx

   LOOP

      DBMS_OUTPUT.put_line ('   Transaction No.: ' || c_rec.trx_number);

      DBMS_OUTPUT.put_line ('   Transaction ID : ' || c_rec.customer_trx_id);

      DBMS_OUTPUT.put_line ('   Org ID         : ' || c_rec.org_id);

      ----------------------------------------------------------------------------

      ---- Setting the org context for the particular session

      apps.mo_global.set_policy_context ('S', c_rec.org_id);


      -- apps.mo_global.init('AR');


      SELECT application_id, responsibility_id

        INTO v_app, v_res

        FROM fnd_responsibility_tl        

       WHERE responsibility_id = 50841

       and LANGUAGE='US';


      ---- Setting the oracle applications context for the particular session

      apps.fnd_global.apps_initialize (v_user, v_res, v_app);

      ----------------------------------------------------------------------------

      xv_ret_status := NULL;

      xv_msg_count := NULL;

      xv_msg_data := NULL;


      --update the Allow Transaction Deletion to Yes to Delete (As mentioned above, better to do it from application)

      UPDATE ar_system_parameters_all

         SET invoice_deletion_flag = 'Y'

       WHERE org_id = c_rec.org_id;


      ar_invoice_api_pub.delete_transaction (

         p_api_name           => 'Delete_Transaction',

         p_api_version        => 1.0,

         p_init_msg_list      => fnd_api.g_true,

         p_commit             => fnd_api.g_false,

         p_validation_level   => fnd_api.g_valid_level_full,

         p_customer_trx_id    => c_rec.customer_trx_id,

         p_return_status      => xv_ret_status,

         p_msg_count          => xv_msg_count,

         p_msg_data           => xv_msg_data,

         p_errors             => v_message_tbl);


      UPDATE xx_delete_transaction

         SET v_ret_status = xv_ret_status

       WHERE ROWID = c_rec.ROWID;


      UPDATE xx_delete_transaction

         SET v_msg_count = xv_msg_count

       WHERE ROWID = c_rec.ROWID;




      IF xv_ret_status <> 'S'

      THEN

         DBMS_OUTPUT.put_line ('   Status: ' || xv_ret_status);


         UPDATE xx_delete_transaction

            SET v_msg_data = v_ret_status

          WHERE ROWID = c_rec.ROWID;


         FOR i IN 1 .. xv_msg_count

         LOOP

            apps.fnd_msg_pub.get (i,

                                  apps.fnd_api.g_false,

                                  xv_msg_data,

                                  v_msg_index);

            DBMS_OUTPUT.put_line ('   Error : ' || xv_msg_data);

         END LOOP;


         DBMS_OUTPUT.put_line ('   ' || xv_msg_data);

      ELSE

         DBMS_OUTPUT.put_line ('   Deleted.');


         -- Revert back to the original value for the deletion flag

         UPDATE ar_system_parameters_all

            SET invoice_deletion_flag = 'N'

          WHERE org_id = c_rec.org_id;

      END IF;


      DBMS_OUTPUT.put_line ('--------------------');

--      COMMIT;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;



/

--------------------------------table creation

CREATE TABLE XX_DELETE_TRANSACTION

AS

   SELECT CUSTOMER_TRX_ID,

          TRX_DATE,

          TRX_NUMBER,

          BILL_TO_CUSTOMER_ID,

          BILL_TO_SITE_USE_ID,

          ORG_ID,

          CREATION_DATE,

          CREATED_BY,

          'N' V_RET_STATUS,

          'N' V_MSG_COUNT,

          'N' V_MSG_DATA,

          'N' V_MESSAGE_TBL

     FROM RA_CUSTOMER_TRX_ALL     

    WHERE ORG_ID = 121 

--      AND CUST_TRX_TYPE_ID = 9437 -- MIG LOCAL INVOICE 

--      AND TRUNC (CREATION_DATE) = '04-FEB-18';

      AND TRX_NUMBER = 'inv';

/

select * from XX_DELETE_TRANSACTION      

/

drop table XX_DELETE_TRANSACTION

/

set serveroutput on ;

Comments

Post a Comment

Popular posts from this blog

opaque schema xsd (standard file used in OIC)

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>
Read more

DOCUMENTATION ON SANDBOX and PERSONALIZATION

Image
DOCUMENTATION ON SANDBOX PERSONALIZATION Prepared by VAMSI GOVATHOTI ROLE: ORACLE APPS TECHNICAL CONSULTANT Contents A) What is a Sandbox? 2 B) Purpose of Sandboxes 2 C) Key Features 2 D) Why Use Sandboxes? 2 1. How to Customize (Enable or Disable) the Fields/Components in the Sandbox 6 2. How to Enable Direct Access from User Interface (UI) to the Desired Destination using an URL 9 3. How to Create “Role Based Access control” to particular Fields / Components 13 4. ‘More Tasks’ Tab to be renamed to 'Create' on Purchase Requisitions page. 18 5. By using Application Composer making field required in sand box 23 6. Personalization by using application composer 27 A) By using Application Composer creating new field in sand box 27 B) Creating of currency field with fixed choice list 32 C) Creating of currency field with dynamic choice list. 36 7. Creating a button in sand box 40 8. Creating a global function for runtime mess...
Read more

SQL,PLSQL interview practice and DSA patterns

 --=============sql=========================== --How to find duplicate records with the number they are duplicated? select job,count(*) from emp group by job; --Write a Query to select to the Nth highest salary from a table? select min(sal) from (select  sal from emp order by sal desc) where rownum < 3;   --Can Dual table be deleted or dropped or altered or inserted? select round(123.45) from dual; --it cant be deleted or dropped or altered or inserted --List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps? select * from emp where deptno = 20  and sal>(select avg(sal)from emp where deptno = 10); --List the empno, ename, sal, dname of all the 'Mgrs' and 'Analyst' working in NEWYORK, --DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc? select empno,ename,sal,dname from emp,dept where emp.deptno= dept.deptno and  loc in('NEWYORK', 'DALLAS') and job in ('manager','analyst')and trunc(mo...
Read more