This discussion is archived
2 Replies Latest reply: Dec 4, 2012 2:16 AM by 976976 RSS

Ar Invoice With Std Tax Loading by Using Api

976976 Newbie
Currently Being Moderated
Hi Experts ,

I am trying to load AR Invoice With Std Tax , Only Invoice Getting load , but tax not reflecting in distribution , I mean Std tax (EBs Tax) , can any one help me Over this,

My code is :

/* Formatted on 11/29/2012 5:23:32 PM (QP5 v5.114.809.3010) */
DECLARE
-- Initializing Block
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
p_header_id number := 1;
p_trx_header_id number := 2;
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_cust_trx_id NUMBER;
l_tbl_count NUMBER;
l_tbl_count_t number;
l_header_s NUMBER;
l_lines_s NUMBER;
l_vat_tax_id NUMBER;
l_lines_s_tax number; -- added by shagul
l_org_header_id NUMBER;
l_org_order_num VARCHAR2 (50);
l_org_order_type VARCHAR2 (120);
l_return_code VARCHAR2 (1);
l_error_msg VARCHAR2 (2000);
l_batch_source_id NUMBER;
l_count NUMBER;
l_trx_number VARCHAR2 (50);
l_trx_date DATE;

-- Initializing Cursor Block
CURSOR c_header (p_header_id NUMBER)
IS
SELECT ooh.header_id,
ooh.order_number,
ooh.source_document_id,
ooh.transactional_curr_code curr_code,
ooh.salesrep_id,
ooh.sold_to_org_id,
ooh.invoice_to_org_id,
ooh.payment_term_id,
ooh.ordered_date,
'PPGPL SHIP ONLY ORDER' order_type
FROM oe_order_headers_all ooh
WHERE HEADER_ID = 140061;

CURSOR c_lines (p_header_id NUMBER)
IS
SELECT ool.line_number,
ool.line_id,
ool.inventory_item_id,
ool.ordered_quantity,
ool.order_quantity_uom,
ool.unit_selling_price,
ool.tax_code,
ool.ship_from_org_id
FROM oe_order_lines_all ool
WHERE HEADER_ID = 140061;

CURSOR c_errors (p_trx_header_id NUMBER)
IS
SELECT error_message FROM ar_trx_errors_gt;
BEGIN
-- Application Initializing
-- fnd_global.apps_initialize (fnd_global.user_id,
-- fnd_global.resp_id,
-- fnd_global.resp_appl_id);
-- arp_global.init_global;
-- arp_standard.init_standard;
BEGIN
mo_global.init ('AR');
-- MO_GLOBAL.SET_POLICY_CONTEXT ('S', 321);
END;
fnd_global.apps_initialize (-1, 20678, 222);

-- Header Loop
FOR l_header IN c_header (p_header_id)
LOOP
SELECT 0 f INTO l_count FROM DUAL;

-- WHERE interface_line_attribute1 = '12939'
-- AND interface_line_attribute2 = 'PPGPL SHIP ONLY ORDER';

BEGIN
SELECT 140061 header_id,
'12948' order_number,
'PPGPL SHIP ONLY ORDER' order_type
INTO l_org_header_id, l_org_order_num, l_org_order_type
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

IF l_count = 0
THEN
BEGIN
-- Get the Header sequence Number
SELECT ppgpl_ar_trx_headers_s.NEXTVAL INTO l_header_s FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

-- Get the Batch Source id
BEGIN
SELECT batch_source_id
INTO l_batch_source_id
FROM ra_batch_sources_all
WHERE NAME = 'PPGPL SHIP ONLY';
EXCEPTION
WHEN OTHERS
THEN
l_batch_source_id := NULL;
END;

-- Get the Original Bill Only Invoice Number
BEGIN
SELECT '12948- Copy', SYSDATE trx_date
INTO l_trx_number, l_trx_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_trx_number := NULL;
END;

-- If the order is ship only and have the original order
IF l_org_header_id IS NOT NULL AND l_trx_number IS NOT NULL
THEN
-- Assinging values to header records
l_batch_source_rec.batch_source_id := l_batch_source_id;
l_tbl_count := 0;
l_tbl_count_t := 0;
l_trx_header_tbl (1).trx_header_id := l_header_s;
l_trx_header_tbl (1).trx_number := l_trx_number || 'D';
l_trx_header_tbl (1).trx_date := l_trx_date;
l_trx_header_tbl (1).gl_date := '15-OCT-2012';
l_trx_header_tbl (1).trx_currency := l_header.curr_code;
l_trx_header_tbl (1).cust_trx_type_id := 1;
l_trx_header_tbl (1).primary_salesrep_id := l_header.salesrep_id;
l_trx_header_tbl (1).bill_to_customer_id :=
l_header.sold_to_org_id;
l_trx_header_tbl (1).bill_to_site_use_id :=
l_header.invoice_to_org_id;
l_trx_header_tbl (1).term_id := l_header.payment_term_id;
l_trx_header_tbl (1).finance_charges := 'N';
l_trx_header_tbl (1).status_trx := 'OP';

