[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
added the who columns in the insert commands for the tables
JAI_AR_SUP_INV_T and JAI_AR_TRX_SUP_INV_T.
Dependencies Due to this bug:-
None
27-Dec-2005 Bug 4906958. Added by Lakshmi Gopalsami Version 120.6
Derived the value for default LE if the value is not retrieved via
default BSV.
07-Mar-2006 Modified by aiyer for the bug 4947681, File version 120.7
Issue :-
Performance tuning for the cursor get_invoices SQL_ID 14827611. Cost = 1287 and sharabale memory 182519
Fix :-
Modified the cursor get_invoices. Added the IL table jai_ar_trxs to the ra_customer_trx_all
and also merged the correlated query to the main query thus reducing the sharable memory also
01/11/2006 SACSETHI for bug 5228046, File version 120.10
1. Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
2. Forward porting of 11i bug 5219225
Dependency Due to This bug :-
None
10/09/2007 Bgowrava for Bug#6400997, File Version 120.9.12000000.3
Debug messages added to apply on the instance and test for errors.
28-Jan-2011 bug 10358786 shujuan Yan Modified
Inclusive Tax
11-May-2011 Bug#12537533 zhiwei Hou modified
The transaction from Open Interface with External event should not be picked up
while supplementary transaction generation.
20-Jan-2011 bug 12844458 mmurtuza
Description: INCORRECT TAX CALCULATION FOR SUPPLEMENTARY INVOICES
Fix: For the varibales v_tax_amt, vamt removed precision of (10,2). The declaration is just as NUMBER.
-----------------------------------------------------------------------------------------*/
PROCEDURE process_report_stpr(p_batch_id number) is
BEGIN
jai_ar_sup_trx_pkg.identify_invoices(p_batch_id);
CURSOR CH(N NUMBER,M VARCHAR2) IS SELECT 1 FROM JAI_AR_TRX_SUP_INV_T WHERE CUSTOMER_TRX_ID = N
AND MAPPING_TYPE = M;
Cursor ch_tax(id number,tax_name number) is select 1 from JAI_AR_TRX_SUP_INV_T where customer_trx_id = id
and mapping_type = 'T'
and existing_type_id = tax_name;
SELECT SET_OF_BOOKS_ID,
ORG_ID,
CUSTOMER_ID,
SITE_USE_ID,
EXISTING_TYPE_ID,
MAPPING_ID,
MAPPING_TYPE,
NEW_TYPE_ID,
EFFECTIVE_FROM_DATE,
EFFECTIVE_TO_DATE,
CONVERSION_RATE,
BATCH_ID
FROM JAI_AR_SUP_CHANGES_ALL
WHERE PROCESS_DATE IS NULL
AND BATCH_ID = NVL(P_BATCH_ID,BATCH_ID)
ORDER BY SITE_USE_ID,
CUSTOMER_ID,
ORG_ID,
SET_OF_BOOKS_ID;
SELECT CURRENCY_CODE
FROM QP_LIST_HEADERS_B
WHERE LIST_HEADER_ID = P_LIST_ID;
SELECT
rcta.customer_trx_id,
rcta.trx_date,
rcta.set_of_books_id,
rcta.bill_to_customer_id,
rcta.bill_to_site_use_id,
rcta.org_id,
rcta.interface_header_context ,
rcta.interface_header_attribute1,
rcta.interface_header_attribute6,
rcta.interface_header_attribute7,
rcta.invoice_currency_code
FROM ra_customer_trx_all rcta,
jai_ar_trxs jat ,
ra_cust_trx_types_all rctta
WHERE
rcta.customer_trx_id = jat.customer_trx_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND rctta.type = 'INV'
AND trunc(rcta.trx_date) between trunc(effective_from_date) AND trunc(nvl(p_effective_to_date,sysdate))
--Added by zhiwei for Bug#12537533 on 20110511 begin
-----------------------------------------------------------
AND NOT EXISTS
(
SELECT 1
FROM JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = rcta.customer_trx_id
AND INTERFACE_FLAG = 'Y'
AND UPPER(INTERFACE_EVENT) = 'EXTERNAL'
)
-----------------------------------------------------------
--Added by zhiwei for Bug#12537533 on 20110511 end
AND ( rcta.interface_header_context IS NULL
OR rcta.interface_header_context = cp_int_hdr_cxt
--Added by zhiwei for Bug#12615714 begin
---------------------------------------------------------------------
--or rcta.interface_header_context = 'OFI TAX IMPORT'--Added by zhiwei for Bug#12615714 on 20110629
or exists(
select 1
from ra_customer_trx_lines_all
where customer_trx_id = rcta.customer_trx_id
and nvl( global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'
)
---------------------------------------------------------------------
--Added by zhiwei for Bug#12615714 end
);
SELECT 1
FROM JAI_AR_SUP_HDRS_ALL
WHERE CUSTOMER_TRX_ID = ID ;
CURSOR SELECT_FILTER_INV IS
SELECT * FROM JAI_AR_SUP_INV_T;
SELECT SET_OF_BOOKS_ID,
ORG_ID,
CUSTOMER_ID,
SITE_USE_ID
FROM JAI_AR_SUP_CHANGES_ALL; */
INSERT INTO JAI_AR_SUP_INV_T
(CUSTOMER_TRX_ID,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
MAPPING_ID,
TRX_DATE,
EFFECTIVE_DATE,
SET_OF_BOOKS_ID,
CUSTOMER_ID,
ORG_ID,
CUSTOMER_SITE,
-- added, Harshita for Bug 4866533
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(ONE.CUSTOMER_TRX_ID,
ONE.INTERFACE_HEADER_CONTEXT ,
ONE.INTERFACE_HEADER_ATTRIBUTE1,
ONE.INTERFACE_HEADER_ATTRIBUTE6 ,
ONE.INTERFACE_HEADER_ATTRIBUTE7,
MAIN.MAPPING_ID,
ONE.TRX_DATE,
MAIN.EFFECTIVE_FROM_DATE,
ONE.SET_OF_BOOKS_ID,
ONE.BILL_TO_CUSTOMER_ID,
NVL(ONE.ORG_ID,0),
ONE.BILL_TO_SITE_USE_ID,
-- added, Harshita for Bug 4866533
LN_CREATED_BY,
LD_CREATION_DATE,
LN_CREATED_BY,
LD_CREATION_DATE
);
for i in SELECT_FILTER_INV
loop
for j in MAP_IDENT
loop
IF I.TRX_DATE BETWEEN J.EFFECTIVE_FROM_DATE AND SYSDATE THEN
IF J.MAPPING_TYPE = 'T' THEN
OPEN CH_TAX(I.CUSTOMER_TRX_ID,J.EXISTING_TYPE_ID);
select count(1)
into ln_open_api
from ra_customer_trx_lines_all
where customer_trx_id = I.CUSTOMER_TRX_ID
and nvl( global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT';
INSERT INTO JAI_AR_TRX_SUP_INV_T
(CUSTOMER_TRX_ID,
EXISTING_TYPE_ID,
NEW_TYPE_ID,
MAPPING_TYPE,
MAPPING_ID,
TRX_DATE,
EFFECTIVE_DATE,
SET_OF_BOOKS_ID,
ORG_ID,
CUSTOMER_SITE,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
CONVERSION_RATE,
-- added, Harshita for Bug 4866533
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (I.CUSTOMER_TRX_ID,
J.EXISTING_TYPE_ID,
J.NEW_TYPE_ID,
J.MAPPING_TYPE,
J.MAPPING_ID,
I.TRX_DATE,
J.EFFECTIVE_FROM_DATE,
J.SET_OF_BOOKS_ID,
J.ORG_ID,
J.SITE_USE_ID,
I.INTERFACE_HEADER_CONTEXT,
I.INTERFACE_HEADER_ATTRIBUTE1,
I.INTERFACE_HEADER_ATTRIBUTE6,
I.INTERFACE_HEADER_ATTRIBUTE7,
J.CONVERSION_RATE,
-- added, Harshita for Bug 4866533
LN_CREATED_BY,
LD_CREATION_DATE,
LN_CREATED_BY,
LD_CREATION_DATE
);
INSERT INTO JAI_AR_TRX_SUP_INV_T
(CUSTOMER_TRX_ID,
EXISTING_TYPE_ID,
NEW_TYPE_ID,
MAPPING_TYPE,
MAPPING_ID,
TRX_DATE,
EFFECTIVE_DATE,
SET_OF_BOOKS_ID,
ORG_ID,
CUSTOMER_SITE,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
CONVERSION_RATE,
-- added, Harshita for Bug 4866533
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (I.CUSTOMER_TRX_ID,
J.EXISTING_TYPE_ID,
J.NEW_TYPE_ID,
J.MAPPING_TYPE,
J.MAPPING_ID,
I.TRX_DATE,
J.EFFECTIVE_FROM_DATE,
J.SET_OF_BOOKS_ID,
J.ORG_ID,
J.SITE_USE_ID,
I.INTERFACE_HEADER_CONTEXT,
I.INTERFACE_HEADER_ATTRIBUTE1,
I.INTERFACE_HEADER_ATTRIBUTE6,
I.INTERFACE_HEADER_ATTRIBUTE7,
J.CONVERSION_RATE,
-- added, Harshita for Bug 4866533
LN_CREATED_BY,
LD_CREATION_DATE,
LN_CREATED_BY,
LD_CREATION_DATE
);
INSERT INTO JAI_AR_TRX_SUP_INV_T
(CUSTOMER_TRX_ID,
EXISTING_TYPE_ID,
NEW_TYPE_ID,
MAPPING_TYPE,
MAPPING_ID,
TRX_DATE,
EFFECTIVE_DATE,
SET_OF_BOOKS_ID,
ORG_ID,
CUSTOMER_SITE,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
CONVERSION_RATE,
-- added, Harshita for Bug 4866533
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(I.CUSTOMER_TRX_ID,
J.EXISTING_TYPE_ID,
J.NEW_TYPE_ID,
J.MAPPING_TYPE,
J.MAPPING_ID,
I.TRX_DATE,
J.EFFECTIVE_FROM_DATE,
J.SET_OF_BOOKS_ID,
J.ORG_ID,
J.SITE_USE_ID,
I.INTERFACE_HEADER_CONTEXT,
I.INTERFACE_HEADER_ATTRIBUTE1,
I.INTERFACE_HEADER_ATTRIBUTE6,
I.INTERFACE_HEADER_ATTRIBUTE7,
J.CONVERSION_RATE,
-- added, Harshita for Bug 4866533
LN_CREATED_BY,
LD_CREATION_DATE,
LN_CREATED_BY,
LD_CREATION_DATE
);
UPDATE JAI_AR_SUP_CHANGES_ALL
SET PROCESS_DATE = SYSDATE
WHERE BATCH_ID = P_BATCH_ID
AND PROCESS_DATE IS NULL;
SELECT A.*, B.BATCH_ID
FROM JAI_AR_TRX_SUP_INV_T A,
JAI_AR_SUP_CHANGES_ALL B
WHERE A.MAPPING_ID = B.MAPPING_ID
ORDER BY CUSTOMER_TRX_ID;
SELECT customer_trx_id
FROM JAI_AR_SUP_HDRS_ALL
WHERE customer_trx_id = INV;
SELECT 1
FROM JAI_AR_SUP_HDRS_ALL
WHERE customer_trx_id = NO
AND supp_inv_type = STAT;
SELECT
b.customer_trx_line_id,
a.org_id,
a.set_of_books_id,
a.bill_to_customer_id,
a.bill_to_site_use_id,
b.line_number,
b.uom_code,
a.ship_to_site_use_id,
a.interface_header_context,
b.unit_selling_price,
b.line_type,
b.link_to_cust_trx_line_id,
b.description,
b.interface_line_attribute3, -- delivery id is stored here.
b.interface_line_attribute7,
b.interface_line_attribute6, -- oe_order_lines_all.line_id
b.inventory_item_id,
c.bom_item_type,
d.mapping_type, -- added by sriram - bug # 3143813 - 19/09/2003
d.new_type_id,
/* Bug 4224466. Added by LGOPALSA */
a.invoice_currency_code
,b.global_attribute_category --Added by zhiwei for Bug#12615714
FROM ra_customer_trx_all a,
ra_customer_trx_lines_all b,
mtl_system_items c,
jai_ar_trx_sup_inv_t d, -- added by sriram - bug # 3143813 - 19/09/2003
jai_ar_trxs jat /* added the table and join condition - aiyer for the fwd porting bug 5225768*/
WHERE a.customer_trx_id = b.customer_trx_id
AND b.inventory_item_id = c.inventory_item_id
--AND c.organization_id = b.warehouse_id
AND ( b.interface_line_attribute11 IS NULL
OR b.interface_line_attribute11 = '0'
or b.global_attribute_category = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'--Added by zhiwei for Bug#12615714 on 20110629
)
AND d.customer_trx_id = a.customer_trx_id
AND b.line_type = cp_line_type --'LINE'
AND a.customer_trx_id = ino
AND c.organization_id = jat.organization_id
AND a.customer_trx_id = jat.customer_trx_id ;
SELECT product_attr_value, list_header_id
FROM qp_List_Lines_v
WHERE List_header_id = p_list_header_id
AND product_attribute_context = 'ITEM'
AND product_attr_value = p_inv_item_id
AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
SELECT b.operand list_price
FROM QP_LIST_LINES b, qp_pricing_attributes c
WHERE c.PRODUCT_ATTR_VALUE = TO_CHAR(Id)
AND c.product_uom_code = UNT_CODE
AND b.list_header_id = PRICE_LIST
AND c.list_line_id = b.list_line_id
AND NVL(TRUNC(b.end_date_active),TRUNC(SYSDATE)) >= TRUNC(TRDATE);
SELECT DISTINCT A.CUST_ACCT_SITE_ID ADDRESS_ID
FROM HZ_CUST_SITE_USES_ALL A,
OE_ORDER_LINES_ALL B
WHERE A.SITE_USE_ID = B.SHIP_TO_ORG_ID
AND B.LINE_ID = PICKING_ID;
SELECT CUST_ACCT_SITE_ID ADDRESS_ID
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = p_ship_to_site_id;
SELECT PRICE_LIST_ID
FROM JAI_CMN_CUS_ADDRESSES
WHERE ADDRESS_ID = NVL(ADDRESS,0)
AND CUSTOMER_ID = INO1;
SELECT PRICE_LIST_ID
FROM JAI_CMN_CUS_ADDRESSES
WHERE ADDRESS_ID = 0
AND CUSTOMER_ID = v_cust_id;
SELECT b.operand list_price
FROM QP_LIST_LINES b, qp_pricing_attributes c
WHERE c.PRODUCT_ATTR_VALUE = TO_CHAR(INVENT)
AND c.product_uom_code = UNT
AND b.list_header_id = NEW_NO
AND c.list_line_id = b.list_line_id
AND NVL(TRUNC(b.end_date_active),TRUNC(SYSDATE)) >= TRUNC(ADATE);
Select precision
from fnd_currencies
where currency_code = lv_currency_code;
SELECT nvl( decode(list_line_type_code, lv_list_line_type_code_dis, operand, lv_list_line_type_code_sur, -operand), 0) operand, arithmetic_operator
--nvl( decode(list_line_type_code, 'DIS', operand, 'SUR', -operand), 0) operand, arithmetic_operator
FROM oe_price_adjustments a -- , qp_modifier_summary_v b
WHERE list_line_type_code IN ('DIS', 'SUR')
AND modifier_level_code = 'LINE'
AND price_adjustment_id = p_price_adjustment_id;
SELECT
SUM(a.quantity), SUM(a.tax_amount),
a.selling_price, a.unit_code, a.tax_category_id,
a.picking_line_id, a.assessable_value
FROM
jai_om_wsh_lines_all A
WHERE
to_char(a.delivery_id) = p_interface_line_attribute3
AND to_char(a.order_line_id) = p_interface_line_attribute6
AND a.inventory_item_id = p_inventory_item_id
GROUP BY
a.selling_price, a.unit_code, a.tax_category_id,
a.picking_line_id, a.assessable_value ;
SELECT JAI_AR_SUP_LINES_S.NEXTVAL
FROM DUAL;
changed a query to select price_list_id basing on Header_Id rather than basing on order_number.
2 23/03/2001 Anuradha Parthasarathy
To ensure that correct assessable value is considered in case of Price List Mapping
3. 19/09/2003 ssumaith - bug # 3143813
Supplementary invoices in Ar are generated for all items in the invoice even though they are not part of the new price list. This has been rectified
by adding joins between the inventory item of the invoice and inventory item in the pricelist for price list types of mapping.
4. 28/03/2004 Vijay Shankar for bug # 3515883, Version: 619.1
- modified cursor GET_INV_LINE to filter out Modifier lines from RA_CUSTOMER_TRX_LINES_ALL table and added a
seperate code that reduces/adds discounts/surcharges from actual item price.
Added Cursor c_adjustment_details and required variables for discounts/surchages calculation
- Cursor C_PRICE_LIST_INFO is modified to use qp_list_lines_v View instead of qp_list_lines and qp_pricing_attributes tables.
Added inventory_item_id, Start and end date checks to where clause on qp_list_lines_v
- Query to fetch details from JAI_OM_WSH_LINES_ALL is modified by adding Order_line_id filter.
Also removed the usage of RA_CUSTOMER_TRX_LINES_ALL table from the query which is redundant
5. 15/03/2005 Bug 4224466. Added by LGOPALSA. Version 115.2
Added rounding code for Exist and new Sales amount.
File Renamed to jai_ar_sup_trx.plb
-----------------------------------
6. 25/05/2005 Brathod, For Bug# 4392001, Fileversion# 116.1
Issue:-
RA_INTERFACE_LINES DFF segments needs to be limited use only one segment
Fix:-
- Following four segments will be obsoleted
1. SUPPLEMENT CM
2. SUPPLEMENT DM
3. SUPPLEMENT INVOICE
4. TDS CREDIT
- A new segment (INDIA INVOICES) will be created with following attributes
1. INTERFACE_LINE_ATTRIBUTE1 - Invoice Type
2. INTERFACE_LINE_ATTRIBUTE2 - Unique Identifier
- As new dff uses the ATTRIBUTE1 field the existing values of ATTRIBUTE1 will be
migrated to ATTRIBUTE4
- Attribute context will be changed to INDIA INVOICES.
- INTERFACE_LINE_ATTRIBUTE1 will identify the type of invoice the possible values
for this field will be same as different segments used previously
i.e SUPPLEMENT CM, SUPPLEMENT DM, SUPPLEMENT INVOICE, TDS CREDIT
7. Aiyer for the bug 5378631, File version 120.9
Issues:-
India Suggestive Supplementary transactions report does not pick up any invoices.
As a part of this fix fwd porting the following fixes:-
==================================================
Slno 11i Bug R12 FWD Porting bug
==================================================
1. 4324523 No Fwd Porting bug logged
2. 4735409 No Fwd Porting bug logged
3. 4764676 4955151
4. 5104197 5225768
===================================================
1. Bug 4324523:- Report India Suggestive Supplementary transactions errors out "PL/SQL: numeric or value error: character string buffer too small"
2. code text exceed 255 characters.
3.Supplementary transactions picks partial qty for dr/cr when line is split.
4.Supplementary invoice not being generated for tax change
Fix :-
1.Bug 4324523:-
Fixed the data type mismatch for old_tax_type and new_tax_type with the tax_type column of ja_in_tax_codes
in the declaration section. (11i bug 4324523)
2.Bug 4735409:-
Aligned so that the code doesn't exceed 255 characters (11i bug 4735409 )
3.Bug 4764676:-
When split happens at the time of shipment, code in this procedure, which is a Select..Into statement raises an exception.
Commented the Select..Into statment and added a Cursor. Added the cursor c_get_so_picking_lines which considers all the lines
(result of split) to get the Total Quantity and Total Tax Amount. (11i bug 4764676, Fwd porting bug 4955151)
4. Bug 5104197:-
Modified the cursor get_inv_line. Removed the join condition for organization_id of mtl_system_items and
warehouse_id of ra_customer_trx_lines_all. Included a join condition for organization_id of mtl_system_items and jai_ar_trx.
This has been done because there are cases where the warehouse_id in the ra_customer_trx_lines_all is null. Hence removed that link.
(11i bug 5104197, fwd porting bug 5225768)
25-June-2007 Bgowrava for Bug#6139693, File Version 120.12
Added Cursor c_get_sup_seq_val which is used to generate sequence numbers from the sequence
JAI_AR_SUP_LINES_S. and this is used as the value for the customer_trx_line_id
of the table JAI_AR_SUP_LINES.
07-Jul-2007 Bgowrava for Bug#6139693, File Version 120.13
Added the NVL condition for the EXIST_NET_SALES_AMT fields in the update statements of
JAI_AR_SUP_HDRS_ALL.
12-Sep-2007 Bgowrava for bug#6400997, File Version 120.9.12000000.6
Added the columns SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS in the insert statements
and assigned values 'N' to both of them. This was done because the default values were
not getting defaulted.
08-AUG-2008 JMEENA for bug#7567647
Commented the cursor c_get_sup_seq_val and replaced the cursor generated v_cust_trx_line_no_s
with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2.
05-Apr-2013 mmurtuza for bug 16580917
Description: CESS and SH CESS LOADED TO EXISTING OTHERS AMT INSTEAD OF EXISTING EXCISE AMT
Fix: Added UPPER function to include all excise type of taxes in Excise Amount
------------------------------------------------------------------------------------------------------------------------------*/
v_today := trunc(SYSDATE); --Ramananda for File.Sql.35
|| Commented the select into query and added the below cursor statements.
|| When Split is done at the time of shipment, the above SELECT..INTO returns more than one row, which causes an exception
|| Added the following cursor statements which gets the Total Quantity and Total Tax Amount
|| grouped on the selling_price, unit_code, tax_category_id, picking_line_id, assessable_value
*/
OPEN c_get_so_picking_lines (
i.interface_line_attribute3 ,
i.interface_line_attribute6 ,
i.inventory_item_id
) ;
SELECT QUANTITY, TAX_CATEGORY_ID, ASSESSABLE_VALUE
INTO AQUANTITY, ATAX_CATEGORY_ID, OLD_ASSESSABLE_VALUE
FROM JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
SELECT DISTINCT CUSTOMER_TRX_ID, EXISTING_TYPE_ID, NEW_TYPE_ID, Mapping_id
INTO P_CUSTOMER_TRX_ID, P_EXISTING_TYPE_ID, P_NEW_TYPE_ID, v_p_mapping_id
FROM JAI_AR_TRX_SUP_INV_T
WHERE MAPPING_TYPE = 'P'
AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
SELECT PRICE_LIST_ID
INTO P_PRICE_LIST
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = (SELECT header_id
FROM oe_order_lines_all
WHERE line_id = FETCH_INV.INTERFACE_HEADER_ATTRIBUTE6
AND org_id = FETCH_INV.org_id);
FOR dis IN (SELECT customer_trx_line_id, to_number(interface_line_attribute11) price_adjustment_id
--, line_number, unit_standard_price, unit_selling_price, extended_amount, revenue_amount
FROM ra_customer_trx_lines_all
WHERE interface_line_attribute11 <> '0'
AND interface_line_attribute6 = I.interface_line_attribute6
AND customer_trx_id = fetch_inv.customer_trx_id
AND line_type = 'LINE')
LOOP
r_adj := null;
INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE, LAST_UPDATE_DATE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT,
SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS, SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id, OAMOUNT, NAMOUNT,
I.SET_OF_BOOKS_ID, I.ORG_ID, I.BILL_TO_SITE_USE_ID, FETCH_INV.BATCH_ID, 'N', 'N'
);--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
UPDATE JAI_AR_SUP_HDRS_ALL
SET EXIST_NET_SALES_AMT = nvl(EXIST_NET_SALES_AMT,0)+OAMOUNT,
NEW_NET_SALES_AMT = nvl(NEW_NET_SALES_AMT,0) +NAMOUNT
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND SUPP_INV_TYPE = STATUS;
/*bug#7567647 Replaced v_cust_trx_line_no_s with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2*/
INSERT INTO JAI_AR_SUP_LINES(
CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, INVENTORY_ITEM_ID, LINE_NUMBER, SUP_INV_TYPE,
DESCRIPTION, QUANTITY, UNIT_CODE, TAX_CATEGORY_ID,
OLD_SELLING_PRICE, NEW_SELLING_PRICE, EXIST_LINE_AMT, NEW_LINE_AMT,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY, PL_MAPPING_ID, interface_line_Attribute2
) VALUES (
FETCH_INV.CUSTOMER_TRX_ID, I.CUSTOMER_TRX_LINE_ID, I.INVENTORY_ITEM_ID, I.LINE_NUMBER, STATUS,
I.DESCRIPTION, AQUANTITY, AUNIT_CODE, ATAX_CATEGORY_ID,
ASELLING_PRICE, C_LIST_PRICE, OAMOUNT, NAMOUNT,
SYSDATE, fnd_global.user_id, NULL,
SYSDATE, fnd_global.user_id, v_p_mapping_id,I.CUSTOMER_TRX_LINE_ID
);
SELECT UNIT_SELLING_PRICE
INTO I_UNIT_SELLING_PRICE
FROM JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
SELECT CUSTOMER_TRX_ID, NEW_TYPE_ID, mapping_id
INTO I_CUSTOMER_TRX_ID, I_NEW_TYPE_ID, v_i_mapping_id
FROM JAI_AR_TRX_SUP_INV_T
WHERE MAPPING_TYPE = 'I'
AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT, SET_OF_BOOKS_ID,
CUSTOMER_SITE, ORG_ID, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS,
SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id,
OLD_INVOICE_AMOUNT, NEW_INVOICE_AMOUNT, I.SET_OF_BOOKS_ID,
I.BILL_TO_SITE_USE_ID, I.ORG_ID, FETCH_INV.BATCH_ID, 'N', 'N'
);--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
UPDATE JAI_AR_SUP_HDRS_ALL
SET EXIST_NET_SALES_AMT = nvl(EXIST_NET_SALES_AMT,0)+ OLD_INVOICE_AMOUNT,
NEW_NET_SALES_AMT = nvl(NEW_NET_SALES_AMT,0) +NEW_INVOICE_AMOUNT
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND SUPP_INV_TYPE = STATUS;
/*bug#7567647 Replaced v_cust_trx_line_no_s with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2*/
INSERT INTO JAI_AR_SUP_LINES(
CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, INVENTORY_ITEM_ID, LINE_NUMBER,
DESCRIPTION, SUP_INV_TYPE, QUANTITY, UNIT_CODE,
TAX_CATEGORY_ID, OLD_SELLING_PRICE, NEW_SELLING_PRICE,
EXIST_LINE_AMT, NEW_LINE_AMT, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, PL_MAPPING_ID,interface_line_attribute2
) VALUES (
FETCH_INV.CUSTOMER_TRX_ID, I.CUSTOMER_TRX_LINE_ID, I.INVENTORY_ITEM_ID, I.LINE_NUMBER,
I.DESCRIPTION, STATUS, AQUANTITY, AUNIT_CODE,
ATAX_CATEGORY_ID, I_UNIT_SELLING_PRICE, C_LIST_PRICE,
OLD_INVOICE_AMOUNT, NEW_INVOICE_AMOUNT, SYSDATE, fnd_global.user_id,
NULL, SYSDATE, fnd_global.user_id, v_I_mapping_id,I.CUSTOMER_TRX_LINE_ID
);
SELECT CUSTOMER_TRX_ID, EXISTING_TYPE_ID, NEW_TYPE_ID, mapping_id
INTO A_CUSTOMER_TRX_ID, A_EXISTING_TYPE_ID, A_NEW_TYPE_ID, v_a_mapping_id
FROM JAI_AR_TRX_SUP_INV_T
WHERE MAPPING_TYPE = 'A'
AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
DELETE JAI_AR_TRX_TAX_CALC_T;
SELECT *
FROM JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
SELECT TEMP.TAX_LINE_NO,
TEMP.CUSTOMER_TRX_LINE_ID,
TEMP.LINK_TO_CUST_TRX_LINE_ID,
TEMP.TAX_ID,
TEMP.TAX_RATE,
TEMP.QTY_RATE,
TEMP.UOM,
TEMP.TAX_AMOUNT,
TEMP.FUNC_TAX_AMOUNT,
TEMP.BASE_TAX_AMOUNT,
TEMP.PRECEDENCE_1,
TEMP.PRECEDENCE_2,
TEMP.PRECEDENCE_3,
TEMP.PRECEDENCE_4,
TEMP.PRECEDENCE_5,
TEMP.PRECEDENCE_6,
TEMP.PRECEDENCE_7,
TEMP.PRECEDENCE_8,
TEMP.PRECEDENCE_9,
TEMP.PRECEDENCE_10,
COD.ADHOC_FLAG
FROM JAI_AR_TRX_TAX_CALC_T TEMP,
JAI_CMN_TAXES_ALL COD
WHERE LINK_TO_CUST_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID
AND TEMP.TAX_ID = COD.TAX_ID;
SELECT EXISTING_TYPE_ID, NEW_TYPE_ID, MAPPING_ID
FROM JAI_AR_TRX_SUP_INV_T
WHERE MAPPING_TYPE = 'T'
AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
INSERT INTO JAI_AR_TRX_TAX_CALC_T(
TAX_LINE_NO,
CUSTOMER_TRX_LINE_ID,
LINK_TO_CUST_TRX_LINE_ID,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6,
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
TAX_ID,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMOUNT,
INVOICE_CLASS,
FUNC_TAX_AMOUNT,
BASE_TAX_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) SELECT
TAX_LINE_NO,
CUSTOMER_TRX_LINE_ID,
LINK_TO_CUST_TRX_LINE_ID,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6,
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
TAX_ID,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMOUNT,
INVOICE_CLASS,
FUNC_TAX_AMOUNT,
BASE_TAX_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
FROM JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
UPDATE JAI_AR_TRX_TAX_CALC_T
SET TAX_ID = CTAX.NEW_TYPE_ID,
TAX_RATE = (SELECT TAX_RATE FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = CTAX.NEW_TYPE_ID),
UOM = (SELECT UOM FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = CTAX.NEW_TYPE_ID)
WHERE TAX_ID = CTAX.EXISTING_TYPE_ID;
jai_ar_sup_trx_pkg.calculate_tax('OE_LINES_UPDATE',ATAX_CATEGORY_ID,I.CUSTOMER_TRX_LINE_ID,
pack_assess ,PASSING_AMOUNT,NVL(FETCH_INV.CONVERSION_RATE,1),
I.Inventory_item_id,Aquantity, AUNIT_CODE,NULL,
NULL, NULL, SYSDATE,fnd_global.user_id,fnd_global.user_id );
SELECT 1 FROM JAI_AR_SUP_LINES
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID
AND SUP_INV_TYPE = STATUS;
/*bug#7567647 Replaced v_cust_trx_line_no_s with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2*/
INSERT INTO JAI_AR_SUP_LINES(
CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, LINE_NUMBER, SUP_INV_TYPE,
INVENTORY_ITEM_ID, DESCRIPTION, QUANTITY, UNIT_CODE,
TAX_CATEGORY_ID, OLD_ASSESSABLE_VALUE, NEW_ASSESSABLE_VALUE, AL_MAPPING_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY,interface_line_attribute2
) VALUES (
FETCH_INV.CUSTOMER_TRX_ID, I.CUSTOMER_TRX_LINE_ID, I.LINE_NUMBER, STATUS,
I.INVENTORY_ITEM_ID, I.DESCRIPTION, AQUANTITY, AUNIT_CODE,
ATAX_CATEGORY_ID, O_A_VAL, N_A_VAL, v_a_mapping_id,
SYSDATE, fnd_global.user_id, NULL, SYSDATE, fnd_global.user_id,I.CUSTOMER_TRX_LINE_ID
);
UPDATE JAI_AR_SUP_LINES
SET OLD_ASSESSABLE_VALUE = O_A_VAL,
NEW_ASSESSABLE_VALUE = N_A_VAL,
AL_MAPPING_ID = v_a_mapping_id
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID
AND SUP_INV_TYPE = STATUS;
INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE,
CREATED_BY, SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS,
SYSDATE, fnd_global.user_id, NULL, SYSDATE,
fnd_global.user_id, I.SET_OF_BOOKS_ID, I.ORG_ID, I.BILL_TO_SITE_USE_ID, FETCH_INV.BATCH_ID, 'N', 'N'
);--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
INSERT INTO JAI_AR_SUP_TAXES(
TAX_LINE_NO, CUSTOMER_TRX_LINE_ID, LINK_TO_CUST_TRX_LINE_ID, SUP_INV_TYPE,
OLD_TAX_ID, OLD_RATE, OLD_UOM, OLD_QTY_RATE,
NEW_TAX_ID, NEW_RATE, NEW_UOM, NEW_QTY_RATE,
MAPPING_ID,
PRECEDENCE_1, PRECEDENCE_2, PRECEDENCE_3, PRECEDENCE_4, PRECEDENCE_5,
PRECEDENCE_6, PRECEDENCE_7, PRECEDENCE_8, PRECEDENCE_9, PRECEDENCE_10,
EXIST_TAX_AMT, NEW_TAX_AMT,
OLD_BASE_TAX_AMT, NEW_BASE_TAX_AMT, OLD_FUNC_TAX_AMT, NEW_FUNC_TAX_AMT,
DIFF_AMT, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY
) VALUES (
A.TAX_LINE_NO, JAI_AR_SUP_TAXES_S.NEXTVAL, A.LINK_TO_CUST_TRX_LINE_ID, STATUS,
A.TAX_ID, A.TAX_RATE, A.UOM, A.QTY_RATE,
B.TAX_ID, B.TAX_RATE, B.UOM, B.QTY_RATE,
T_MAPPING_ID,
B.PRECEDENCE_1, B.PRECEDENCE_2, B.PRECEDENCE_3, B.PRECEDENCE_4, B.PRECEDENCE_5,
B.PRECEDENCE_6, B.PRECEDENCE_7, B.PRECEDENCE_8, B.PRECEDENCE_9, B.PRECEDENCE_10,
A.TAX_AMOUNT, B.TAX_AMOUNT,
A.BASE_TAX_AMOUNT, B.BASE_TAX_AMOUNT, A.FUNC_TAX_AMOUNT, B.FUNC_TAX_AMOUNT,
TAX_DIFF_AMT, SYSDATE, fnd_global.user_id,
NULL, SYSDATE, fnd_global.user_id
);
SELECT TAX_TYPE INTO OLD_TAX_TYPE FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = A.TAX_ID;
SELECT TAX_TYPE INTO NEW_TAX_TYPE FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = B.TAX_ID;
UPDATE JAI_AR_SUP_LINES
SET EXIST_EXCISE_AMT = NVL(EXIST_EXCISE_AMT,0) + A.TAX_AMOUNT
WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
AND SUP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_HDRS_ALL
SET EXIST_EXCISE_AMT = NVL(EXIST_EXCISE_AMT,0)+A.TAX_AMOUNT
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND SUPP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_LINES
SET EXIST_OTHER_AMT = NVL(EXIST_OTHER_AMT,0) + A.TAX_AMOUNT
WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
AND SUP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_HDRS_ALL
SET EXIST_OTHER_AMT = NVL(EXIST_OTHER_AMT,0)+A.TAX_AMOUNT
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND SUPP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_LINES
SET NEW_EXCISE_AMT = NVL(NEW_EXCISE_AMT,0) + B.TAX_AMOUNT
WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
AND SUP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_HDRS_ALL
SET NEW_EXCISE_AMT = NVL(NEW_EXCISE_AMT,0)+B.TAX_AMOUNT
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND SUPP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_LINES
SET NEW_OTHER_AMT = NVL(NEW_OTHER_AMT,0) + B.TAX_AMOUNT
WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
AND SUP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_HDRS_ALL
SET NEW_OTHER_AMT = NVL(NEW_OTHER_AMT,0)+B.TAX_AMOUNT
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
AND SUPP_INV_TYPE = STATUS;
UPDATE JAI_AR_SUP_LINES
SET EXCISE_DIFF_AMT = NVL(NEW_EXCISE_AMT,0) - NVL(EXIST_EXCISE_AMT,0),
OTHER_DIFF_AMT = NVL(NEW_OTHER_AMT,0) - NVL(EXIST_OTHER_AMT,0)
WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID;
UPDATE JAI_AR_SUP_HDRS_ALL
SET EXCISE_DIFF_AMT = NVL(NEW_EXCISE_AMT,0) - NVL(EXIST_EXCISE_AMT,0),
OTHER_DIFF_AMT = NVL(NEW_OTHER_AMT,0) - NVL(EXIST_OTHER_AMT,0)
WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
DELETE JAI_AR_TRX_SUP_INV_T;
DELETE JAI_AR_SUP_INV_T;
DELETE JAI_AR_TRX_TAX_CALC_T;
SELECT A.customer_trx_id,
A.chosen_for_process,
A.exist_net_sales_amt,
A.new_net_sales_amt,
A.set_of_books_id,
A.org_id,
B.bill_to_site_use_id,
B.bill_to_customer_id,
A.supp_inv_type,
B.invoice_currency_code,
B.primary_salesrep_id,
B.term_id,
C.organization_id
FROM JAI_AR_SUP_HDRS_ALL A,
RA_CUSTOMER_TRX_ALL B,
JAI_AR_TRXS C
where A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND A.chosen_for_process = 'Y'
AND A.SUPPLEMENTARY_NUM IS NULL;
select distinct set_of_books_id,
customer_id,
customer_site,
supp_inv_type,
org_id
from JAI_AR_SUP_HDRS_ALL;
select distinct invoice_currency_code,
primary_salesrep_id,
term_id
from ra_customer_trx_all
where set_of_books_id = id
and bill_to_customer_id = cid
and bill_to_site_use_id = sid;
select sum(nvl(a.new_line_amt,0) - nvl(a.exist_line_amt,0)) AMT,
B.SET_OF_BOOKS_ID,
B.CUSTOMER_SITE,
B.CUSTOMER_ID,
B.supp_INV_TYPE ,
INVENTORY_ITEM_ID,
sum(QUANTITY) quant,
a.description,
a.unit_code, --2001/04/10 Anuradha Parthasarathy
c.organization_id, --2001/05/10 Anuradha Parthasarathy,
a.interface_line_attribute2
from JAI_AR_SUP_HDRS_ALL b,
JAI_AR_SUP_LINES a,
JAI_AR_TRXS c
where a.customer_trx_id = b.customer_trx_id
and a.customer_trx_id = c.customer_trx_id
and a.sup_inv_type = b.supp_inv_type
and b.chosen_for_process = 'Y'
and b.set_of_books_id = sob_id
and b.customer_id = cust_id
and b.customer_site = cust_site
and b.supp_inv_type = inv_type
and b.supp_inv_raised_ind = 'N'
and b.SUPPLEMENTARY_NUM is null
and b.supp_inv_date is null
group by b.set_of_books_id,
b.customer_site ,
b.customer_id,
a.inventory_item_id,
b.supp_inv_type,
a.description,
a.unit_code,
c.organization_id --Added by Nagaraj.s for Bug#3110461
,a.interface_line_attribute2
; --2001/04/10 Anuradha Parthasarathy
select sum (C.REVENUE_AMOUNT_SPLIT) AMT,
B.SET_OF_BOOKS_ID,
B.CUSTOMER_SITE,
B.CUSTOMER_ID,
B.supp_INV_TYPE ,
a.INVENTORY_ITEM_ID,
a.interface_line_attribute2
from JAI_AR_SUP_HDRS_ALL b,
JAI_AR_SUP_LINES a,
RA_CUST_TRX_LINE_SALESREPS_ALL C
where a.customer_trx_id = b.customer_trx_id
and a.sup_inv_type = b.supp_inv_type
and c.customer_trx_line_id = a.customer_Trx_line_id
and b.set_of_books_id = sob_id
and b.customer_site = cid
and b.customer_site = sid
and a.inventory_item_id = item_id
and a.sup_inv_type = sup_inv_type
group by b.set_of_books_id,
b.customer_site ,
b.customer_id,
a.inventory_item_id,
b.supp_inv_type,
a.description;
select sum (C.REVENUE_AMOUNT_SPLIT) REVENUE_AMT,
c.salesrep_id,
B.SET_OF_BOOKS_ID,
B.CUSTOMER_SITE,
B.CUSTOMER_ID,
B.supp_INV_TYPE ,
INVENTORY_ITEM_ID,
D.SALES_CREDIT_TYPE_ID
from JAI_AR_SUP_HDRS_ALL b,
JAI_AR_SUP_LINES a,
RA_CUST_TRX_LINE_SALESREPS_ALL C,
ra_salesreps_all d
where a.customer_trx_id = b.customer_trx_id
and a.sup_inv_type = b.supp_inv_type
and c.customer_trx_line_id = a.customer_Trx_line_id
and c.salesrep_id = d.salesrep_id
and b.set_of_books_id = sob_id
and b.customer_id = cid
and b.customer_site = sid
and b.supp_inv_type = sup_inv
and a.inventory_item_id = invnt_item_id
group by b.set_of_books_id,
b.customer_site ,
b.customer_id,
a.inventory_item_id,
b.supp_inv_type,
a.description,
c.salesrep_iD,
D.SALES_CREDIT_TYPE_ID;
select cust_acct_site_id address_id
from hz_cust_site_uses_all
where site_use_id = site_id;
SELECT K.customer_trx_line_id,
K. OLD_SELLING_PRICE,
K.NEW_SELLING_PRICE,
K.quantity,
K.inventory_item_id,
K.description,
k.pl_mapping_id,
k.exist_line_amt,
k.new_line_amt,
k.unit_code, --2001/04/10 Anuradha Parthasarathy
k.interface_line_attribute2 --4499078
FROM JAI_AR_SUP_LINES K
WHERE K.CUSTOMER_TRX_ID = INV
AND K.SUP_INV_TYPE = TYPE;
SELECT DISTINCT A.salesrep_id,
A.revenue_percent_split,
C.sales_credit_type_id,
A.cust_trx_line_salesrep_id
FROM RA_CUST_TRX_LINE_SALESREPS_ALL A,
JAI_AR_SUP_LINES B,
RA_SALESREPS_ALL C
WHERE A.CUSTOMER_TRX_LINE_ID =INVLNO
AND C.salesrep_id = A.salesrep_id;
SELECT conversion_rate
FROM JAI_AR_SUP_CHANGES_ALL
where mapping_id = id;
SELECT name
FROM ra_terms
WHERE term_id = id;
SELECT XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU( cp_org_id )
from dual;
Uom_Code inserted into Ra_Interface_Lines_All to ensure
that Standard Lines used in Auto Accounting is supported
2 2001/05/10 Anuradha Parthasarathy
Warehouse id added to make auto accounting to be organization specific.
3 2003/08/25 Nagaraj.s For Bug#3110461 Version : 616.1
Organization_id Added in the Group by Clause of cursor get_inv_cnsldt.
16/04/2007 Kunkumar for bugno 5989740 version 115.6.6107.2
Forward porting to R12
--------------------------------------------------------------------------------------------*/
IF CHSN_FOR_CNSLDT = 'N' OR CHSN_FOR_CNSLDT IS NULL THEN
FOR SUPP_INV IN GET_SUPP_INV
LOOP
if supp_inv.supp_inv_type = 'CR' then
--select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
INSERT INTO RA_INTERFACE_LINES_ALL
(amount,
inventory_item_id,
description,
uom_code, --2001/04/10 Anuradha Parthasarathy
orig_system_bill_customer_id,
orig_system_bill_address_id,
set_of_books_id,
trx_date,
trx_number,
batch_source_name,
cust_trx_type_name,
line_type,
conversion_rate,
conversion_type,
interface_line_context,
interface_line_attribute3,
interface_line_attribute2,
currency_code,
primary_salesrep_id,
tax_code,
term_id,
term_name,
interface_line_attribute1,
interface_line_attribute4, -- Added by Brathod, bug 4392001
INTERFACE_LINE_ATTRIBUTE10, --2001/05/10 Anuradha Parthasarathy
warehouse_id, --2001/05/10 Anuradha Parthasarathy
quantity,
unit_selling_price,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
legal_entity_id , -- added by ssumaith - for LE - Bug# 4448789
org_id -- added by ssumaith - for LE - Bug# 4448789
)
values (v_amount,
supp_inv_line.inventory_item_id,
SUPP_INV_LINE.DESCRIPTION,
supp_inv_line.unit_code, --2001/04/10 Anuradha Parthasarathy
supp_inv.bill_to_customer_id,
v_address,
supp_inv.set_of_books_id,
null, -- sysdate,
v_seq,
decode(supp_inv.supp_inv_type,'DB','Supplement Debit Memo','CR','Supplement Credit Memo',
'SI','Supplement Invoice'),
decode(supp_inv.supp_inv_type,'DB','Supplement DM','CR','Supplement CM',
'SI','Supplement Invoice'),
'LINE',
NVL(v_conversion_rate,1),
'User',
/* Commented by Brathod for Bug# 4392001
-decode(supp_inv.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
'SI','SUPPLEMENT INVOICE'), -- Context
*/
'INDIA INVOICES', -- Added by brathod, Bug 4392001
supp_inv_line.customer_trx_line_id, -- attribute3
supp_inv_line.interface_line_attribute2, -- attribute2
supp_inv.invoice_currency_code,
supp_inv.primary_salesrep_id,
'Localization',
decode(supp_inv.supp_inv_type,'DB',supp_inv.term_id,'SI',supp_inv.term_id,null),
decode(supp_inv.supp_inv_type,'CR',NULL,v_term),
decode(supp_inv.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
'SI','SUPPLEMENT INVOICE'), -- supp_inv.customer_trx_id ,Brathod, Bug# 4392001
supp_inv.customer_trx_id, -- Added by Brathod for Bug# 4392001
supp_inv.organization_id,
supp_inv.organization_id,
supp_inv_line.quantity,
decode(v_amount,0,0,v_sp_diff),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
null ,
ln_legal_entity_id ,
SUPP_INV.org_id
);
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
(sales_credit_percent_split,
salesrep_id,
/* Commented By Brathod, For Bug# 4392001
-- interface_line_context,
-- interface_line_attribute1,
-- interface_line_attribute2,
-- interface_line_attribute3,
End of Bug# 4392001 */
sales_credit_type_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
values (sales_crdt.revenue_percent_split,
sales_crdt.salesrep_id,
/* Commented By Brathod, For Bug# 4392001
decode(supp_inv.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
'SI','SUPPLEMENT INVOICE'),
supp_inv.customer_trx_id,
supp_inv_line.interface_line_attribute2,
supp_inv_line.customer_trx_line_id,
End of Bug# 4392001 */
sales_crdt.sales_credit_type_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate);
UPDATE JAI_AR_SUP_HDRS_ALL
SET SUPPLEMENTARY_NUM = V_SEQ,
SUPP_INV_DATE = SYSDATE,
SUPP_INV_RAISED_IND = 'Y',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id
WHERE CUSTOMER_TRX_ID = SUPP_INV.CUSTOMER_TRX_ID
AND SUPP_INV_TYPE = SUPP_INV.SUPP_INV_TYPE
AND CHOSEN_FOR_PROCESS = 'Y';
select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
INSERT INTO RA_INTERFACE_LINES_ALL
(amount,
inventory_item_id,
description,
uom_code, --2001/04/10 Anuradha Parthasarathy
orig_system_bill_customer_id,
orig_system_bill_address_id,
set_of_books_id,
trx_date,
trx_number,
batch_source_name,
cust_trx_type_name,
line_type,
conversion_rate,
conversion_type,
interface_line_context,
interface_line_attribute3,
interface_line_attribute2,
currency_code,
primary_salesrep_id,
tax_code,
term_id,
term_name,
interface_line_attribute1,
interface_line_attribute4, -- Added by brathod, Bug 4392001
INTERFACE_LINE_ATTRIBUTE10, --2001/05/10 Anuradha Parthasarathy
warehouse_id, --2001/05/10 Anuradha Parthasarathy
quantity,
unit_selling_price,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id , /* added by ssumaith - LE - Bug # 4448789 */
legal_entity_id /* added by ssumaith - LE - Bug # 4448789 */
)
values (inv_cnsldt.AMT,
inv_cnsldt.inventory_item_id,
inv_cnsldt.DESCRIPTION,
inv_cnsldt.unit_code, --2001/04/10 Anuradha Parthasarathy
inv_cnsldt.customer_id,
v_address,
inv_cnsldt.set_of_books_id,
null, -- sysdate,
v_seq,
decode(inv_cnsldt.supp_inv_type,'DB','Supplement Debit Memo',
'CR','Supplement Credit Memo','SI','Supplement Invoice'),
decode(inv_cnsldt.supp_inv_type,'DB','Supplement DM','CR','Supplement CM',
'SI','Supplement Invoice'),
lv_line_type, --'LINE',
v_conversion_rate, --NVL(v_conversion_rate,1),
lv_user, --'User',
/* Commented By Brathod, Bug 4392001
decode(inv_cnsldt.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
'SI','SUPPLEMENT INVOICE'),
End of Bug# 4392001 */
lv_india_inv, --'INDIA INVOICES', -- Added by brathod, Bug 4392001
NULL,
inv_cnsldt.interface_line_attribute2 ,
v_invoice_curr_code ,
v_sales_rep_id ,
lv_loc, --'Localization',
decode(inv_cnsldt.supp_inv_type,'DB',v_term_id,'SI', v_term_id,null),
decode(inv_cnsldt.supp_inv_type,'CR',NULL,v_term),
/* Added By Brathod for Bug# 4392001 */
decode(inv_cnsldt.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
'SI','SUPPLEMENT INVOICE'), -- attribute1
null, -- attribute4
inv_cnsldt.organization_id, --2001/05/10 Anuradha Parthasarathy
inv_cnsldt.organization_id, --2001/05/10 Anuradha Parthasarathy
inv_cnsldt.quant,
( inv_cnsldt.AMT/inv_cnsldt.quant),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
null,
cnsldt_header.org_id,
ln_legal_entity_id
);
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
(sales_credit_percent_split,
salesrep_id,
/* Commented By Brathod for Bug# 4392001
interface_line_context,
interface_line_attribute1,
interface_line_attribute2,
interface_line_attribute3,
End Of Bug# 4392001*/
sales_credit_type_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
values ((cnsldt_crdt_amt.AMT/cnsldt_crdt_rep.REVENUE_AMT)*100,
cnsldt_crdt_rep.salesrep_id,
/* Commented By Brathod for Bug# 4392001
decode(cnsldt_crdt_AMT.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
'SI','SUPPLEMENT INVOICE'),
NULL,
cnlsdt_crdt_amt.interface_line_attribute2,
NULL,
End of Bug# 4392001*/
cnsldt_crdt_rep.sales_credit_type_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate);
UPDATE JAI_AR_SUP_HDRS_ALL
SET SUPPLEMENTARY_NUM = V_SEQ,
SUPP_INV_DATE = SYSDATE,
SUPP_INV_RAISED_IND = 'Y',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id
WHERE SET_OF_BOOKS_ID = cnsldt_header.set_of_books_id
AND CUSTOMER_ID = cnsldt_header.customer_id
AND CUSTOMER_SITE = cnsldt_header.customer_site
AND supp_inv_type = cnsldt_header.supp_inv_type
AND CHOSEN_FOR_PROCESS = 'Y'
AND SUPPLEMENTARY_NUM IS NULL
AND SUPP_INV_DATE IS NULL
AND SUPP_INV_RAISED_IND = 'N';
p_last_update_date DATE ,
p_last_updated_by NUMBER ,
p_last_update_login NUMBER
)
IS
TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
SELECT
c.tax_line_no lno,
c.tax_id,
c.tax_rate,
c.qty_rate,
c.uom uom_code,
c.func_tax_amount,
c.base_tax_amount,
c.precedence_1 p_1,
c.precedence_2 p_2,
c.precedence_3 p_3,
c.precedence_4 p_4,
c.precedence_5 p_5,
c.precedence_6 p_6,
c.precedence_7 p_7,
c.precedence_8 p_8,
c.precedence_9 p_9,
c.precedence_10 p_10,
c.tax_amount,
d.tax_type,
d.end_date valid_date,
decode( upper(d.tax_type),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
-- Delete by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
-----------------------------------------------------------------------
--jai_constants.tax_type_exc_edu_cess,1,
--jai_constants.tax_type_sh_exc_edu_cess,1,--Added higher education cess by kundan kumar for bug#5907436 'TDS', 2,
-----------------------------------------------------------------------
-- Delete by Jia Li for Tax Inclusive Computations on 2007/12/07, End
-- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
-----------------------------------------------------------------------
'TDS' , 2,
'EXCISE_EDUCATION_CESS', 6,
'CVD_EDUCATION_CESS' , 6,
'EXCISE_SH_EDU_CESS' , 6,
-----------------------------------------------------------------------
-- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
0
) tax_type_val,
d.inclusive_tax_flag -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
FROM
JAI_AR_TRX_TAX_CALC_T c,
JAI_CMN_TAXES_ALL d
WHERE
c.link_to_cust_trx_line_id = p_line_id AND
c.tax_id = d.tax_id
ORDER BY
c.tax_line_no;
SELECT
A.uom_class
FROM
mtl_units_of_measure A,
mtl_units_of_measure B
WHERE
A.uom_code = p_line_uom_code AND
B.uom_code = p_tax_line_uom_code AND
A.uom_class = B.uom_class;
IF transaction_name = 'OE_LINES_UPDATE' THEN
UPDATE JAI_AR_TRX_TAX_CALC_T
SET
tax_amount = nvl(tax_amt_tab(rec.lno),0) ,
base_tax_amount = decode(nvl(base_tax_amt_tab(rec.lno),0) ,
0 ,
nvl(tax_amt_tab(rec.lno),0) ,
nvl(base_tax_amt_tab(rec.lno),0)
) ,
func_tax_amount = nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor ,
last_update_date = p_last_update_date ,
last_updated_by = p_last_updated_by ,
last_update_login = p_last_update_login
WHERE
link_to_cust_trx_line_id = p_line_id AND
tax_line_no = rec.lno;