CREATE OR REPLACE PACKAGE xx_inv_complete
AS
   FUNCTION inv_complete (p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
      RETURN VARCHAR2;

   PROCEDURE defer_revenue (
      p_customer_trx_id        IN              NUMBER
    , p_customer_trx_line_id   IN              NUMBER
    , p_ext_amount             IN              NUMBER
    , x_return_status          OUT NOCOPY      VARCHAR2
    , x_msg_count              OUT NOCOPY      NUMBER
    , x_msg_data               OUT NOCOPY      VARCHAR2
    , x_adj_id                 OUT NOCOPY      NUMBER
    , x_adj_number             OUT NOCOPY      VARCHAR2
   );
END;
/

CREATE OR REPLACE PACKAGE BODY xx_inv_complete
AS
   FUNCTION inv_complete (p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
      RETURN VARCHAR2
   IS
      CURSOR get_trx_lines (p_cust_trx_id IN NUMBER)
      IS
         SELECT customer_trx_line_id, extended_amount
           FROM ra_customer_trx_lines rci
          WHERE customer_trx_id = p_cust_trx_id;

      i                       INTEGER;
      l_key                   VARCHAR2 (240)                             := p_event.geteventkey ();
      l_payment_schedule_id   NUMBER (15);
      l_customer_trx_id       NUMBER;
      l_org_id                NUMBER;
      l_user_id               NUMBER;
      l_resp_id               NUMBER;
      l_application_id        NUMBER;
      l_security_gr_id        NUMBER;
      l_counter               NUMBER;
      l_history_exists_flag   BOOLEAN;
      l_ps_exists             BOOLEAN;
      l_history_rec           ar_trx_summary_hist%ROWTYPE;
      l_tot_inv_amt           NUMBER;
      l_inv_inst_count        NUMBER;
      l_rev_adj_rec           ar_revenue_adjustment_pvt.rev_adj_rec_type;
      v_msg_index_out         NUMBER;
      v_message               VARCHAR2 (4000);
      x_return_status         VARCHAR2 (1);
      x_msg_count             NUMBER;
      x_msg_data              VARCHAR2 (4000);
      x_adj_id                NUMBER;
      x_adj_number            VARCHAR2 (100);
      j                       NUMBER;
      l_amount                NUMBER;
      l_per                   NUMBER;
      l_gl_date               DATE;
   BEGIN
      l_customer_trx_id := p_event.getvalueforparameter ('CUSTOMER_TRX_ID');
      l_org_id := p_event.getvalueforparameter ('ORG_ID');
      l_user_id := p_event.getvalueforparameter ('USER_ID');
      l_resp_id := p_event.getvalueforparameter ('RESP_ID');
      l_application_id := p_event.getvalueforparameter ('RESP_APPL_ID');
      l_security_gr_id := p_event.getvalueforparameter ('SECURITY_GROUP_ID');
      SAVEPOINT inv_complete_event;
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

      FOR i IN get_trx_lines (l_customer_trx_id)
      LOOP
         defer_revenue (l_customer_trx_id
                      , i.customer_trx_line_id
                      , i.extended_amount
                      , x_return_status
                      , x_msg_count
                      , x_msg_data
                      , x_adj_id
                      , x_adj_number
                       );
      END LOOP;

      RETURN 'SUCCESS';
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK TO inv_complete_event;
         fnd_message.set_name ('AR', 'GENERIC_MESSAGE');
         fnd_message.set_token ('GENERIC_TEXT', SQLERRM);
         fnd_msg_pub.ADD;
         wf_core.CONTEXT ('AR_BUS_EVENT_SUB_PVT'
                        , 'INV_COMPLETE'
                        , 'oracle.apps.ar.transaction.Invoice.complete'
                        , p_subscription_guid
                         );
         RETURN 'ERROR';
   END inv_complete;

   PROCEDURE defer_revenue (
      p_customer_trx_id        IN              NUMBER
    , p_customer_trx_line_id   IN              NUMBER
    , p_ext_amount             IN              NUMBER
    , x_return_status          OUT NOCOPY      VARCHAR2
    , x_msg_count              OUT NOCOPY      NUMBER
    , x_msg_data               OUT NOCOPY      VARCHAR2
    , x_adj_id                 OUT NOCOPY      NUMBER
    , x_adj_number             OUT NOCOPY      VARCHAR2
   )
   IS
      l_rev_adj_rec     ar_revenue_adjustment_pvt.rev_adj_rec_type;
      v_msg_index_out   NUMBER;
      v_message         VARCHAR2 (4000);
      j        CONSTANT NUMBER                                     := 12;
      l_amount          NUMBER;
      l_per             NUMBER;
      l_gl_date         DATE;
   BEGIN
      l_amount := (p_ext_amount / j);
      l_per := (l_amount / p_ext_amount);

      FOR i IN 0 .. j
      LOOP
         l_gl_date := SYSDATE + (i * 30);
         l_rev_adj_rec.customer_trx_id := p_customer_trx_id;
         l_rev_adj_rec.adjustment_type := 'UN';
         l_rev_adj_rec.amount_mode := 'A';
         l_rev_adj_rec.amount := l_amount;
         l_rev_adj_rec.PERCENT := l_per;
         l_rev_adj_rec.line_selection_mode := 'S';
         l_rev_adj_rec.from_cust_trx_line_id := p_customer_trx_line_id;
         l_rev_adj_rec.reason_code := 'RA';
         l_rev_adj_rec.comments := 'TEST';
         l_rev_adj_rec.gl_date := l_gl_date;
         ar_revenueadjust_pub.unearn_revenue (p_api_version            => 2.0
                                            , x_return_status          => x_return_status
                                            , x_msg_count              => x_msg_count
                                            , x_msg_data               => x_msg_data
                                            , p_rev_adj_rec            => l_rev_adj_rec
                                            , x_adjustment_id          => x_adj_id
                                            , x_adjustment_number      => x_adj_number
                                             );
         DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status);
         DBMS_OUTPUT.put_line ('Msg Count is :' || x_msg_count);
         DBMS_OUTPUT.put_line ('Adjustment ID is :' || x_adj_id);
         DBMS_OUTPUT.put_line ('Adjustment Number is :' || x_adj_number);

         IF x_msg_count > 0
         THEN
            FOR v_index IN 1 .. x_msg_count
            LOOP
               fnd_msg_pub.get (p_msg_index          => v_index
                              , p_encoded            => 'F'
                              , p_data               => x_msg_data
                              , p_msg_index_out      => v_msg_index_out
                               );
               v_message := SUBSTR (x_msg_data, 1, 200);
               DBMS_OUTPUT.put_line (x_msg_data);
               DBMS_OUTPUT.put_line ('============================================================');
            END LOOP;

            DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
            DBMS_OUTPUT.put_line ('============================================================');
         END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         fnd_message.set_name ('AR', 'GENERIC_MESSAGE');
         fnd_message.set_token ('GENERIC_TEXT', SQLERRM);
         fnd_msg_pub.ADD;
   END defer_revenue;
END xx_inv_complete;
/

SHOW errors