[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Fix : Commented the voucher_num insert into the ap_invoices_interface table
4 23/02/2007 bduvarag for Bug#4990941, File version 120.8
Forward porting the changes done in 11i bug 4709459
5 04/11/2007 bduvarag for Bug#5607160, File version 120.9
Forward porting the changes done in 11i bug#5591827
6 04/17/2007 vkaranam for Bug#5989740, File version 120.10
Forward porting the changes done in 11i bug#5583832
7 04-Jul-2007 kukumar for bug# 5593895, File version 120.12,120.13 ( brathod changed for 120.11 )
Projects changes are not included in this checkin and GSCC error resolved.
8 04-Jul-2007 Forward porting iSupplier changes
Forward porting the changes done in 11i bug#5961325 bug#3637364
9 17-DEC-2007 Jia Li for Tax inclusive computation
10 24-Jan-2008 Modifed by Jason Liu for retroactive price
11 14-APR-2008 Kevin Cheng for bug#6962018
change return value from 1 to ratio of AP invoice quantity to PO item quantity for
partially recoverable issue.
12 11-Mar-2011 Wenqiong for bug #1168411,File version 120.11
Updated create_boe_invoice, and added new procedure/function ap_void_check,
get_boe_applied_status,check_boe_payment.
13 24-NOV-2011 mmurtuza for bug 13423031
Description: AP - HEADER LEVEL LE DOES NOT COME CORRECT AT THE TIME OF TDS GENERATED INVOICES
Fix: Added cursor cur_get_base_legal_entity_id in insert_ap_inv_interface
procedure to populate legal_entity_id from the base invoice
14 15-MAR-2013 zhiwei.xin Forward porting for DTC ER bug#13359892
---------------------------------------------------------------------------------------------------------
*/
GV_MODULE_PREFIX CONSTANT VARCHAR2(30) := 'jai_ap_utils_pkg'; -- -- Added by Jia Li for tax inclusive computation on 2007/12/26
SELECT NVL(MAX(line_number),0) + 1 line_num
FROM ap_invoice_lines_interface
WHERE invoice_id = inv_id;
SELECT jibh.tr6_date
FROM JAI_CMN_RG_PLA_HDRS jibh,
PO_VENDORS pv,
PO_VENDOR_SITES_ALL pvs
WHERE jibh.PLA_ID = id
AND pvs.vendor_site_id (+)= jibh.vendor_site_id
AND pv.vendor_id = jibh.vendor_id;
SELECT 'PLA/Invoice/'||TO_CHAR(p_org_id) inv_num
FROM DUAL;
Removed select and added cursor.
*/
CURSOR multi_org_installed is
SELECT decode(multi_org_flag, 'Y', 1,0) multi_org_cnt
FROM fnd_product_groups;
Select ap_invoices_interface_s.nextval
Into inv_interface_id
From dual;
SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO inv_line_interface_id
FROM DUAL;
Select currency_code
Into p_currency_code
From gl_sets_of_books
Where set_of_books_id = P_SET_OF_BOOK_ID;
Insert into AP_INVOICES_INTERFACE
(
invoice_id ,
invoice_num,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
accts_pay_code_combination_id,
source,
org_id,
legal_entity_id , /*added by ssumaith - bug# 4448789 */
created_by,
creation_date,
last_updated_by,
last_update_date
)
SELECT
inv_interface_id , -- REPORT_HEADER_ID,
for_invoice_num_rec.inv_num||'/'||jibh.PLA_ID, -- INVOICE_NUM,
jibh.TR6_DATE, -- (Invoice Date ) WEEK_END_DATE,
jibh.VENDOR_ID, -- VENDOR_ID,
jibh.VENDOR_SITE_ID, -- VENDOR_SITE_ID,
jibh.PLA_AMOUNT, -- TOTAL,
p_currency_code, -- DEFAULT_CURRENCY_CODE,
-- Bug 5141305. Added by Lakshmi Gopalsami
-- Removed the reference to accts_pay_code_combination_id of po_vendors
pvs.ACCTS_PAY_CODE_COMBINATION_ID,
lv_source,
v_ORG_ID, -- ORG_ID
ln_legal_entity_id , -- LEGAL_ENTITY_ID
jibh.CREATED_BY, -- CREATED_BY,
jibh.CREATION_DATE, -- CREATION_DATE,
jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
jibh.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
FROM JAI_CMN_RG_PLA_HDRS jibh,
PO_VENDORS pv,
PO_VENDOR_SITES_ALL pvs
WHERE jibh.PLA_ID = P_PLA_ID
AND pvs.vendor_site_id (+)= jibh.vendor_site_id
AND pv.vendor_id = jibh.vendor_id
AND NVL(pvs.org_id, 0) = NVL(v_org_id, 0);
SELECT count(*)
into cnt_rec
FROM JAI_CMN_RG_PLA_HDRS jibh,
JAI_CMN_INVENTORY_ORGS org
WHERE jibh.PLA_ID = P_PLA_ID
AND org.organization_id = jibh.organization_id
AND org.location_id = jibh.location_id;
INSERT INTO ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
org_id,
amount_includes_tax_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
inv_interface_id, -- REPORT_HEADER_ID,
inv_line_interface_id,
counter_cur_rec.line_num,
lv_lookup_type_code, -- LINE_TYPE_LOOKUP_CODE, --rchandan for bug#4428980
jibh.PLA_AMOUNT, -- AMOUNT,
trunc(for_accounting_date_rec.tr6_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
lv_description, -- ITEM_DESCRIPTION, --rchandan for bug#4428980
org.MODVAT_PLA_ACCOUNT_ID, -- ACCTS_PAY_CODE_COMBINATION_ID,
v_ORG_ID, -- ORG_ID,
'N', -- AMOUNT_INCLUDES_TAX_FLAG,
jibh.CREATED_BY, -- CREATED_BY,
jibh.CREATION_DATE, -- CREATION_DATE,
jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
jibh.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE,
NULL -- LAST_UPDATE_LOGIN
FROM JAI_CMN_RG_PLA_HDRS jibh,
JAI_CMN_INVENTORY_ORGS org
WHERE jibh.PLA_ID = P_PLA_ID
AND org.organization_id = jibh.organization_id
AND org.location_id = 0 ;
INSERT INTO ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
org_id,
amount_includes_tax_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
inv_interface_id, -- REPORT_HEADER_ID,
inv_line_interface_id,
counter_cur_rec.line_num,
lv_lookup_type_code, -- LINE_TYPE_LOOKUP_CODE, --rchandan for bug#4428980
jibh.PLA_AMOUNT, -- AMOUNT,
trunc(for_accounting_date_rec.tr6_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
lv_description, -- ITEM_DESCRIPTION, --rchandan for bug#4428980
org.MODVAT_PLA_ACCOUNT_ID, -- ACCTS_PAY_CODE_COMBINATION_ID,
v_ORG_ID, -- ORG_ID,
'N', -- AMOUNT_INCLUDES_TAX_FLAG,
jibh.CREATED_BY, -- CREATED_BY,
jibh.CREATION_DATE, -- CREATION_DATE,
jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
jibh.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE,
NULL -- LAST_UPDATE_LOGIN
FROM JAI_CMN_RG_PLA_HDRS jibh,
JAI_CMN_INVENTORY_ORGS org
WHERE jibh.PLA_ID = P_PLA_ID
AND org.organization_id = jibh.organization_id
AND org.location_id = jibh.location_id;
SELECT NVL(MAX(line_number),0) + 1 line_num
FROM ap_invoice_lines_interface
WHERE invoice_id = inv_id;
SELECT 'BOE/Invoice/'||TO_CHAR(p_org_id)||'/'||TO_CHAR(P_BOE_ID) inv_num
FROM DUAL; --Added on 21-Feb-2002
Removed select and added cursor.
*/
CURSOR multi_org_installed is
SELECT decode(multi_org_flag, 'Y', 1,0) multi_org_cnt
FROM fnd_product_groups;
SELECT jct.tax_type,
SUM(nvl(jbr.total_amount, jbr.tax_amount)) boe_amount
FROM jai_boe_roundings jbr, jai_cmn_taxes_all jct
WHERE jbr.boe_id = pn_boe_id
AND jbr.tax_id = jct.tax_id
GROUP BY jct.tax_type;
SELECT import_date, organization_id, location_id,depb_amount
FROM jai_cmn_boe_hdrs
WHERE boe_id = pn_boe_id;
SELECT ap_invoices_interface_s.NEXTVAL
INTO inv_interface_id
FROM dual;
SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO inv_line_interface_id
FROM DUAL;
SELECT currency_code
INTO p_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_book_id;
INSERT INTO AP_INVOICES_INTERFACE
(
invoice_id,
invoice_num,
-- voucher_num, -- added by Aparajita on 08-may-2002 bug 2361769 Harshita for Bug 4870243
invoice_date,
invoice_type_lookup_code, -- Added by Bo Li for Bug #1168411 BOE enhancement
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
accts_pay_code_combination_id,
--set_of_books_id,
source,
gl_date, -- Vijay Shankar for bug#3049198
--accounting_date,
org_id,
legal_entity_id ,
created_by,
creation_date,
last_updated_by,
last_update_date
-- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement Begin
,
application_id,
product_table,
reference_key1,
reference_key2,
reference_key3,
reference_key4,
reference_key5
-- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
)
SELECT
inv_interface_id, -- invoice_interface_header_id,
for_invoice_num_rec.inv_num, -- invoice_num, --added on 21-feb-2002
-- for_invoice_num_rec.inv_num, -- added for voucher number, same as invoice number by aparajita Harshita for Bug 4870243
-- trunc(jibh.bol_date),
trunc(jibh.import_date), -- Vijay Shankar for bug#3049198
'STANDARD', -- Added by Bo Li for Bug #1168411 BOE enhancement
-- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
--------------------------------------------------------------------
-- jibh.vendor_id,
-- jibh.vendor_site_id,
jibh.customs_authority_id,
jibh.customs_authority_site_id,
--------------------------------------------------------------------
-- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
jibh.boe_amount - nvl(jibh.depb_amount,0), -- total,/*Bug 5607160 bduvarag*/
p_currency_code, -- default_currency_code,
-- Bug 5141305. Added by Lakshmi Gopalsami
-- Removed the reference to accts_pay_code_combination_id of po_vendors
pvs.ACCTS_PAY_CODE_COMBINATION_ID,
lv_source,
trunc(jibh.import_date), -- Vijay Shankar for bug#3049198
v_org_id , -- org_id,
ln_legal_entity_id , -- LEGAL_ENTITY_ID
jibh.created_by,
trunc(jibh.creation_date),
jibh.last_updated_by,
trunc(jibh.last_update_date)
-- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement Begin
,
7000,
'JAI_CMN_BOE_HDRS',
jibh.boe_id,
NULL,
NULL,
NULL,
NULL
-- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
FROM
JAI_CMN_BOE_HDRS jibh,
po_vendors pv,
po_vendor_sites_all pvs
where jibh.boe_id = p_boe_id
and pvs.vendor_site_id (+)= jibh.vendor_site_id
and pv.vendor_id = jibh.vendor_id
and nvl(pvs.org_id, 0) = nvl(v_org_id, 0);
UPDATE jai_cmn_boe_hdrs
SET invoice_num = for_invoice_num_rec.inv_num
WHERE boe_id = p_boe_id;
select count(*)
into cnt_rec
from JAI_CMN_BOE_HDRS jibh,
JAI_CMN_INVENTORY_ORGS org
where jibh.boe_id = p_boe_id
and org.organization_id = jibh.organization_id
and org.location_id = jibh.location_id;
insert into ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
org_id,
amount_includes_tax_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
inv_interface_id, -- report_header_id,
inv_line_interface_id,
counter_cur_rec.line_num,
lv_lookup_type_code, -- line_type_lookup_code, --rchandan for bug#4428980
round(jibh.boe_amount), -- amount,/*Bug 5607160 bduvarag*/
/*
jibh.import_date, -- bug#3049198
lv_description, -- item_description, --rchandan for bug#4428980
org.boe_account_id,
v_org_id, -- org_id,
'N' , -- amount_includes_tax_flag,
jibh.created_by,
trunc(jibh.creation_date),
jibh.last_updated_by,
jibh.last_update_date,
null -- last_update_login
from JAI_CMN_BOE_HDRS jibh,
JAI_CMN_INVENTORY_ORGS org
where jibh.boe_id = p_boe_id
and org.organization_id = jibh.organization_id
AND org.location_id = 0 ;
insert into ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
org_id,
amount_includes_tax_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
select
inv_interface_id, -- report_header_id,
inv_line_interface_id,
counter_cur_rec.line_num,
lv_lookup_type_code, -- line_type_lookup_code, --rchandan for bug#4428980
round(jibh.boe_amount),/*Bug 5607160 bduvarag*/
/*
jibh.import_date, -- bug#3049198
lv_description, -- item_description, --rchandan for bug#4428980
org.boe_account_id,
v_org_id, -- org_id,
'N', -- amount_includes_tax_flag,
jibh.created_by,
trunc(jibh.creation_date),
jibh.last_updated_by,
jibh.last_update_date,
null -- last_update_login
from
JAI_CMN_BOE_HDRS jibh,
JAI_CMN_INVENTORY_ORGS org
where
jibh.boe_id = p_boe_id
and org.organization_id = jibh.organization_id
and org.location_id = jibh.location_id;
SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO inv_line_interface_id
FROM dual;
INSERT INTO ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
org_id,
amount_includes_tax_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(inv_interface_id,
inv_line_interface_id,
counter_cur_rec.line_num,
lv_lookup_type_code,
get_tax_amount_rec.boe_amount,
trunc(ld_import_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
lv_description,
lv_tax_account_code,
v_org_id,
'N',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id);
SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO inv_line_interface_id
FROM DUAL;
insert into ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
org_id,
amount_includes_tax_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES(
inv_interface_id, -- report_header_id,
inv_line_interface_id,
counter_cur_rec.line_num,
lv_lookup_type_code,
ln_depb_amount,
trunc(ld_import_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
lv_description, -- item_description,
ln_depb_account_id,
v_org_id, -- org_id,
'N', -- amount_includes_tax_flag,
fnd_global.login_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.login_id
);
PROCEDURE insert_ap_inv_interface(
p_jai_source IN VARCHAR2,
p_invoice_id OUT NOCOPY ap_invoices_interface.INVOICE_ID%TYPE,
p_invoice_num IN ap_invoices_interface.INVOICE_NUM%TYPE DEFAULT NULL,
p_invoice_type_lookup_code IN ap_invoices_interface.INVOICE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
p_invoice_date IN ap_invoices_interface.INVOICE_DATE%TYPE DEFAULT NULL,
p_po_number IN ap_invoices_interface.PO_NUMBER%TYPE DEFAULT NULL,
p_vendor_id IN ap_invoices_interface.VENDOR_ID%TYPE DEFAULT NULL,
p_vendor_num IN ap_invoices_interface.VENDOR_NUM%TYPE DEFAULT NULL,
p_vendor_name IN ap_invoices_interface.VENDOR_NAME%TYPE DEFAULT NULL,
p_vendor_site_id IN ap_invoices_interface.VENDOR_SITE_ID%TYPE DEFAULT NULL,
p_vendor_site_code IN ap_invoices_interface.VENDOR_SITE_CODE%TYPE DEFAULT NULL,
p_invoice_amount IN ap_invoices_interface.INVOICE_AMOUNT%TYPE DEFAULT NULL,
p_invoice_currency_code IN ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE DEFAULT NULL,
p_exchange_rate IN ap_invoices_interface.EXCHANGE_RATE%TYPE DEFAULT NULL,
p_exchange_rate_type IN ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL,
p_exchange_date IN ap_invoices_interface.EXCHANGE_DATE%TYPE DEFAULT NULL,
p_terms_id IN ap_invoices_interface.TERMS_ID%TYPE DEFAULT NULL,
p_terms_name IN ap_invoices_interface.TERMS_NAME%TYPE DEFAULT NULL,
p_description IN ap_invoices_interface.DESCRIPTION%TYPE DEFAULT NULL,
p_awt_group_id IN ap_invoices_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
p_awt_group_name IN ap_invoices_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
p_last_update_date IN ap_invoices_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
p_last_updated_by IN ap_invoices_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
p_last_update_login IN ap_invoices_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
p_creation_date IN ap_invoices_interface.CREATION_DATE%TYPE DEFAULT NULL,
p_created_by IN ap_invoices_interface.CREATED_BY%TYPE DEFAULT NULL,
--Added below the attribute category and attribute parameters for Bug #3841637
p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
p_status IN ap_invoices_interface.STATUS%TYPE DEFAULT NULL,
p_source IN ap_invoices_interface.SOURCE%TYPE DEFAULT NULL,
p_group_id IN ap_invoices_interface.GROUP_ID%TYPE DEFAULT NULL,
p_request_id IN ap_invoices_interface.REQUEST_ID%TYPE DEFAULT NULL,
p_payment_cross_rate_type IN ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE DEFAULT NULL,
p_payment_cross_rate_date IN ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE DEFAULT NULL,
p_payment_cross_rate IN ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE DEFAULT NULL,
p_payment_currency_code IN ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE DEFAULT NULL,
p_workflow_flag IN ap_invoices_interface.WORKFLOW_FLAG%TYPE DEFAULT NULL,
p_doc_category_code IN ap_invoices_interface.DOC_CATEGORY_CODE%TYPE DEFAULT NULL,
p_voucher_num IN ap_invoices_interface.VOUCHER_NUM%TYPE DEFAULT NULL,
p_payment_method_lookup_code IN ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE DEFAULT NULL,
p_pay_group_lookup_code IN ap_invoices_interface.PAY_GROUP_LOOKUP_CODE%TYPE DEFAULT NULL,
p_goods_received_date IN ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE DEFAULT NULL,
p_invoice_received_date IN ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE DEFAULT NULL,
p_gl_date IN ap_invoices_interface.GL_DATE%TYPE DEFAULT NULL,
p_accts_pay_ccid IN ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
p_ussgl_transaction_code IN ap_invoices_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
p_exclusive_payment_flag IN ap_invoices_interface.EXCLUSIVE_PAYMENT_FLAG%TYPE DEFAULT NULL,
p_org_id IN ap_invoices_interface.ORG_ID%TYPE DEFAULT NULL,
p_amount_applicable_to_dis IN ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE DEFAULT NULL,
p_prepay_num IN ap_invoices_interface.PREPAY_NUM%TYPE DEFAULT NULL,
p_prepay_dist_num IN ap_invoices_interface.PREPAY_DIST_NUM%TYPE DEFAULT NULL,
p_prepay_apply_amount IN ap_invoices_interface.PREPAY_APPLY_AMOUNT%TYPE DEFAULT NULL,
p_prepay_gl_date IN ap_invoices_interface.PREPAY_GL_DATE%TYPE DEFAULT NULL,
-- Bug4240179. Added by LGOPALSA. Changed the data type
-- for the following 4 fields.
p_invoice_includes_prepay_flag IN VARCHAR2 DEFAULT NULL,
p_no_xrate_base_amount IN NUMBER DEFAULT NULL,
p_vendor_email_address IN VARCHAR2 DEFAULT NULL,
p_terms_date IN DATE DEFAULT NULL,
p_requester_id IN NUMBER DEFAULT NULL,
p_ship_to_location IN VARCHAR2 DEFAULT NULL,
p_external_doc_ref IN VARCHAR2 DEFAULT NULL,
-- Bug 7109056. Added by Lakshmi Gopalsami
p_payment_method_code IN VARCHAR2 DEFAULT NULL,
--Added by Qiong for AP Open Interface of ZX intergration --ported by Chong from 12.1.3 20130325
p_Calc_Tax_During_Import_Flag IN VARCHAR2 DEFAULT NULL
) IS
lv_object_name VARCHAR2(61); -- := 'jai_ap_utils_pkg.insert_ap_inv_interface'; /* Added by Ramananda for bug#4407165 */
select legal_entity_id from ap_invoices_all
WHERE invoice_id = p_attribute1;
lv_object_name := 'jai_ap_utils_pkg.insert_ap_inv_interface'; /* Added by Ramananda for bug#4407165 */
INSERT INTO ap_invoices_interface(
INVOICE_ID,
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,
PO_NUMBER,
VENDOR_ID,
VENDOR_NUM,
VENDOR_NAME,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
TERMS_ID,
TERMS_NAME,
DESCRIPTION,
AWT_GROUP_ID,
AWT_GROUP_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
--Added below the attribute category and attribute columns for Bug #3841637
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
STATUS,
SOURCE,
GROUP_ID,
REQUEST_ID,
PAYMENT_CROSS_RATE_TYPE,
PAYMENT_CROSS_RATE_DATE,
PAYMENT_CROSS_RATE,
PAYMENT_CURRENCY_CODE,
WORKFLOW_FLAG,
DOC_CATEGORY_CODE,
-- VOUCHER_NUM, Harshita for Bug 4870243
PAYMENT_METHOD_CODE, -- Bug 7109056. added by Lakshmi gopalsami
PAY_GROUP_LOOKUP_CODE,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
GL_DATE,
ACCTS_PAY_CODE_COMBINATION_ID,
USSGL_TRANSACTION_CODE,
EXCLUSIVE_PAYMENT_FLAG,
ORG_ID,
LEGAL_ENTITY_ID , /* added by ssumaith - bug# 4448789*/
AMOUNT_APPLICABLE_TO_DISCOUNT,
PREPAY_NUM,
PREPAY_DIST_NUM,
PREPAY_APPLY_AMOUNT,
PREPAY_GL_DATE,
Calc_Tax_During_Import_Flag --Added by Qiong for AP open Interface --ported by Chong from 12.1.3 20130325
/* , Bug4240179. Added by LGOPALSA
Commented the following 4 fields*/
--INVOICE_INCLUDES_PREPAY_FLAG,
--NO_XRATE_BASE_AMOUNT,
--VENDOR_EMAIL_ADDRESS,
--TERMS_DATE
/*,
REQUESTER_ID,
SHIP_TO_LOCATION,
EXTERNAL_DOC_REF*/)--commented by Sanjikum for Bug#4183001
VALUES(
ap_invoices_interface_s.NEXTVAL,
p_invoice_num,
p_invoice_type_lookup_code,
p_invoice_date,
p_po_number,
p_vendor_id,
p_vendor_num,
p_vendor_name,
p_vendor_site_id,
p_vendor_site_code,
p_invoice_amount,
p_invoice_currency_code,
p_exchange_rate,
p_exchange_rate_type,
p_exchange_date,
p_terms_id,
p_terms_name,
p_description,
p_awt_group_id,
p_awt_group_name,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
--Added below the attribute category and attribute columns for Bug #3841637
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_status,
p_source,
p_group_id,
p_request_id,
p_payment_cross_rate_type,
p_payment_cross_rate_date,
p_payment_cross_rate,
p_payment_currency_code,
p_workflow_flag,
p_doc_category_code,
-- p_voucher_num, Harshita for Bug 4870243
p_payment_method_code, -- Bug 7109056. Added by Lakshmi Gopalsami
p_pay_group_lookup_code,
p_goods_received_date,
p_invoice_received_date,
p_gl_date,
p_accts_pay_ccid,
p_ussgl_transaction_code,
p_exclusive_payment_flag,
p_org_id,
ln_legal_entity_id , /* added by ssumaith - bug# 4448789*/
p_amount_applicable_to_dis,
p_prepay_num,
p_prepay_dist_num,
p_prepay_apply_amount,
p_prepay_gl_date
,p_Calc_Tax_During_Import_Flag --ported by Chong from 12.1.3 20130325
/* , Bug4240179. Added by LGOPALSA
Commented the following 4 fields*/
--p_invoice_includes_prepay_flag,
--p_no_xrate_base_amount,
--p_vendor_email_address,
--p_terms_date
/*,
p_requester_id,
p_ship_to_location,
p_external_doc_ref*/) --commented by Sanjikum for Bug#4183001
RETURNING invoice_id INTO p_invoice_id;
END insert_ap_inv_interface;
PROCEDURE insert_ap_inv_lines_interface(
p_jai_source IN VARCHAR2,
p_invoice_id IN ap_invoice_lines_interface.INVOICE_ID%TYPE,
p_invoice_line_id OUT NOCOPY ap_invoice_lines_interface.INVOICE_LINE_ID%TYPE,
p_line_number IN ap_invoice_lines_interface.LINE_NUMBER%TYPE DEFAULT NULL,
p_line_type_lookup_code IN ap_invoice_lines_interface.LINE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
p_line_group_number IN ap_invoice_lines_interface.LINE_GROUP_NUMBER%TYPE DEFAULT NULL,
p_amount IN ap_invoice_lines_interface.AMOUNT%TYPE DEFAULT NULL,
p_accounting_date IN ap_invoice_lines_interface.ACCOUNTING_DATE%TYPE DEFAULT NULL,
p_description IN ap_invoice_lines_interface.DESCRIPTION%TYPE DEFAULT NULL,
p_amount_includes_tax_flag IN ap_invoice_lines_interface.AMOUNT_INCLUDES_TAX_FLAG%TYPE DEFAULT NULL,
p_prorate_across_flag IN ap_invoice_lines_interface.PRORATE_ACROSS_FLAG%TYPE DEFAULT NULL,
p_tax_code IN ap_invoice_lines_interface.TAX_CODE%TYPE DEFAULT NULL,
p_final_match_flag IN ap_invoice_lines_interface.FINAL_MATCH_FLAG%TYPE DEFAULT NULL,
p_po_header_id IN ap_invoice_lines_interface.PO_HEADER_ID%TYPE DEFAULT NULL,
p_po_number IN ap_invoice_lines_interface.PO_NUMBER%TYPE DEFAULT NULL,
p_po_line_id IN ap_invoice_lines_interface.PO_LINE_ID%TYPE DEFAULT NULL,
p_po_line_number IN ap_invoice_lines_interface.PO_LINE_NUMBER%TYPE DEFAULT NULL,
p_po_line_location_id IN ap_invoice_lines_interface.PO_LINE_LOCATION_ID%TYPE DEFAULT NULL,
p_po_shipment_num IN ap_invoice_lines_interface.PO_SHIPMENT_NUM%TYPE DEFAULT NULL,
p_po_distribution_id IN ap_invoice_lines_interface.PO_DISTRIBUTION_ID%TYPE DEFAULT NULL,
p_po_distribution_num IN ap_invoice_lines_interface.PO_DISTRIBUTION_NUM%TYPE DEFAULT NULL,
p_po_unit_of_measure IN ap_invoice_lines_interface.PO_UNIT_OF_MEASURE%TYPE DEFAULT NULL,
p_inventory_item_id IN ap_invoice_lines_interface.INVENTORY_ITEM_ID%TYPE DEFAULT NULL,
p_item_description IN ap_invoice_lines_interface.ITEM_DESCRIPTION%TYPE DEFAULT NULL,
p_quantity_invoiced IN ap_invoice_lines_interface.QUANTITY_INVOICED%TYPE DEFAULT NULL,
p_ship_to_location_code IN ap_invoice_lines_interface.SHIP_TO_LOCATION_CODE%TYPE DEFAULT NULL,
p_unit_price IN ap_invoice_lines_interface.UNIT_PRICE%TYPE DEFAULT NULL,
p_distribution_set_id IN ap_invoice_lines_interface.DISTRIBUTION_SET_ID%TYPE DEFAULT NULL,
p_distribution_set_name IN ap_invoice_lines_interface.DISTRIBUTION_SET_NAME%TYPE DEFAULT NULL,
p_dist_code_concatenated IN ap_invoice_lines_interface.DIST_CODE_CONCATENATED%TYPE DEFAULT NULL,
p_dist_code_combination_id IN ap_invoice_lines_interface.DIST_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
p_awt_group_id IN ap_invoice_lines_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
p_awt_group_name IN ap_invoice_lines_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
p_last_updated_by IN ap_invoice_lines_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
p_last_update_date IN ap_invoice_lines_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
p_last_update_login IN ap_invoice_lines_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
p_created_by IN ap_invoice_lines_interface.CREATED_BY%TYPE DEFAULT NULL,
p_creation_date IN ap_invoice_lines_interface.CREATION_DATE%TYPE DEFAULT NULL,
--Added below the attribute category and attribute parameters for Bug #3841637
p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
p_po_release_id IN ap_invoice_lines_interface.PO_RELEASE_ID%TYPE DEFAULT NULL,
p_release_num IN ap_invoice_lines_interface.RELEASE_NUM%TYPE DEFAULT NULL,
p_account_segment IN ap_invoice_lines_interface.ACCOUNT_SEGMENT%TYPE DEFAULT NULL,
p_balancing_segment IN ap_invoice_lines_interface.BALANCING_SEGMENT%TYPE DEFAULT NULL,
p_cost_center_segment IN ap_invoice_lines_interface.COST_CENTER_SEGMENT%TYPE DEFAULT NULL,
p_project_id IN ap_invoice_lines_interface.PROJECT_ID%TYPE DEFAULT NULL,
p_task_id IN ap_invoice_lines_interface.TASK_ID%TYPE DEFAULT NULL,
p_expenditure_type IN ap_invoice_lines_interface.EXPENDITURE_TYPE%TYPE DEFAULT NULL,
p_expenditure_item_date IN ap_invoice_lines_interface.EXPENDITURE_ITEM_DATE%TYPE DEFAULT NULL,
p_expenditure_organization_id IN ap_invoice_lines_interface.EXPENDITURE_ORGANIZATION_ID%TYPE DEFAULT NULL,
p_project_accounting_context IN ap_invoice_lines_interface.PROJECT_ACCOUNTING_CONTEXT%TYPE DEFAULT NULL,
p_pa_addition_flag IN ap_invoice_lines_interface.PA_ADDITION_FLAG%TYPE DEFAULT NULL,
p_pa_quantity IN ap_invoice_lines_interface.PA_QUANTITY%TYPE DEFAULT NULL,
p_ussgl_transaction_code IN ap_invoice_lines_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
p_stat_amount IN ap_invoice_lines_interface.STAT_AMOUNT%TYPE DEFAULT NULL,
p_type_1099 IN ap_invoice_lines_interface.TYPE_1099%TYPE DEFAULT NULL,
p_income_tax_region IN ap_invoice_lines_interface.INCOME_TAX_REGION%TYPE DEFAULT NULL,
p_assets_tracking_flag IN ap_invoice_lines_interface.ASSETS_TRACKING_FLAG%TYPE DEFAULT NULL,
p_price_correction_flag IN ap_invoice_lines_interface.PRICE_CORRECTION_FLAG%TYPE DEFAULT NULL,
p_org_id IN ap_invoice_lines_interface.ORG_ID%TYPE DEFAULT NULL,
p_receipt_number IN ap_invoice_lines_interface.RECEIPT_NUMBER%TYPE DEFAULT NULL,
p_receipt_line_number IN ap_invoice_lines_interface.RECEIPT_LINE_NUMBER%TYPE DEFAULT NULL,
p_match_option IN ap_invoice_lines_interface.MATCH_OPTION%TYPE DEFAULT NULL,
p_packing_slip IN ap_invoice_lines_interface.PACKING_SLIP%TYPE DEFAULT NULL,
p_rcv_transaction_id IN ap_invoice_lines_interface.RCV_TRANSACTION_ID%TYPE DEFAULT NULL,
p_pa_cc_ar_invoice_id IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_ID%TYPE DEFAULT NULL,
p_pa_cc_ar_invoice_line_num IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_LINE_NUM%TYPE DEFAULT NULL,
p_reference_1 IN ap_invoice_lines_interface.REFERENCE_1%TYPE DEFAULT NULL,
p_reference_2 IN ap_invoice_lines_interface.REFERENCE_2%TYPE DEFAULT NULL,
p_pa_cc_processed_code IN ap_invoice_lines_interface.PA_CC_PROCESSED_CODE%TYPE DEFAULT NULL,
p_tax_recovery_rate IN ap_invoice_lines_interface.TAX_RECOVERY_RATE%TYPE DEFAULT NULL,
p_tax_recovery_override_flag IN ap_invoice_lines_interface.TAX_RECOVERY_OVERRIDE_FLAG%TYPE DEFAULT NULL,
p_tax_recoverable_flag IN ap_invoice_lines_interface.TAX_RECOVERABLE_FLAG%TYPE DEFAULT NULL,
p_tax_code_override_flag IN ap_invoice_lines_interface.TAX_CODE_OVERRIDE_FLAG%TYPE DEFAULT NULL,
p_tax_code_id IN ap_invoice_lines_interface.TAX_CODE_ID%TYPE DEFAULT NULL,
p_credit_card_trx_id IN ap_invoice_lines_interface.CREDIT_CARD_TRX_ID%TYPE DEFAULT NULL,
-- Bug 4240179. Changed data for vendor_item_num and award_id
-- Added by LGOPALSA
p_award_id IN NUMBER DEFAULT NULL,
p_vendor_item_num IN VARCHAR2 DEFAULT NULL,
p_taxable_flag IN VARCHAR2 DEFAULT NULL,
p_price_correct_inv_num IN VARCHAR2 DEFAULT NULL,
p_external_doc_line_ref IN VARCHAR2 DEFAULT NULL)
IS
lv_object_name VARCHAR2(61);
lv_object_name := 'jai_ap_utils_pkg.insert_ap_inv_lines_interface'; /* Added by Ramananda for bug#4407165 */
INSERT INTO ap_invoice_lines_interface(
INVOICE_ID,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
LINE_GROUP_NUMBER,
AMOUNT,
ACCOUNTING_DATE,
DESCRIPTION,
AMOUNT_INCLUDES_TAX_FLAG,
PRORATE_ACROSS_FLAG,
TAX_CODE,
FINAL_MATCH_FLAG,
PO_HEADER_ID,
PO_NUMBER,
PO_LINE_ID,
PO_LINE_NUMBER,
PO_LINE_LOCATION_ID,
PO_SHIPMENT_NUM,
PO_DISTRIBUTION_ID,
PO_DISTRIBUTION_NUM,
PO_UNIT_OF_MEASURE,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
QUANTITY_INVOICED,
SHIP_TO_LOCATION_CODE,
UNIT_PRICE,
DISTRIBUTION_SET_ID,
DISTRIBUTION_SET_NAME,
DIST_CODE_CONCATENATED,
DIST_CODE_COMBINATION_ID,
AWT_GROUP_ID,
AWT_GROUP_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
--Added below the attribute category and attribute columns for Bug #3841637
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PO_RELEASE_ID,
RELEASE_NUM,
ACCOUNT_SEGMENT,
BALANCING_SEGMENT,
COST_CENTER_SEGMENT,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ACCOUNTING_CONTEXT,
PA_ADDITION_FLAG,
PA_QUANTITY,
USSGL_TRANSACTION_CODE,
STAT_AMOUNT,
TYPE_1099,
INCOME_TAX_REGION,
ASSETS_TRACKING_FLAG,
PRICE_CORRECTION_FLAG,
ORG_ID,
RECEIPT_NUMBER,
RECEIPT_LINE_NUMBER,
MATCH_OPTION,
PACKING_SLIP,
RCV_TRANSACTION_ID,
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
REFERENCE_1,
REFERENCE_2,
PA_CC_PROCESSED_CODE,
TAX_RECOVERY_RATE,
TAX_RECOVERY_OVERRIDE_FLAG,
TAX_RECOVERABLE_FLAG,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
CREDIT_CARD_TRX_ID
--, Bug 4240179. Commented by LGOPALSA
-- AWARD_ID,
-- VENDOR_ITEM_NUM
/*,
TAXABLE_FLAG,
PRICE_CORRECT_INV_NUM,
EXTERNAL_DOC_LINE_REF*/)--commented by Sanjikum for Bug#4183001
VALUES(
p_invoice_id,
ap_invoice_lines_interface_s.NEXTVAL,
p_line_number,
p_line_type_lookup_code,
p_line_group_number,
p_amount,
trunc(p_accounting_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
p_description,
p_amount_includes_tax_flag,
p_prorate_across_flag,
p_tax_code,
p_final_match_flag,
p_po_header_id,
p_po_number,
p_po_line_id,
p_po_line_number,
p_po_line_location_id,
p_po_shipment_num,
p_po_distribution_id,
p_po_distribution_num,
p_po_unit_of_measure,
p_inventory_item_id,
p_item_description,
p_quantity_invoiced,
p_ship_to_location_code,
p_unit_price,
p_distribution_set_id,
p_distribution_set_name,
p_dist_code_concatenated,
p_dist_code_combination_id,
p_awt_group_id,
p_awt_group_name,
p_last_updated_by,
p_last_update_date,
p_last_update_login,
p_created_by,
p_creation_date,
--Added below the attribute category and attribute columns for Bug #3841637
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_po_release_id,
p_release_num,
p_account_segment,
p_balancing_segment,
p_cost_center_segment,
p_project_id,
p_task_id,
p_expenditure_type,
p_expenditure_item_date,
p_expenditure_organization_id,
p_project_accounting_context,
p_pa_addition_flag,
p_pa_quantity,
p_ussgl_transaction_code,
p_stat_amount,
p_type_1099,
p_income_tax_region,
p_assets_tracking_flag,
p_price_correction_flag,
p_org_id,
p_receipt_number,
p_receipt_line_number,
p_match_option,
p_packing_slip,
p_rcv_transaction_id,
p_pa_cc_ar_invoice_id,
p_pa_cc_ar_invoice_line_num,
p_reference_1,
p_reference_2,
p_pa_cc_processed_code,
p_tax_recovery_rate,
p_tax_recovery_override_flag,
p_tax_recoverable_flag,
p_tax_code_override_flag,
p_tax_code_id,
p_credit_card_trx_id
--, Bug 4240179. Commented by LGOPALSA
--p_award_id,
--p_vendor_item_num
/*,
p_taxable_flag,
p_price_correct_inv_num,
p_external_doc_line_ref*/)--commented by Sanjikum for Bug#4183001
RETURNING invoice_line_id INTO p_invoice_line_id;
END insert_ap_inv_lines_interface;
select quantity_invoiced, unit_price, po_distribution_id, rcv_transaction_id,invoice_id
from ap_invoice_lines_all
where invoice_id = pn_invoice_id
AND line_number = pn_invoice_line_number;
select unit_of_measure receipt_uom,
source_doc_unit_of_measure po_uom,
po_unit_price /*Bug 4990941 bduvarag*/
from rcv_transactions
where transaction_id = p_rcv_transaction_id;
select price_override, quantity
from po_line_locations_all
where (po_header_id, po_line_id, line_location_id ) in
(
select po_header_id, po_line_id, line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id
); */
select price_override, quantity
from po_line_locations_all
where (po_header_id, po_line_id, line_location_id ) in
(
select po_header_id, po_line_id, po_line_location_id
from ap_invoice_lines_all
where invoice_id = pn_invoice_id
AND line_number = pn_invoice_line_number
);
select qty_received
from JAI_RCV_LINES
where (shipment_header_id, shipment_line_id)
in
(
select shipment_header_id, shipment_line_id
from rcv_transactions
where transaction_id = p_rcv_transaction_id
);
select uom_code
from mtl_units_of_measure
where unit_of_measure = p_unit_of_measure;
select item_id
from rcv_shipment_lines
where shipment_line_id = (select shipment_line_id
from rcv_transactions
where transaction_id = p_transaction_id);
SELECT source
FROM ap_invoices_all
WHERE invoice_id = inv_id;
Since the procedure was revamped with the new approach, deleted the old code.
--------------------------------------------------------------------------------------------------------------------*/
Begin
v_prv_req_id := p_prv_req_id;
Select invoice_id
into v_invoice_id
from ap_invoices_all
Where invoice_num = p_boe_id
And vendor_id = p_vendor_id;
Select excise_calc_base -- , assessable_value (Commented as Assessable Value is picked by other conditions now )
From JAI_OPM_ITM_MASTERS
Where item_id = p_item_id;
Select Pricelist_Id
From JAI_OPM_CUSTOMERS
Where Cust_id = p_cust_id ;
Select a.Base_Price
From Op_Prce_Itm a ,op_prce_eff b
Where a.pricelist_id = b.pricelist_id
And a.Pricelist_Id = p_Pricelist_id
And a.Item_Id = p_item_id
And sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate) ;
Select assessable_value
From JAI_OPM_ITM_MASTERS
Where item_id = p_item_id;
Procedure pan_update ( P_errbuf OUT NOCOPY varchar2,
P_return_code OUT NOCOPY varchar2,
P_vendor_id IN PO_VENDORS.vendor_id%TYPE,
P_old_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
P_new_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
P_debug_flag IN varchar2) is
/* Cursor to lock the jai_ap_tds_thhold_grps */
Cursor C_lock_thhold_grps is
select threshold_grp_id,
vendor_id,
org_tan_num,
vendor_pan_num,
section_type,
section_code,
fin_year,
total_invoice_amount,
total_invoice_cancel_amount,
total_invoice_apply_amount,
total_invoice_unapply_amount,
total_tax_paid,
total_thhold_change_tax_paid,
current_threshold_slab_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
from jai_ap_tds_thhold_grps
where vendor_id = P_vendor_id
and vendor_pan_num = p_old_pan_num
order by vendor_id,threshold_grp_id
for UPDATE of threshold_grp_id NOWAIT;
/* Update the tables in the following order
(1) JAI_AP_TDS_VENDOR_HDRS
(2) jai_ap_tds_thhold_grps
(3) jai_ap_tds_thhold_xceps
*/
lv_vendor_site_id_updated varchar2(1000) ;
lv_thhold_grp_id_updated varchar2(1000) ;
lv_thhold_xcep_id_updated varchar2(1000) ;
lv_vendor_site_id_updated := '';
lv_thhold_grp_id_updated := '';
lv_thhold_xcep_id_updated := '';
lv_debug_msg := ' 1. Update jai_ap_tds_thhold_grps';
lv_debug_msg := ' 2. Going to update jai_ap_tds_thhold_grps';
update jai_ap_tds_thhold_grps
set vendor_pan_num = P_new_pan_num
where vendor_id = P_vendor_id
and vendor_pan_num = P_old_pan_num
and threshold_grp_id = thhold_grps.threshold_grp_id;
lv_debug_msg := ' 3. Done with update of '|| thhold_grps.threshold_grp_id;
lv_thhold_grp_id_updated := lv_thhold_grp_id_updated || '-' || thhold_grps.threshold_grp_id;
lv_debug_msg := ' 4. Value of lv_thhold_grp_id_updated '|| lv_thhold_grp_id_updated;
lv_debug_msg := ' 5. Update JAI_AP_TDS_VENDOR_HDRS';
for vndr_tds_hdr in (select vthdr.*
from JAI_AP_TDS_VENDOR_HDRS vthdr
where vthdr.vendor_id = P_vendor_id
and vthdr.pan_no = P_old_pan_num)
loop
lv_debug_msg := ' 6. Going to update JAI_AP_TDS_VENDOR_HDRS';
update JAI_AP_TDS_VENDOR_HDRS
set pan_no = P_new_pan_num
where vendor_id = vndr_tds_hdr.vendor_id
and vendor_site_id = vndr_tds_hdr.vendor_site_id
and pan_no = P_old_pan_num;
lv_debug_msg := ' 7. Done with update of vendor '|| vndr_tds_hdr.vendor_id;
lv_vendor_site_id_updated := lv_vendor_site_id_updated || ' - '||vndr_tds_hdr.vendor_site_id;
lv_debug_msg := ' 8. Value of lv_vendor_site_id_updated '|| lv_vendor_site_id_updated;
lv_debug_msg := ' 9. Update jai_ap_tds_thhold_xceps';
for thhold_xceps in (select tdsxps.*
from jai_ap_tds_thhold_xceps tdsxps
where tdsxps.vendor_id = P_vendor_id
and vendor_pan = P_old_pan_num)
loop
lv_debug_msg := ' 10. Going to update jai_ap_tds_thhold_xceps';
Update jai_ap_tds_thhold_xceps
set vendor_pan = P_new_pan_num
where vendor_id = P_vendor_id
and vendor_pan = P_old_pan_num;
lv_debug_msg := ' 11. Done with update of vendor'||P_vendor_id ;
lv_thhold_xcep_id_updated := lv_thhold_xcep_id_updated || '-' || thhold_xceps.threshold_exception_id;
lv_debug_msg := ' 12. Value of lv_thhold_xcep_id_updated '|| lv_thhold_xcep_id_updated;
lv_debug_msg := ' 13. Inside insert - ';
Insert into jai_ap_tds_pan_changes
( pan_change_id,
vendor_id,
old_pan_num,
new_pan_num,
request_id,
request_date,
vendor_site_id_updated,
thhold_grp_id_updated,
thhold_xcep_id_updated,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
values
( jai_ap_tds_pan_changes_s.nextval,
P_vendor_id,
P_old_pan_num,
P_new_pan_num,
ln_request_id,
sysdate,
lv_vendor_site_id_updated,
lv_thhold_grp_id_updated,
lv_thhold_xcep_id_updated,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
Print_log(lv_debug_flag,'(Pan update :Exception) Vendor to be updated by this process are locked');
End pan_update;
SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.tds_regime;
SELECT attribute_Value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = P_regime_id
AND attribute_type_code = jai_constants.regn_type_others
AND attribute_code = jai_constants.regn_type_tds_batch
and organization_id = nvl(p_org_id, organization_id); --added for Bug#9149941
SELECT b.batch_name
FROM ap_invoices_all a,
ap_batches_all b
WHERE a.batch_id = b.batch_id
AND a.invoice_id = cp_invoice_id;
select tax_name,
tax_account_id,
mod_cr_percentage,
adhoc_flag,
nvl(tax_rate, 0) tax_rate,
tax_type
from JAI_CMN_TAXES_ALL
where tax_id = ln_tax_id;
select *
from ap_invoice_distributions_all
where invoice_id = ln_invoice_id
and distribution_line_number = ln_dist_line_number;
select precision
from fnd_currencies
where currency_code = lv_currency_code;
Select invoice_currency_code
into lv_inv_curr_code
from ap_invoices_all
where invoice_id = p_invoice_id;
for Misc_loop in ( select *
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and parent_invoice_distribution_id = p_invoice_distribution_id
)
loop
fnd_file.put_line(FND_FILE.LOG,' inside loop -- 2 ' );
for tax_loop in check_rec_tax (select tax_id
from ja_in_ap_tax_distributions
where invoice_id = misc_loop.invoice_id
and distribution_line_number = misc_loop.distribution_line_number)
loop
Service and Education cess are recoverable taxes and
IPV should not be calculated on these lines
If not (tax_loop.tax_type like '%EDUCATION_CESS') Then
*/
Open get_misc_lines(misc_loop.distribution_line_number, misc_loop.invoice_id);
update ap_invoice_distributions_all
set invoice_price_variance = round(ln_tax_ipv,ln_inv_pre),
base_invoice_price_variance = round(ln_tax_bipv, ln_base_pre),
price_var_code_combination_id = P_price_var_ccid
where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
update ap_invoice_distributions_all
set exchange_rate_variance = round(ln_tax_erv,ln_inv_pre),
rate_var_code_combination_id = P_rate_var_ccid
where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
/* update ipv and bipv to 0. no need to update Var CCID */
update ap_invoice_distributions_all
set invoice_price_variance = 0,
base_invoice_price_variance = 0,
exchange_rate_variance = 0
where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
select NVL(tax_amount,-1) -- 5763527, Added by kunkumar for Bug#5593895
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
AND parent_invoice_distribution_id = p_parent_dist_id /*bug 9346307*/
and line_no = p_precedence_value ;
select amount
from ap_invoice_distributions_all
where invoice_distribution_id = p_parent_dist_id
and invoice_id = p_invoice_id ;
select Precedence_1 P_1,
Precedence_2 P_2,
Precedence_3 P_3,
Precedence_4 P_4,
Precedence_5 P_5,
Precedence_6 P_6,
Precedence_7 P_7,
Precedence_8 P_8,
Precedence_9 P_9,
Precedence_10 P_10
from JAI_PO_TAXES
where line_location_id = p_line_location_id
and tax_id = p_tax_id ;
select Precedence_1 P_1,
Precedence_2 P_2,
Precedence_3 P_3,
Precedence_4 P_4,
Precedence_5 P_5,
Precedence_6 P_6,
Precedence_7 P_7,
Precedence_8 P_8,
Precedence_9 P_9,
Precedence_10 P_10
from JAI_RCV_LINE_TAXES
where shipment_line_id IN
( select shipment_line_id
from JAI_RCV_LINE_TAXES
where transaction_id = p_transaction_id
)
and tax_id = p_tax_id ;
SELECT
tax_account_id
FROM
jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT
TO_NUMBER(acc_rgm.attribute_value)
INTO
ln_tax_rgm_acc_id
FROM
jai_rgm_definitions rgm_def
, jai_rgm_registrations tax_rgm
, jai_rgm_registrations acc_rgm
WHERE regime_code IN (jai_constants.service_regime,jai_constants.vat_regime)
AND tax_rgm.regime_id = rgm_def.regime_id
AND tax_rgm.registration_type = jai_constants.regn_type_tax_types
AND tax_rgm.attribute_code = pv_tax_type
AND tax_rgm.regime_id = acc_rgm.regime_id
AND acc_rgm.registration_type = jai_constants.regn_type_accounts
AND acc_rgm.attribute_code = jai_constants.recovery_interim
AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
PROCEDURE insert_gl_interface
( pn_set_of_books_id IN NUMBER
, pd_accounting_date IN DATE
, pv_currency_code IN VARCHAR2
, pn_enter_cr IN NUMBER DEFAULT NULL
, pn_enter_dr IN NUMBER DEFAULT NULL
, pd_transaction_date IN DATE
, pn_code_combination_id IN NUMBER
, pd_currency_conversion_date IN DATE
, pv_currency_conversion_type IN VARCHAR2
, pv_currency_conversion_rate IN VARCHAR2
, pv_reference1 IN VARCHAR2
, pv_reference10 IN VARCHAR2
, pv_reference23 IN VARCHAR2
, pv_reference26 IN VARCHAR2
, pv_reference27 IN VARCHAR2
)
IS
BEGIN
INSERT INTO gl_interface
( status
, set_of_books_id
, user_je_source_name
, user_je_category_name
, accounting_date
, currency_code
, date_created
, created_by
, actual_flag
, entered_cr
, entered_dr
, transaction_date
, code_combination_id
, currency_conversion_date
, user_currency_conversion_type
, currency_conversion_rate
, reference1
, reference10
, reference22
, reference23
, reference24
, reference25
, reference26
, reference27
)
VALUES
( 'NEW' -- 'NEW'
, pn_set_of_books_id
, 'Payables India' -- je source name 'Payables India'
, 'Register India' -- je category name 'Register India'
, pd_accounting_date
, pv_currency_code
, sysdate -- standard who column
, TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
, 'A' -- 'A'
, pn_enter_cr
, pn_enter_cr
, pd_transaction_date
, pn_code_combination_id
, pd_currency_conversion_date
, pv_currency_conversion_type
, pv_currency_conversion_rate
, pv_reference1
, 'India Localization Entry for accounting inclusive taxes for invoice'||pv_reference10
, 'India Localization Entry' -- 'India Localization Entry'
, pv_reference23
, 'AP_INVOICES_ALL' -- 'AP_INVOICES_ALL'
, 'INVOICE_ID' -- 'INVOICE_ID'
, pv_reference26
, pv_reference27
);
END insert_gl_interface;
SELECT
tax_line.tax_id tax_id
, tax.tax_type tax_type
, SUM(tax_line.tax_amount) tax_amount
FROM
jai_ap_match_inv_taxes tax_line
, jai_cmn_taxes_all tax
WHERE NVL(tax.inclusive_tax_flag,'N') = 'Y'
AND tax_line.invoice_id = pn_invoice_id
AND tax_line.parent_invoice_line_number = pn_invoice_line_num
AND tax_line.tax_id = tax.tax_id
GROUP BY
tax_line.tax_id
, tax.tax_type;
SELECT
tax_line.tax_id tax_id
, tax.tax_type tax_type
, SUM(tax_line.tax_amt) tax_amount
FROM
jai_cmn_document_taxes tax_line
, jai_cmn_taxes_all tax
WHERE NVL(tax.inclusive_tax_flag,'N') = 'Y'
AND tax_line.source_doc_type = jai_constants.G_AP_STANDALONE_INVOICE
AND tax_line.source_doc_id = pn_invoice_id
AND tax_line.source_doc_parent_line_no = pn_invoice_line_num
AND tax_line.tax_id = tax.tax_id
GROUP BY
tax_line.tax_id
, tax.tax_type;
SELECT
dist_code_combination_id
, amount
FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_invoice_line_num;
SELECT
org_id
, gl_date
, invoice_num
, invoice_date
, invoice_currency_code
, exchange_rate
, exchange_rate_type
, exchange_date
INTO
ln_org_id
, ld_gl_date
, lv_invoice_num
, ld_invoice_date
, lv_invoice_currency_code
, ln_exchange_rate
, lv_exchange_rate_type
, ld_exchange_date
FROM
ap_invoices_all
WHERE invoice_id = pn_invoice_id;
SELECT
NVL(ja.inclusive_tax_flag, 'N') inclusive_tax_flag
INTO
lv_inclu_tax_flag
FROM
jai_ap_tds_years ja
WHERE ja.legal_entity_id = ln_org_id
AND sysdate BETWEEN ja.start_date AND ja.end_date;
SELECT
aila.line_number
INTO
ln_invoice_line_num
FROM
ap_invoice_distributions_all aida
, ap_invoice_lines_all aila
WHERE aida.invoice_distribution_id = pn_invoice_distribution_id
AND aila.line_number = aida.invoice_line_number
AND aila.invoice_id = pn_invoice_id
AND aila.line_type_lookup_code = 'ITEM';
SELECT
inventory_item_id
, set_of_books_id
, match_type
, po_distribution_id
, po_line_location_id
, amount
INTO
ln_inventory_item_id
, ln_set_of_books_id
, lv_match_type
, ln_po_dist_id
, ln_po_loc_id
, ln_item_line_amt
FROM
ap_invoice_lines_all
WHERE invoice_id = pn_invoice_id
AND line_number = ln_invoice_line_num;
SELECT
ploc.ship_to_organization_id
, mp.organization_code
INTO
ln_inv_org_id
, lv_inv_org_code
FROM
po_line_locations_all ploc
, mtl_parameters mp
WHERE ploc.line_location_id = ln_po_loc_id
AND ploc.ship_to_organization_id = mp.organization_id;
SELECT
COUNT(invoice_distribution_id)
INTO
ln_invoice_post_num
FROM
ap_invoice_distributions_all aida
WHERE aida.invoice_id = pn_invoice_id
AND aida.invoice_line_number = ln_invoice_line_num
AND aida.posted_flag = 'Y';
insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
, pd_accounting_date => ld_gl_date
, pv_currency_code => lv_invoice_currency_code
, pn_enter_dr => standalone_inclu_tax_csr.tax_amount
, pd_transaction_date => ld_invoice_date
, pn_code_combination_id => ln_tax_account_id
, pd_currency_conversion_date => ld_exchange_date
, pv_currency_conversion_type => lv_exchange_rate_type
, pv_currency_conversion_rate => ln_exchange_rate
, pv_reference1 => lv_inv_org_code
, pv_reference10 => lv_invoice_num
, pv_reference23 => lv_procedure_name
, pv_reference26 => pn_invoice_id
, pv_reference27 => ln_inv_org_id
) ;
insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
, pd_accounting_date => ld_gl_date
, pv_currency_code => lv_invoice_currency_code
, pn_enter_dr => match_inclu_tax_csr.tax_amount
, pd_transaction_date => ld_invoice_date
, pn_code_combination_id => ln_tax_account_id
, pd_currency_conversion_date => ld_exchange_date
, pv_currency_conversion_type => lv_exchange_rate_type
, pv_currency_conversion_rate => ln_exchange_rate
, pv_reference1 => lv_inv_org_code
, pv_reference10 => lv_invoice_num
, pv_reference23 => lv_procedure_name
, pv_reference26 => pn_invoice_id
, pv_reference27 => ln_inv_org_id
) ;
insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
, pd_accounting_date => ld_gl_date
, pv_currency_code => lv_invoice_currency_code
, pn_enter_cr => ln_cr_line_amt
, pd_transaction_date => ld_invoice_date
, pn_code_combination_id => item_line_dist_csr.dist_code_combination_id
, pd_currency_conversion_date => ld_exchange_date
, pv_currency_conversion_type => lv_exchange_rate_type
, pv_currency_conversion_rate => ln_exchange_rate
, pv_reference1 => lv_inv_org_code
, pv_reference10 => lv_invoice_num
, pv_reference23 => lv_procedure_name
, pv_reference26 => pn_invoice_id
, pv_reference27 => ln_inv_org_id
) ;
SELECT CHART_OF_ACCOUNTS_ID
FROM GL_LEDGER_LE_V
WHERE LEDGER_ID = pn_sob_id
AND LEGAL_ENTITY_ID = pn_legal_entity_id;
SELECT APPLICATION_COLUMN_NAME SEGMENT_NAME
FROM FND_ID_FLEX_SEGMENTS
WHERE ID_FLEX_CODE ='GL#'
AND ID_FLEX_NUM = cn_coa_id
AND FLEX_VALUE_SET_ID = (SELECT FLEX_VALUE_SET_ID
FROM FND_ID_FLEX_SEGMENTS SEG , FND_SEGMENT_ATTRIBUTE_VALUES AT
WHERE SEG.APPLICATION_ID = 101
AND SEG.ID_FLEX_CODE = 'GL#'
AND SEG.ID_FLEX_NUM = cn_coa_id
AND AT.APPLICATION_ID = SEG.APPLICATION_ID
AND AT.ID_FLEX_CODE = SEG.ID_FLEX_CODE
AND AT.ID_FLEX_NUM = SEG.ID_FLEX_NUM
AND AT.APPLICATION_COLUMN_NAME = SEG.APPLICATION_COLUMN_NAME
AND AT.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND ATTRIBUTE_VALUE = 'Y');
SELECT DTC_SECTION_CODE
FROM JAI_DTC_SCTN_ACCOUNT_MAPPING
WHERE NATURAL_ACCOUNT_VALUE = cv_natural_account
--Added by Chong for bug#15939571 20121210 Start
-----------------------------------------------------------------------------------------------------
AND from_date <= NVL(cd_accounting_date, sysdate)
AND NVL(to_date, TO_DATE('01019999','DDMMYYYY')) >= NVL(cd_accounting_date, sysdate)
-----------------------------------------------------------------------------------------------------
--Added by Chong for bug#15939571 20121210 End
AND LEGAL_ENTITY_ID = cn_legal_entity_id;
lv_dynamic_sql := 'SELECT ' || PV_SEGMENT_NAME || ' FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = '
|| PN_DISTRIBUTION_CCID;
lv_dynamic_sql := 'SELECT ' || PV_SEGMENT_NAME || ' FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = '
|| PN_DISTRIBUTION_CCID;
SELECT '1'
FROM JAI_RGM_REGISTRATIONS reg,
jai_rgm_definitions def
WHERE
reg.REGISTRATION_TYPE = 'OTHERS' AND
reg.ATTRIBUTE_CODE = 'TDS_CALENDAR' AND
reg.regime_id= def.regime_id AND
def.regime_code = 'TDS';
select '1' from
JAI_AP_TDS_VENDOR_HDRS
where vendor_id = pn_vendor_id;
SELECT '1' FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id;
,p_last_update_date IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATE_DATE%TYPE
,p_last_updated_by IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATED_BY%TYPE
,p_last_update_login IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATE_LOGIN%TYPE
,p_tax_id IN JAI_CMN_TAX_CTG_LINES.TAX_ID%TYPE
)
IS
CURSOR tax_cat_id IS
SELECT JAI_CMN_TAX_CTGS_ALL_S.nextval val_id
FROM dual;
CURSOR tax_cat is SELECT 'X' X
FROM JAI_CMN_TAX_CTGS_ALL
WHERE tax_category_name = p_tax_category_name
AND org_id = to_number(p_org_id);
INSERT INTO JAI_CMN_TAX_CTGS_ALL(
TAX_CATEGORY_ID
,TAX_CATEGORY_NAME
,TAX_CATEGORY_DESC
,ITEM_CLASS_CD
,ORG_ID
,OBJECT_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES(
ln_val_id
,p_tax_category_name
,p_tax_category_desc
,p_item_class_cd
,p_org_id
,p_object_version_number
,p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login
);
INSERT INTO JAI_CMN_TAX_CTG_LINES(
TAX_CATEGORY_ID
,TAX_ID
,LINE_NO
,PRECEDENCE_1
,PRECEDENCE_2
,PRECEDENCE_3
,PRECEDENCE_4
,PRECEDENCE_5
,PRECEDENCE_6
,PRECEDENCE_7
,PRECEDENCE_8
,PRECEDENCE_9
,PRECEDENCE_10
,OBJECT_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES(
ln_val_id
,p_tax_id
,1
,0 --PRECEDENCE_1
,NULL --PRECEDENCE_2
,NULL --PRECEDENCE_3
,NULL --PRECEDENCE_4
,NULL --PRECEDENCE_5
,NULL --PRECEDENCE_6
,NULL --PRECEDENCE_7
,NULL --PRECEDENCE_8
,NULL --PRECEDENCE_9
,NULL --PRECEDENCE_10
,p_object_version_number
,p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login
);
SELECT 'X' X
FROM JAI_CMN_TAX_CTGS_ALL
WHERE tax_category_name = p_tax_category_name
AND org_id = to_number(p_org_id);
SELECT jcta.tax_id
,jcta.tax_rate
,jcta.cess_rate
,jcta.sh_cess_rate
,jcta.surcharge_rate
,jcta.tax_name
,jcta.tax_descr
,jcta.tax_type
,jcta.vendor_id
,jcta.vendor_site_id
,jcta.modifiable_flag
,jcta.tax_account_id
,jcta.orig_tax_percentage
,jcta.org_id
,jcta.duty_drawback_flag
,jcta.vat_flag
,jcta.section_type
,jcta.start_date
,jcta.end_date
FROM JAI_CMN_TAXES_ALL jcta
WHERE jcta.tax_id = p_tax_id
AND jcta.org_id = to_number(p_org_id);
SELECT JAI_CMN_TAX_CTGS_ALL_S.nextval
INTO ln_val_id
FROM dual;
INSERT INTO JAI_CMN_TAX_CTGS_ALL(
TAX_CATEGORY_ID
,TAX_CATEGORY_NAME
,TAX_CATEGORY_DESC
,ITEM_CLASS_CD
,ORG_ID
,OBJECT_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES(
ln_val_id
,p_tax_category_name
,p_tax_category_desc
,p_item_class_cd
,p_org_id
,p_object_version_number
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
);
SELECT JAI_CMN_TAXES_ALL_S.nextval
INTO ln_base_tax_id
FROM dual;
jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
SELECT JAI_CMN_TAXES_ALL_S.nextval
INTO ln_cess_id
FROM dual;
SELECT JAI_CMN_TAXES_ALL_S.nextval
INTO ln_cess_id
FROM dual;
jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
SELECT JAI_CMN_TAXES_ALL_S.nextval
INTO ln_sh_cess_id
FROM dual;
jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
SELECT JAI_CMN_TAXES_ALL_S.nextval
INTO ln_surcharge_id
FROM dual;
jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
PROCEDURE insert_jai_cmn_taxes_all (
p_jai_cmn_taxes_all_rec jai_cmn_taxes_all%ROWTYPE
) IS
BEGIN
--insert tax code
INSERT INTO JAI_CMN_TAXES_ALL(
tax_id
,tax_name
,tax_descr
,tax_type
,tax_rate
,tax_amount
,uom_code
,adhoc_flag
,vendor_id
,vendor_flag
,vendor_site_id
,modifiable_flag
,tax_account_id
,mod_cr_percentage
,stform_type
,orig_tax_percentage
,tds_section
,currency_flag
,start_date
,end_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,org_id
,surcharge_rate
,surcharge_flag
,rounding_factor
,duty_drawback_flag
,duty_drawback_percentage
,vat_flag
,section_type
,section_code
,cess_rate
,object_version_number
,sh_cess_rate
,inclusive_tax_flag
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,tax_type_id
,auto_create_category
,reverse_charge_flag
)
VALUES(
p_jai_cmn_taxes_all_rec.tax_id
,p_jai_cmn_taxes_all_rec.tax_name
,p_jai_cmn_taxes_all_rec.tax_descr
,p_jai_cmn_taxes_all_rec.tax_type
,p_jai_cmn_taxes_all_rec.tax_rate
,p_jai_cmn_taxes_all_rec.tax_amount
,p_jai_cmn_taxes_all_rec.uom_code
,p_jai_cmn_taxes_all_rec.adhoc_flag
,p_jai_cmn_taxes_all_rec.vendor_id
,p_jai_cmn_taxes_all_rec.vendor_flag
,p_jai_cmn_taxes_all_rec.vendor_site_id
,p_jai_cmn_taxes_all_rec.modifiable_flag
,p_jai_cmn_taxes_all_rec.tax_account_id
,p_jai_cmn_taxes_all_rec.mod_cr_percentage
,p_jai_cmn_taxes_all_rec.stform_type
,p_jai_cmn_taxes_all_rec.orig_tax_percentage
,p_jai_cmn_taxes_all_rec.tds_section
,p_jai_cmn_taxes_all_rec.currency_flag
,p_jai_cmn_taxes_all_rec.start_date
,p_jai_cmn_taxes_all_rec.end_date
,p_jai_cmn_taxes_all_rec.creation_date
,p_jai_cmn_taxes_all_rec.created_by
,p_jai_cmn_taxes_all_rec.last_update_date
,p_jai_cmn_taxes_all_rec.last_updated_by
,p_jai_cmn_taxes_all_rec.last_update_login
,p_jai_cmn_taxes_all_rec.org_id
,p_jai_cmn_taxes_all_rec.surcharge_rate
,p_jai_cmn_taxes_all_rec.surcharge_flag
,p_jai_cmn_taxes_all_rec.rounding_factor
,p_jai_cmn_taxes_all_rec.duty_drawback_flag
,p_jai_cmn_taxes_all_rec.duty_drawback_percentage
,p_jai_cmn_taxes_all_rec.vat_flag
,p_jai_cmn_taxes_all_rec.section_type
,p_jai_cmn_taxes_all_rec.section_code
,p_jai_cmn_taxes_all_rec.cess_rate
,p_jai_cmn_taxes_all_rec.object_version_number
,p_jai_cmn_taxes_all_rec.sh_cess_rate
,p_jai_cmn_taxes_all_rec.inclusive_tax_flag
,p_jai_cmn_taxes_all_rec.attribute1
,p_jai_cmn_taxes_all_rec.attribute2
,p_jai_cmn_taxes_all_rec.attribute3
,p_jai_cmn_taxes_all_rec.attribute4
,p_jai_cmn_taxes_all_rec.attribute5
,p_jai_cmn_taxes_all_rec.attribute6
,p_jai_cmn_taxes_all_rec.attribute7
,p_jai_cmn_taxes_all_rec.attribute8
,p_jai_cmn_taxes_all_rec.attribute9
,p_jai_cmn_taxes_all_rec.attribute10
,p_jai_cmn_taxes_all_rec.attribute11
,p_jai_cmn_taxes_all_rec.attribute12
,p_jai_cmn_taxes_all_rec.attribute13
,p_jai_cmn_taxes_all_rec.attribute14
,p_jai_cmn_taxes_all_rec.attribute15
,p_jai_cmn_taxes_all_rec.attribute_category
,p_jai_cmn_taxes_all_rec.tax_type_id
,p_jai_cmn_taxes_all_rec.auto_create_category
,p_jai_cmn_taxes_all_rec.reverse_charge_flag
);
jai_cmn_utils_pkg.WRITE_FND_LOG(FND_LOG.LEVEL_PROCEDURE, 'JAI.PLSQL.JAI_AP_UTILS_PKG.insert_jai_cmn_taxes_all',
'Created a new tax code ' || p_jai_cmn_taxes_all_rec.tax_id || '-' || p_jai_cmn_taxes_all_rec.tax_name);
END insert_jai_cmn_taxes_all;
PROCEDURE insert_jai_cmn_tax_ctg_lines (
p_jai_cmn_tax_ctg_lines_rec jai_cmn_tax_ctg_lines%ROWTYPE
) IS
BEGIN
--insert tax tagecode
INSERT INTO jai_cmn_tax_ctg_lines(
tax_category_id
,tax_id
,line_no
,precedence_1
,precedence_2
,precedence_3
,precedence_4
,precedence_5
,precedence_6
,precedence_7
,precedence_8
,precedence_9
,precedence_10
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,object_version_number
)
VALUES(
p_jai_cmn_tax_ctg_lines_rec.tax_category_id
,p_jai_cmn_tax_ctg_lines_rec.tax_id
,p_jai_cmn_tax_ctg_lines_rec.line_no
,p_jai_cmn_tax_ctg_lines_rec.precedence_1
,p_jai_cmn_tax_ctg_lines_rec.precedence_2
,p_jai_cmn_tax_ctg_lines_rec.precedence_3
,p_jai_cmn_tax_ctg_lines_rec.precedence_4
,p_jai_cmn_tax_ctg_lines_rec.precedence_5
,p_jai_cmn_tax_ctg_lines_rec.precedence_6
,p_jai_cmn_tax_ctg_lines_rec.precedence_7
,p_jai_cmn_tax_ctg_lines_rec.precedence_8
,p_jai_cmn_tax_ctg_lines_rec.precedence_9
,p_jai_cmn_tax_ctg_lines_rec.precedence_10
,p_jai_cmn_tax_ctg_lines_rec.creation_date
,p_jai_cmn_tax_ctg_lines_rec.created_by
,p_jai_cmn_tax_ctg_lines_rec.last_update_date
,p_jai_cmn_tax_ctg_lines_rec.last_updated_by
,p_jai_cmn_tax_ctg_lines_rec.last_update_login
,p_jai_cmn_tax_ctg_lines_rec.object_version_number
);
jai_cmn_utils_pkg.WRITE_FND_LOG(FND_LOG.LEVEL_PROCEDURE, 'JAI.PLSQL.JAI_AP_UTILS_PKG.insert_jai_cmn_tax_ctg_lines',
'Created a tax category line ' || p_jai_cmn_tax_ctg_lines_rec.line_no || '-' || p_jai_cmn_tax_ctg_lines_rec.tax_id);
END insert_jai_cmn_tax_ctg_lines;
select new_section_code
from jai_tds_section_mapping
where old_section_code = pv_tds_section_code;
select 1
from ja_lookups
where lookup_code = cv_section_code;
delete from jai_tds_section_mapping;
insert into
jai_tds_section_mapping
(
mapping_id,
old_section_code,
new_section_code
)
values(
JAI_TDS_SECTION_MAPPING_S.nextval,
l_line_old,
l_line_new
);
Fnd_File.put_line(Fnd_File.LOG,'0 rows inserted , Please check files to ensure the Section Code is avaiable in System.');
Fnd_File.put_line(Fnd_File.LOG,ln_sum ||' rows inserted ');
To update Section code to new Section Code in Natural Account Mapping Table.
*/
procedure update_natural_sec_mapping
is
lx_ex exception;
update_count_out number;
select old_section_code,new_section_code
from jai_tds_section_mapping
)
loop
update JAI_DTC_SCTN_ACCOUNT_MAPPING
set dtc_section_code = rec_maps.new_section_code
where dtc_section_code = rec_maps.old_section_code;
update_count_out := SQL%ROWCOUNT;
Fnd_File.put_line(Fnd_File.output,'In table JAI_DTC_SCTN_ACCOUNT_MAPPING Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
Fnd_File.put_line(Fnd_File.log,'In table JAI_DTC_SCTN_ACCOUNT_MAPPING Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
end update_natural_sec_mapping;
To update Addtional information default section code
*/
procedure update_suppl_default_sec
is
lx_ex exception;
update_count_out number;
select old_section_code,new_section_code
from jai_tds_section_mapping
)
loop
update JAI_AP_TDS_VENDOR_HDRS
set section_code = rec_maps.new_section_code
where section_code = rec_maps.old_section_code;
update_count_out := SQL%ROWCOUNT;
Fnd_File.put_line(Fnd_File.output,'In table JAI_AP_TDS_VENDOR_HDRS Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
Fnd_File.put_line(Fnd_File.log,'In table JAI_AP_TDS_VENDOR_HDRS Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
end update_suppl_default_sec;
/*To update actual section code in table JAI_AP_TDS_INV_TAXES*/
procedure update_inv_tax_sec
is
lx_ex exception;
update_count_out number;
select old_section_code,new_section_code
from jai_tds_section_mapping
)
loop
update JAI_AP_TDS_INV_TAXES
set actual_section_code = rec_maps.new_section_code
where actual_section_code = rec_maps.old_section_code;
update_count_out := SQL%ROWCOUNT;
Fnd_File.put_line(Fnd_File.output,'In table JAI_AP_TDS_INV_TAXES Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
Fnd_File.put_line(Fnd_File.log,'In table JAI_AP_TDS_INV_TAXES Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
end update_inv_tax_sec;
Fnd_File.put_line(Fnd_File.LOG,'Step 2 begin: Update Section Code for Natural Account Mapping.');
update_natural_sec_mapping;
Fnd_File.put_line(Fnd_File.LOG,'Step 3 begin: Update Defaulted Section Code for Supplier Site.');
update_suppl_default_sec;
Fnd_File.put_line(Fnd_File.LOG,'Step 4 begin: Update Actual Section Code for Invoice Tax.');
update_inv_tax_sec;