1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Accrual Data Cleanup

Discussion in 'Oracle Apps Technical' started by Iwanhe, Jun 29, 2015.

  1. Iwanhe

    Iwanhe Active Member

    Messages:
    30
    Likes Received:
    2
    Trophy Points:
    135
    Location:
    Indonesia
    Introduction

    The code here is used for delete all data generated from Accrual Load Run program, if using Online Accrue (perpetual)

    Background

    After using the PL/SQL code, you can run accrual load run again and use AP and PO Reconciliation Report to see the total balance match with Trial Balance ending balance or no for the accrual account

    The code

    Code (SQL):
    -- Accrual data clean up script
    SET SERVEROUT ON
    DECLARE
      p_operating_unit NUMBER := &OP_UNIT_ID;
      p_operating_unit_validate NUMBER := &ReEnter_OU_ID;

    BEGIN

     IF p_operating_unit <> p_operating_unit_validate THEN
        dbms_output.put_line(' Incorrect Operating Unit provided.' );
        dbms_output.put_line(' Data cleanup not done.... ');
        RETURN;
     END IF;

     DELETE FROM   cst_write_offs
      WHERE operating_unit_id = p_operating_unit
        AND EXISTS
            (SELECT 1
               FROM    po_accrual_write_offs_all pawo
              WHERE   pawo.write_off_id=cst_write_offs.write_off_id);


     DELETE FROM   cst_write_off_details
      WHERE operating_unit_id = p_operating_unit
        AND EXISTS
            (SELECT 1
       FROM po_accrual_write_offs_all pawo
      WHERE pawo.write_off_id=cst_write_off_details.write_off_id);

      dbms_output.put_line(' Cleaned up Write Offs....' );

     DELETE  FROM   CST_RECONCILIATION_BUILD
      WHERE  operating_unit_id = p_operating_unit;

     DELETE  FROM   CST_RECONCILIATION_SUMMARY
      WHERE  operating_unit_id = p_operating_unit;

     DELETE  FROM   cst_misc_reconciliation
      WHERE  operating_unit_id = p_operating_unit;

     DELETE FROM   CST_AP_PO_RECONCILIATION
      WHERE  operating_unit_id = p_operating_unit;

      dbms_output.put_line(' Data clean Up Complete....' );

     COMMIT;

    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        dbms_output.put_line(' Exception Raised : ' || SQLERRM);
    END;
    /
     
    References
    Oracle Support