-- Lines Loop Start
FOR l_lines IN c_lines (p_header_id)
LOOP
l_tbl_count := l_tbl_count + 1;
l_tbl_count_t := l_tbl_count_t + 1;
-- Get Sequence and Vat tax id
BEGIN
SELECT ppgpl_ar_trx_lines_s.NEXTVAL
INTO l_lines_s
FROM DUAL;

SELECT ppgpl_ar_trx_lines_s.NEXTVAL
INTO l_lines_s_tax --- added by shagul
FROM DUAL;

IF l_lines.tax_code IS NOT NULL
THEN
SELECT vat_tax_id
INTO l_vat_tax_id
FROM ar_vat_tax_all_b
WHERE tax_code = 'VAT (15%)';
ELSE
l_vat_tax_id := NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_vat_tax_id := NULL;
END;

-- Assigning Values to lines Record
l_trx_lines_tbl (l_tbl_count).trx_header_id := l_header_s;
l_trx_lines_tbl (l_tbl_count).trx_line_id := l_lines_s;
l_trx_lines_tbl (l_tbl_count).line_number :=
l_lines.line_number;
l_trx_lines_tbl (l_tbl_count).inventory_item_id :=
l_lines.inventory_item_id;
l_trx_lines_tbl (l_tbl_count).uom_code :=
l_lines.order_quantity_uom;
l_trx_lines_tbl (l_tbl_count).quantity_invoiced :=
l_lines.ordered_quantity;
l_trx_lines_tbl (l_tbl_count).quantity_ordered :=
l_lines.ordered_quantity;
l_trx_lines_tbl (l_tbl_count).vat_tax_id := l_vat_tax_id;
-- l_trx_lines_tbl (l_tbl_count).TAXABLE_FLAG := 'Y'; -- newly added
l_trx_lines_tbl (l_tbl_count).unit_selling_price :=
l_lines.unit_selling_price;
l_trx_lines_tbl (l_tbl_count).line_type := 'LINE';
l_trx_lines_tbl (l_tbl_count).warehouse_id :=
l_lines.ship_from_org_id;
l_trx_lines_tbl (l_tbl_count).sales_order :=
l_header.order_number;
l_trx_lines_tbl (l_tbl_count).sales_order_line :=
l_lines.line_number;
l_trx_lines_tbl (l_tbl_count).sales_order_date :=
l_header.ordered_date;
l_trx_lines_tbl (l_tbl_count).interface_line_context :=
'ORDER ENTRY';
l_trx_lines_tbl (l_tbl_count).interface_line_attribute1 :=
l_header.order_number;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute2 :=
l_header.order_type;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute3 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute4 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute5 :=
l_lines.line_id;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute6 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute7 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute8 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute9 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute10 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute11 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute12 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute13 := 0;
l_trx_lines_tbl (l_tbl_count).interface_line_attribute14 := 0;


END LOOP;
END IF;
END IF;
END LOOP;

DBMS_OUTPUT.put_line ('Sucess');

-- If any records are processed then call the API
IF l_tbl_count > 0
THEN
BEGIN
ar_invoice_api_pub.create_single_invoice (
p_api_version => 1.0,
-- p_commit => fnd_api.g_true,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl,
x_customer_trx_id => l_cust_trx_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Failed' || SQLERRM);
END;

-- Checking the return status
IF l_return_status = fnd_api.g_ret_sts_error
OR l_return_status = fnd_api.g_ret_sts_unexp_error
THEN
fnd_file.put_line (fnd_file.LOG,
'Unexpected Errors Found!1 - ' || l_msg_data);
DBMS_OUTPUT.put_line ('eror' || SQLERRM || l_msg_data);
ELSE
IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
THEN
fnd_file.put_line (fnd_file.LOG, 'Invoice suceessfully created!');
DBMS_OUTPUT.put_line ('Invoice suceessfully created!');
ELSE
fnd_file.put_line (fnd_file.LOG, 'Errors found! ' || l_msg_data);
DBMS_OUTPUT.put_line (
'Unexpected Errors Found12! - ' || SQLERRM || l_errors.error_message
);

FOR l_errors IN c_errors (l_header_s)
LOOP
fnd_file.put_line (fnd_file.LOG, l_errors.error_message);
END LOOP;
END IF;
END IF;
ELSE
fnd_file.put_line (fnd_file.LOG,
'There are no records to process the transaction');
END IF;


COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Unexpected Errors Found! - ' || SQLERRM);
DBMS_OUTPUT.put_line( 'Unexpected Errors Found!!100 - '
|| SQLERRM
|| 'CHECK'
|| l_msg_data);
END;


Thanks in Advance
shagul

Edited by: 973973 on Nov 29, 2012 5:42 PM

Edited by: 973973 on Nov 29, 2012 6:43 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points