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
Blog of Andreas Victor 