Invoice and Credit Memo Applied for Oracle R12

Hi,
I want to share about Invoice and Credit Memo apply in Oracle R12 module Account Receivables.

Query to find transactions which integrated between Invoice and Credit Memo.

--Query List for AR credit memo transactions
SELECT payment_schedule_id,ract.customer_trx_id,
       ract.trx_number,
       ract.org_id,ractl.extended_amount,
       ract.reason_code,
       ractl.line_type,
       ABS(ractl.gross_extended_amount) gross_extended_amount
FROM ar_payment_schedules_all ps, ra_customer_trx_all ract, ra_customer_trx_lines_all ractl
WHERE     ps.customer_trx_id = ract.customer_trx_id
     AND ract.customer_trx_id = ractl.customer_trx_id
     AND ract.customer_trx_id = ractl.customer_trx_id
     AND ract.org_id = ractl.org_id
     AND ract.org_id = :p_org_id
     AND ractl.line_type = 'LINE'
     AND ract.trx_number = :p_cm_number
ORDER BY ract.customer_trx_id;

API Script for Programaticaly transactions

/* Formatted on 4/25/2016 4:24:01 PM (QP5 v5.163.1008.3004)
* API in Function script, with parameter : 
* Credit_memo_number, Invoice_number, Applied_amount, Invoice_org_id 
*/

CREATE OR REPLACE FUNCTION APPS.XMII_AR_APPLY_CM_FNC (
   p_credit_memo    VARCHAR2,
   p_invoice_num    VARCHAR2,
   p_amt            NUMBER,
   ln_org_id        NUMBER
   )
   RETURN NUMBER
IS
   ln_request_id                 NUMBER;
   lc_inv                        NUMBER;
   lb_complete                   BOOLEAN;
   lc_phase                      VARCHAR2 (100);
   lc_status                     VARCHAR2 (100);
   lc_dev_phase                  VARCHAR2 (100);
   lc_dev_status                 VARCHAR2 (100);
   lc_message                    VARCHAR2 (100);
   v_cm_payment_schedule         NUMBER; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
   v_inv_payment_schedule        NUMBER; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
   v_amount_applied              NUMBER; -- Amount of credit memo to apply to invoice
   v_apply_date                  DATE := SYSDATE;
   v_gl_date                     DATE := SYSDATE - 1;
   v_ussgl_transaction_code      VARCHAR2 (1024); -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
   v_null_flex                   VARCHAR2 (1024); -- null, unless you have flexfield segments to define
   v_customer_trx_line_id        NUMBER; -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
   v_comments                    VARCHAR2 (240) := 'Applied by API'; --automatically';
   v_module_name                 VARCHAR2 (128); -- := 'XXCREDIT_APPLICATION.APPLY_CREDIT_MEMO'; -- If null, validation won't occur
   v_module_version              VARCHAR2 (128) := '1'; -- If null, validation won't occur
   lc_msg_txt                    VARCHAR2 (1000);
   --
   -- Output
   --
   v_out_rec_application_id      NUMBER;
   v_acctd_amount_applied_from   NUMBER;
   v_acctd_amount_applied_to     NUMBER;
   --ln_org_id                     NUMBER := fnd_profile.VALUE ('ORG_ID');
   lc_trx                        NUMBER;
   
BEGIN
   BEGIN
      SELECT payment_schedule_id
        INTO v_cm_payment_schedule
        FROM ar_payment_schedules_all ps, ra_customer_trx_all ract
       WHERE     ps.customer_trx_id = ract.customer_trx_id
             AND ract.trx_number = p_credit_memo
             AND ract.org_id = ln_org_id;
   EXCEPTION WHEN OTHERS THEN 
        dbms_output.put_line('error mapping v_cm_payment_schedule : '||sqlerrm);
   END;
   
   BEGIN
      SELECT payment_schedule_id
        INTO v_inv_payment_schedule
        FROM ar_payment_schedules_all ps, ra_customer_trx_all ract
       WHERE     ps.customer_trx_id = ract.customer_trx_id
             AND ract.trx_number = p_invoice_num
             AND ract.org_id = ln_org_id;
   EXCEPTION WHEN  OTHERS THEN
        dbms_output.put_line('error mapping v_inv_payment_schedule : '||sqlerrm);
   END;
   
   --EXCEPTION
   --   WHEN OTHERS
   --   THEN
   --      v_cm_payment_schedule := 0;
   --      v_inv_payment_schedule := 0;
   --      dbms_outout
   --END;

   v_amount_applied := p_amt;

   --fnd_client_info.set_org_context (8108);
   
   Mo_global.init('AR'); 
   Mo_global.set_policy_context('S', 8108);

   arp_process_application.cm_application (
      p_cm_ps_id                    => v_cm_payment_schedule,
      p_invoice_ps_id               => v_inv_payment_schedule,
      p_amount_applied              => v_amount_applied,
      p_apply_date                  => v_apply_date,
      p_gl_date                     => v_gl_date,
      p_ussgl_transaction_code      => v_ussgl_transaction_code,       -- NULL
      p_attribute_category          => v_null_flex,                    -- NULL
      p_attribute1                  => v_null_flex,                    -- NULL
      p_attribute2                  => v_null_flex,                    -- NULL
      p_attribute3                  => v_null_flex,                    -- NULL
      p_attribute4                  => v_null_flex,                    -- NULL
      p_attribute5                  => v_null_flex,                    -- NULL
      p_attribute6                  => v_null_flex,                    -- NULL
      p_attribute7                  => v_null_flex,                    -- NULL
      p_attribute8                  => v_null_flex,                    -- NULL
      p_attribute9                  => v_null_flex,                    -- NULL
      p_attribute10                 => v_null_flex,                    -- NULL
      p_attribute11                 => v_null_flex,                    -- NULL
      p_attribute12                 => v_null_flex,                    -- NULL
      p_attribute13                 => v_null_flex,                    -- NULL
      p_attribute14                 => v_null_flex,                    -- NULL
      p_attribute15                 => v_null_flex,                    -- NULL
      p_global_attribute_category   => v_null_flex,                    -- NULL
      p_global_attribute1           => v_null_flex,                    -- NULL
      p_global_attribute2           => v_null_flex,                    -- NULL
      p_global_attribute3           => v_null_flex,                    -- NULL
      p_global_attribute4           => v_null_flex,                    -- NULL
      p_global_attribute5           => v_null_flex,                    -- NULL
      p_global_attribute6           => v_null_flex,                    -- NULL
      p_global_attribute7           => v_null_flex,                    -- NULL
      p_global_attribute8           => v_null_flex,                    -- NULL
      p_global_attribute9           => v_null_flex,                    -- NULL
      p_global_attribute10          => v_null_flex,                    -- NULL
      p_global_attribute11          => v_null_flex,                    -- NULL
      p_global_attribute12          => v_null_flex,                    -- NULL
      p_global_attribute13          => v_null_flex,                    -- NULL
      p_global_attribute14          => v_null_flex,                    -- NULL
      p_global_attribute15          => v_null_flex,                    -- NULL
      p_global_attribute16          => v_null_flex,                    -- NULL
      p_global_attribute17          => v_null_flex,                    -- NULL
      p_global_attribute18          => v_null_flex,                    -- NULL
      p_global_attribute19          => v_null_flex,                    -- NULL
      p_global_attribute20          => v_null_flex,                    -- NULL
      p_customer_trx_line_id        => v_customer_trx_line_id,         -- NULL
      p_comments                    => v_comments,                     -- NULL
      p_module_name                 => v_module_name,                  -- NULL
      p_module_version              => v_module_version,               -- NULL
      p_out_rec_application_id      => v_out_rec_application_id,        --NULL
      p_acctd_amount_applied_from   => v_acctd_amount_applied_from,    -- NULL
      p_acctd_amount_applied_to     => v_acctd_amount_applied_to       -- NULL
                                                                );
      
   COMMIT;
  
   RETURN ln_request_id;
EXCEPTION WHEN OTHERS THEN 
   dbms_output.put_line(sqlerrm);
   RETURN -99999;
END;
/

Reference :
Ediyanto’s blog post
ERPQueries Blogspot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s