[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
oe_debug_pub.add( 'OEXVIMSB->BEFORE SELECT QP_LIST_HEADERS_TL FOR DIS' ) ;
SELECT LIST_HEADER_ID
INTO l_list_header_id
FROM qp_list_headers_tl
WHERE NAME = p_modifier_name
AND LANGUAGE = userenv('LANG')
AND nvl(VERSION_NO, FND_API.G_MISS_CHAR)= nvl(p_version_no, FND_API.G_MISS_CHAR);
oe_debug_pub.add( 'OEXVIMSB->BEFORE SELECT QP_LIST_LINE FOR DIS' ) ;
SELECT LIST_LINE_ID
INTO l_list_line_id
FROM qp_list_lines
WHERE LIST_HEADER_ID = l_list_header_id
AND LIST_LINE_NO = p_list_line_no
AND LIST_LINE_TYPE_CODE = p_list_line_type_code ;
oe_debug_pub.add( 'OEXVIMSB->BEFORE SELECT QP_LIST_HEADER_TL FOR PROMOLINE' ) ;
SELECT LIST_HEADER_ID
INTO l_list_header_id
FROM qp_list_headers_tl
WHERE NAME = p_modifier_name
AND LANGUAGE = userenv('LANG')
AND nvl(VERSION_NO,FND_API.G_MISS_CHAR) = nvl(p_version_no,FND_API.G_MISS_CHAR);
oe_debug_pub.add( 'OEXVIMSB->BEFORE SELECT QP_LIST_LINE FOR PROMOLINE' ) ;
SELECT LIST_LINE_ID
INTO l_list_line_id
FROM qp_list_lines
WHERE LIST_HEADER_ID = l_list_header_id
AND LIST_LINE_NO = p_list_line_no
AND LIST_LINE_TYPE_CODE ='PROMOLINE';
SELECT LIST_HEADER_ID
INTO l_list_header_id
FROM qp_list_headers_vl
WHERE name =p_modifier_name
AND ask_for_flag = 'Y';
SELECT coupon_id
INTO l_list_line_id
FROM qp_coupons
WHERE coupon_number =p_modifier_name;
(p_x_header_rec.OPERATION<>OE_GLOBALS.G_OPR_UPDATE))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CANCELLATION IS NOT ALLOWED WHILE CREATING AN ORDER... ' ) ;
SELECT tracking_quantity_ind,
secondary_uom_code,
secondary_default_ind
FROM mtl_system_items
WHERE organization_id = discrete_org_id
AND inventory_item_id = discrete_item_id;
IF p_x_header_rec.operation = OE_Globals.G_OPR_UPDATE THEN
-----------------------------------------------------------
-- Validate change sequence
-----------------------------------------------------------
IF p_x_header_rec.force_apply_flag <> 'Y' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'VALIDATING CHANGE SEQUENCE' ) ;
SELECT change_sequence
INTO l_c_change_sequence
FROM oe_order_headers
WHERE order_source_id = p_x_header_rec.order_source_id
AND orig_sys_document_ref = p_x_header_rec.orig_sys_document_ref
AND decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_header_rec.sold_to_org_id, FND_API.G_MISS_NUM), 1)
FOR UPDATE; --added so that changes cannot be commited out of sequence when run in multiple sessions
END IF; -- If header operation code is update
oe_debug_pub.add( 'INSERTING RECORD IN ACTIONS TABLE' ) ;
AND p_x_header_rec.operation IN ('INSERT','CREATE','UPDATE','DELETE')
THEN
BEGIN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE DERIVING REQ HEADER ID FOR INTERNAL ORDERS' ) ;
-- Following select is removed because of the po tables are
-- changing to multi-org, and it has been decided that PO
-- will pass ids columns instead of reference as they are unique
-- in _all tables and reference column can be derived uniquely
-- but not the vice-versa
-- SELECT requisition_header_id
-- INTO p_header_rec.source_document_id
-- FROM po_requisition_headers
-- WHERE segment1 = p_header_rec.orig_sys_document_ref;
SELECT segment1
INTO p_x_header_rec.orig_sys_document_ref
FROM po_requisition_headers_all
WHERE requisition_header_id = p_x_header_rec.source_document_id;
IF p_x_header_rec.operation IN ('INSERT','CREATE')
THEN --{
Begin
-- Start for the fix of bug 1794206
IF (p_x_header_rec.order_source_id = OE_GLOBALS.G_ORDER_SOURCE_INTERNAL)
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE VALIDATING INTERNAL ORDER FOR INSERT OPR' ) ;
SELECT 1 into l_count
FROM oe_order_headers
WHERE order_source_id = p_x_header_rec.order_source_id
AND orig_sys_document_ref = p_x_header_rec.orig_sys_document_ref
AND decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_header_rec.sold_to_org_id, FND_API.G_MISS_NUM), 1)
AND source_document_id = p_x_header_rec.source_document_id
AND rownum < 2;
oe_debug_pub.add( 'AFTER VALIDATING INTERNAL ORDER FOR INSERT OPR' ) ;
oe_debug_pub.add( 'BEFORE VALIDATING EXTERNAL ORDER FOR INSERT OPR' ) ;
SELECT 1 into l_count
FROM oe_order_headers
WHERE order_source_id = p_x_header_rec.order_source_id
AND orig_sys_document_ref = p_x_header_rec.orig_sys_document_ref
AND decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_header_rec.sold_to_org_id, FND_API.G_MISS_NUM), 1)
AND rownum < 2;
oe_debug_pub.add( 'AFTER VALIDATING EXTERNAL ORDER FOR INSERT OPR' ) ;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW ORDER WITH THE SAME ORDER SOURCE ID AND ORIG_SYS_DOCUMENT_REF... ' ) ;
oe_debug_pub.add( 'VALID ORDER FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO INSERT NEW ORDER... ' ) ;
ELSIF p_x_header_rec.operation IN ('UPDATE','DELETE')
THEN
Begin
--change sequence is not used in the WHERE-clause here because we don't want
--to restrict new order to have the same change sequence as old order
SELECT header_id, order_number, change_sequence
INTO l_header_id, l_order_number, l_c_change_sequence
FROM oe_order_headers
WHERE order_source_id = p_x_header_rec.order_source_id
AND orig_sys_document_ref = p_x_header_rec.orig_sys_document_ref
AND (sold_to_org_id is NULL OR
decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_header_rec.sold_to_org_id, FND_API.G_MISS_NUM), 1));
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING ORDER BUT THAT ORDER DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING ORDER ... ' ) ;
ELSE --IF p_x_header_rec.operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID OPERATION CODE. NOT ONE OF INSERT , CREATE , UPDATE OR DELETE... ' ) ;
/* bsadri fill in the IDs for actions if this is an update */
BEGIN
IF p_x_header_rec.operation IN ('UPDATE','DELETE') THEN
FOR b in 1..p_x_action_request_tbl.COUNT
LOOP--{
IF p_x_action_request_tbl(b).entity_code = OE_Globals.G_ENTITY_HEADER
THEN
p_x_action_request_tbl(b).entity_id := p_x_header_rec.header_id;
IF p_x_header_adj_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_header_rec.operation IN ('INSERT','CREATE') AND
p_x_header_adj_tbl(I).operation NOT IN ('INSERT','CREATE'))OR
(p_x_header_rec.operation IN ('UPDATE') AND
p_x_header_adj_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')) OR
(p_x_header_rec.operation IN ('DELETE') AND
p_x_header_adj_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID HEADER ADJUSTMENTS OPERATION CODE...' ) ;
IF p_x_header_adj_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'NEW ADJUSTMENT FOR THE EXISITNG HEARDER_ID:' || P_X_HEADER_REC.HEADER_ID ) ;
SELECT 1 into l_count
FROM oe_price_adjustments
WHERE header_id = p_x_header_rec.header_id
AND line_id IS NULL
AND orig_sys_discount_ref =
p_x_header_adj_tbl(I).orig_sys_discount_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW HDRADJ WITH THE SAME HEADER_ID AND ORIG_SYS_DISCOUNT_REF... ' ) ;
oe_debug_pub.add( 'VALID HEADER LEVEL PRICE ADJ FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW HDR PRICE ADJ... ' ) ;
End If; -- Insert, Create operation
IF p_x_header_adj_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'HEADER ID: '||TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT price_adjustment_id
INTO l_price_adjustment_id
FROM oe_price_adjustments
WHERE header_id = p_x_header_rec.header_id
AND line_id IS NULL
AND orig_sys_discount_ref =
p_x_header_adj_tbl(I).orig_sys_discount_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING HDR ADJ BUT THAT DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING HDRADJ ... ' ) ;
End IF; -- Update and Delete operation
p_x_header_adj_tbl.delete (I);
p_x_header_adj_val_tbl.DELETE (I);
p_x_header_adj_tbl.delete (I);
p_x_header_adj_val_tbl.DELETE (I);
p_x_header_adj_tbl.delete (I);
p_x_header_adj_val_tbl.DELETE (I);
IF p_x_header_price_att_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_header_rec.operation IN ('INSERT','CREATE') AND
p_x_header_price_att_tbl(I).operation NOT IN ('INSERT','CREATE'))OR
(p_x_header_rec.operation IN ('UPDATE') AND
p_x_header_price_att_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')) OR
(p_x_header_rec.operation IN ('DELETE') AND
p_x_header_price_att_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID HEADER ATTRIBUTE OPERATION CODE...' ) ;
IF p_x_header_price_att_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'NEW ATT FOR THE EXISITNG HEARDER_ID:' || P_X_HEADER_REC.HEADER_ID ) ;
SELECT 1 into l_count
FROM oe_order_price_attribs
WHERE header_id = p_x_header_rec.header_id
AND line_id IS NULL
AND orig_sys_atts_ref =
p_x_header_price_att_tbl(I).orig_sys_atts_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW HDRATT WITH THE SAME HEADER_ID AND ATTRIBUTE ID....' ) ;
oe_debug_pub.add( 'VALID HEADER LEVEL PRICE ATT FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW HDR PRICE ATT... ' ) ;
End If; -- Insert, Create operation
IF p_x_header_price_att_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'HEADER ID: '||TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT order_price_attrib_id
INTO l_price_attrib_id
FROM oe_order_price_attribs
WHERE header_id = p_x_header_rec.header_id
AND line_id IS NULL
AND orig_sys_atts_ref =
p_x_header_price_att_tbl(I).orig_sys_atts_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING HDR ATT BUT THAT DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING HDRATT ... ' ) ;
End IF; -- Update and Delete operation
IF p_x_header_scredit_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_header_rec.operation IN ('INSERT','CREATE') AND
p_x_header_scredit_tbl(I).operation NOT IN ('INSERT','CREATE'))OR
(p_x_header_rec.operation IN ('UPDATE') AND
p_x_header_scredit_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE')) OR
(p_x_header_rec.operation IN ('DELETE') AND
p_x_header_scredit_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID HEADER SALES CREDITS OPERATION CODE...' ) ;
IF p_x_header_scredit_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
THEN
Begin
p_x_header_scredit_tbl(I).header_id := l_header_id;
SELECT 1 into l_count
FROM oe_sales_credits
WHERE header_id = p_x_header_rec.header_id
AND line_id IS NULL
AND orig_sys_credit_ref =
p_x_header_scredit_tbl(I).orig_sys_credit_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW HDRCREDIT WITH THE SAME HEADER_ID AND ORIG_SYS_CREDIT_REF... ' ) ;
oe_debug_pub.add( 'VALID HEADER LEVEL SALES CREDIT FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW HDR SALES CREDIT... ' ) ;
END IF; -- Insert, Create Operation
IF p_x_header_scredit_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
THEN
Begin
p_x_header_scredit_tbl(I).header_id := l_header_id;
SELECT sales_credit_id
INTO l_sales_credit_id
FROM oe_sales_credits
WHERE header_id = l_header_id
AND line_id IS NULL
AND orig_sys_credit_ref =
p_x_header_scredit_tbl(I).orig_sys_credit_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING ORDER BUT THAT ORDER DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING HEADER LEVEL SCREDIT ... ' ) ;
End IF; -- Update and Delete operation
IF p_x_header_payment_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_header_rec.operation IN ('INSERT','CREATE') AND
p_x_header_payment_tbl(I).operation NOT IN ('INSERT','CREATE'))OR
(p_x_header_rec.operation IN ('UPDATE') AND
p_x_header_payment_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE')) OR
(p_x_header_rec.operation IN ('DELETE') AND
p_x_header_payment_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID HEADER PAYMENT OPERATION CODE...' ) ;
IF p_x_header_payment_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
THEN
Begin
p_x_header_payment_tbl(I).header_id := l_header_id;
SELECT 1 into l_count
FROM oe_payments
WHERE header_id = p_x_header_rec.header_id
AND line_id IS NULL
AND orig_sys_payment_ref =
p_x_header_payment_tbl(I).orig_sys_payment_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW HDR PAYMENT WITH THE SAME HEADER_ID AND ORIG_SYS_PAYMENT_REF... ' ) ;
oe_debug_pub.add( 'VALID HEADER LEVEL PAYMENT FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW HDR PAYMENT... ' ) ;
END IF; -- Insert, Create Operation
IF p_x_header_payment_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
THEN
Begin
p_x_header_payment_tbl(I).header_id := l_header_id;
SELECT header_id
INTO l_header_id
FROM oe_payments
WHERE header_id = l_header_id
AND line_id IS NULL
AND orig_sys_payment_ref =
p_x_header_payment_tbl(I).orig_sys_payment_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING ORDER BUT THAT ORDER DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING HEADER LEVEL payment ... ' ) ;
End IF; -- Update and Delete operation
Select ordered_quantity
Into l_existing_qty
From Oe_Order_Lines
Where orig_sys_document_ref = p_x_line_tbl(I).orig_sys_document_ref
And order_source_id = p_x_line_tbl(I).order_source_id
And orig_sys_line_ref = p_x_line_tbl(I).orig_sys_line_ref
And orig_sys_shipment_ref = p_x_line_tbl(I).orig_sys_shipment_ref
And decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_line_tbl(I).sold_to_org_id, FND_API.G_MISS_NUM), 1);
IF (p_x_header_rec.operation IN ('INSERT','CREATE') AND
p_x_line_tbl(I).operation NOT IN ('INSERT','CREATE'))OR
(p_x_header_rec.operation IN ('UPDATE') AND
p_x_line_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE'))OR
(p_x_header_rec.operation IN ('DELETE') AND
p_x_line_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID OPERATION CODE. YOU ARE TRYING TO INSERT THE ORDER HEADER BUT NOT THE LINES. IF THE OPERATION ON THE HEADER IS INSERT , IT SHOULD BE INSERT AT THE LINE LEVEL ALSO , NOT UPDATE OR DELETE...' ) ;
AND p_x_line_tbl(I).operation IN ('INSERT','CREATE','UPDATE','DELETE')
THEN
BEGIN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE DERIVING REQ LINE ID FOR INTERNAL ORDERS' ) ;
-- SELECT requisition_line_id
-- INTO p_line_tbl(I).source_document_line_id
-- FROM po_requisition_lines
-- WHERE requisition_header_id = p_header_rec.source_document_id
-- AND line_num = p_line_tbl(I).orig_sys_line_ref;
SELECT line_num, destination_organization_id
INTO p_x_line_tbl(I).orig_sys_line_ref, l_po_dest_org_id
FROM po_requisition_lines_all
WHERE requisition_header_id = p_x_header_rec.source_document_id
AND requisition_line_id = p_x_line_tbl(I).source_document_line_id;
SELECT intransit_time * -1
INTO l_intransit_time
FROM mtl_interorg_ship_methods
WHERE from_organization_id = p_x_line_tbl(I).ship_from_org_id
AND to_organization_id = l_po_dest_org_id
AND nvl(default_flag,1) = 1;
IF p_x_line_tbl(I).operation IN ('INSERT','CREATE')
-- aksingh(10/11/2000) this is in process of being coded
AND p_x_header_rec.operation = 'UPDATE'
THEN
p_x_line_tbl(I).header_id := l_header_id;
SELECT 1 into l_count
FROM oe_order_lines
WHERE header_id = l_header_id
AND orig_sys_line_ref = p_x_line_tbl(I).orig_sys_line_ref
AND orig_sys_shipment_ref = p_x_line_tbl(I).orig_sys_shipment_ref
AND decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_line_tbl(I).sold_to_org_id, FND_API.G_MISS_NUM), 1);
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW LINE WITH THE SAME ORDER SOURCE ID , ORIG_SYS_DOCUMENT_REF AND ORIG_SYS_LINE_REF... ' ) ;
oe_debug_pub.add( 'VALID ORDER LINE FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO INSERT A NEW LINE... ' ) ;
SELECT 1 into l_count
FROM oe_order_lines
WHERE header_id = l_header_id
AND orig_sys_line_ref = p_x_line_tbl(I).orig_sys_line_ref
AND decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_line_tbl(I).sold_to_org_id, FND_API.G_MISS_NUM), 1);
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW LINE WITH THE SAME ORDER SOURCE ID , ORIG_SYS_DOCUMENT_REF AND ORIG_SYS_LINE_REF. .. ' ) ;
oe_debug_pub.add( 'VALID ORDER LINE FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO INSERT A NEW LINE... ' ) ;
SELECT 1 into l_count
FROM oe_order_lines
WHERE header_id = l_header_id
AND orig_sys_line_ref = p_x_line_tbl(I).orig_sys_line_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW LINE WITH THE SAME ORDER SOURCE ID , ORIG_SYS_DOCUMENT_REF AND ORIG_SYS_LINE_REF. .. ' ) ;
oe_debug_pub.add( 'VALID ORDER LINE FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO INSERT A NEW LINE... ' ) ;
ELSIF p_x_line_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'L_HEADER_ID: ' || l_header_id) ;
SELECT line_id, line_number, shipment_number,
option_number, change_sequence, unit_selling_price
INTO l_line_id, l_line_number, l_shipment_number,
l_option_number, l_c_change_sequence, l_cho_unit_selling_price
FROM oe_order_lines
WHERE header_id = l_header_id
AND orig_sys_line_ref = p_x_line_tbl(I).orig_sys_line_ref
AND orig_sys_shipment_ref = p_x_line_tbl(I).orig_sys_shipment_ref
AND (sold_to_org_id is NULL OR
decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_line_tbl(I).sold_to_org_id, FND_API.G_MISS_NUM), 1));
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING LINE BUT THAT LINE DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING LINE ... ' ) ;
SELECT line_id, line_number, shipment_number,
option_number, change_sequence, unit_selling_price
INTO l_line_id, l_line_number, l_shipment_number,
l_option_number, l_c_change_sequence, l_cho_unit_selling_price
FROM oe_order_lines
WHERE header_id = l_header_id
AND orig_sys_line_ref = p_x_line_tbl(I).orig_sys_line_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING LINE BUT THAT LINE DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING LINE ... ' ) ;
ELSIF p_x_line_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID OPERATION CODE. NOT ONE OF INSERT , CREATE , UPDATE OR DELETE... ' ) ;
SELECT inventory_item_id
INTO l_inventory_item_id_int
FROM mtl_system_items_vl
WHERE concatenated_segments = p_x_line_val_tbl(I).inventory_item
AND customer_order_enabled_flag = 'Y'
AND bom_item_type in (1,2,4)
AND organization_id =
oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',l_org_id);
SELECT inventory_item_id
INTO l_inventory_item_id_ord
FROM mtl_system_items_vl
WHERE concatenated_segments = p_x_line_tbl(I).ordered_item
AND customer_order_enabled_flag = 'Y'
AND bom_item_type in (1,2,4)
AND organization_id =
oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',l_org_id);
SELECT customer_item_number
INTO p_x_line_tbl(I).ordered_item
FROM mtl_customer_items
WHERE customer_item_id = p_x_line_tbl(I).ordered_item_id
AND inactive_flag = 'N';
-- 6 Lines are deleted from here, check the previous version for details.
IF p_x_line_tbl(I).ordered_item_id = FND_API.G_MISS_NUM THEN
l_ordered_item_id := NULL;
SELECT /* MOAC_SQL_CHANGE */ u.cust_acct_site_id,
s.cust_account_id
INTO l_address_id
,l_cust_id
FROM HZ_CUST_SITE_USES_ALL u, --moac
HZ_CUST_ACCT_SITES s
WHERE u.cust_acct_site_id = s.cust_acct_site_id
AND u.org_id = s.org_id
AND u.site_use_id = p_x_line_tbl(I).ship_to_org_id
AND u.site_use_code = 'SHIP_TO';
SELECT inventory_item_id
INTO l_inventory_item_id_gen
FROM mtl_cross_references
WHERE cross_reference_type = p_x_line_tbl(I).item_identifier_type
AND (organization_id =
OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID',l_org_id)
OR organization_id IS NULL)
AND cross_reference = p_x_line_tbl(I).ordered_item
And (inventory_item_id = l_inventory_item_id_int
OR l_inventory_item_id_int IS NULL);
and p_x_line_tbl(I).operation in ('CREATE','INSERT') --added for bug 5509598
and (nvl(p_x_line_tbl(I).split_from_line_ref,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) THEN --added for bug 5531063
p_return_status := FND_API.G_RET_STS_ERROR;
Check Pricing_Qunatity and Pricing_Quantity_Uom and update
-----------------------------------------------------------
*/
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE VALIDATING PRICING QUNATITY AND UOM' ) ;
SELECT line_id
INTO p_x_line_tbl(I).service_reference_line_id
FROM oe_order_lines ol
WHERE ol.header_id = l_header_id
AND ol.orig_sys_line_ref =
p_x_line_tbl(I).service_reference_line
AND decode(l_customer_key_profile, 'Y',
nvl(ol.sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_line_tbl(I).sold_to_org_id, FND_API.G_MISS_NUM), 1);
SELECT line_id
INTO p_x_line_tbl(I).service_reference_line_id
FROM oe_order_lines ol
WHERE ol.order_source_id =
p_x_header_rec.order_source_id
AND ol.orig_sys_document_ref =
p_x_line_tbl(I).service_reference_order
AND ol.orig_sys_line_ref =
p_x_line_tbl(I).service_reference_line
AND decode(l_customer_key_profile, 'Y',
nvl(ol.sold_to_org_id, FND_API.G_MISS_NUM), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_x_line_tbl(I).sold_to_org_id, FND_API.G_MISS_NUM), 1);
/* bsadri fill in the IDs for actions if this is an update */
BEGIN
IF p_x_line_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE') THEN
-- The following condition is added for #1927259
if (p_x_action_request_tbl.COUNT >0 ) then
FOR b in l_counter..p_x_action_request_tbl.COUNT
LOOP--{
l_counter_memory := l_counter_memory + 1;
IF p_x_line_adj_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_line_tbl(p_x_line_adj_tbl(I).line_index).operation
IN ('INSERT','CREATE') AND
p_x_line_adj_tbl(I).operation NOT IN ('INSERT','CREATE')) OR
(p_x_line_tbl(p_x_line_adj_tbl(I).line_index).operation
IN ('UPDATE') AND
p_x_line_adj_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')) OR
(p_x_line_tbl(p_x_line_adj_tbl(I).line_index).operation
IN ('DELETE') AND
p_x_line_adj_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID LINE ADJUSTMENTS OPERATION CODE...' ) ;
IF p_x_line_adj_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
AND p_x_line_tbl(p_x_line_adj_tbl(I).line_index).operation = 'UPDATE'
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'NEW ADJUSTMENT FOR LINE LEVEL , THE EXISITNG HEARDER_ID:' || TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT 1 into l_count
FROM oe_price_adjustments
WHERE header_id = p_x_header_rec.header_id
AND line_id = l_line_id
AND orig_sys_discount_ref =
p_x_line_adj_tbl(I).orig_sys_discount_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW LNADJ WITH THE SAME HEADER_ID , LINE_ID AND ORIG_SYS_DISCOUNT_REF... ' ) ;
oe_debug_pub.add( 'VALID LINE LEVEL PRICE ADJ FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW LINE PRICE ADJ... ' ) ;
End IF; -- Insert, Create Operation
IF p_x_line_adj_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
AND p_x_line_tbl(p_x_line_adj_tbl(I).line_index).operation IN ('UPDATE','DELETE')
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'HEADER ID: '||TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT price_adjustment_id
INTO l_price_adjustment_id
FROM oe_price_adjustments
WHERE header_id = l_header_id
AND line_id = p_x_line_adj_tbl(I).line_id
AND orig_sys_discount_ref =
p_x_line_adj_tbl(I).orig_sys_discount_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING LINE ADJ BUT THAT DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING LINEADJ ... ' ) ;
End IF; -- Update and Delete operation
p_x_line_adj_tbl.delete (I);
p_x_line_adj_val_tbl.DELETE (I);
p_x_line_adj_tbl.delete (I);
p_x_line_adj_val_tbl.DELETE (I);
p_x_line_adj_tbl.delete (I);
p_x_line_adj_val_tbl.DELETE (I);
IF p_x_line_price_att_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_line_tbl(p_x_line_price_att_tbl(I).line_index).operation
IN ('INSERT','CREATE') AND
p_x_line_price_att_tbl(I).operation NOT IN ('INSERT','CREATE')) OR
(p_x_line_tbl(p_x_line_price_att_tbl(I).line_index).operation
IN ('UPDATE') AND
p_x_line_price_att_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')) OR
(p_x_line_tbl(p_x_line_price_att_tbl(I).line_index).operation
IN ('DELETE') AND
p_x_line_price_att_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID LINE ADJUSTMENTS OPERATION CODE...' ) ;
IF p_x_line_price_att_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
AND p_x_line_tbl(p_x_line_price_att_tbl(I).line_index).operation = 'UPDATE'
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'NEW ATT FOR THE LINE LEVEL , EXISITNG HEARDER_ID:' || P_X_HEADER_REC.HEADER_ID ) ;
SELECT 1 into l_count
FROM oe_order_price_attribs
WHERE header_id = p_x_header_rec.header_id
AND line_id = l_line_id
AND orig_sys_atts_ref =
p_x_line_price_att_tbl(I).orig_sys_atts_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW HDRATT WITH THE SAME HEADER_ID AND ATTRIBUTE ID....' ) ;
oe_debug_pub.add( 'VALID LINE LEVEL PRICE ATT FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW HDR PRICE ATT... ' ) ;
End If; -- Insert, Create operation
IF p_x_line_price_att_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
AND p_x_line_tbl(p_x_line_price_att_tbl(I).line_index).operation IN ('UPDATE','DELETE')
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'HEADER ID: '||TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT order_price_attrib_id
INTO l_price_attrib_id
FROM oe_order_price_attribs
WHERE header_id = l_header_id
AND line_id = l_line_id
AND orig_sys_atts_ref =
p_x_line_price_att_tbl(I).orig_sys_atts_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING HDR ATT BUT THAT DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING HDRATT ... ' ) ;
End IF; -- Update and Delete operation
IF p_x_line_scredit_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_line_tbl(p_x_line_scredit_tbl(I).line_index).operation
IN ('INSERT','CREATE') AND
p_x_line_scredit_tbl(I).operation NOT IN ('INSERT','CREATE')) OR
(p_x_line_tbl(p_x_line_scredit_tbl(I).line_index).operation
IN ('UPDATE') AND
p_x_line_scredit_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')) OR
(p_x_line_tbl(p_x_line_scredit_tbl(I).line_index).operation
IN ('DELETE') AND
p_x_line_scredit_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID LINE SALES CREDITS OPERATION CODE...' ) ;
IF p_x_line_scredit_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
AND p_x_line_tbl(p_x_line_scredit_tbl(I).line_index).operation
= 'UPDATE'
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'NEW ADJUSTMENT FOR LINE LEVEL , THE EXISITNG HEARDER_ID:' || TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT 1 into l_count
FROM oe_sales_credits
WHERE header_id = p_x_header_rec.header_id
AND line_id = l_line_id
AND orig_sys_credit_ref =
p_x_line_scredit_tbl(I).orig_sys_credit_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW LNSCREDIT WITH THE SAME HEADER_ID , LINE_ID AND ORIG_SYS_CREDIT_REF... ' ) ;
oe_debug_pub.add( 'VALID LINE LEVEL SALES CREDIT FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW LINE SALES CREDIT... ' ) ;
END IF; -- Insert, Update Operation
IF p_x_line_scredit_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
AND p_x_line_tbl(p_x_line_scredit_tbl(I).line_index).operation IN ('UPDATE','DELETE')
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'HEADER ID: '||TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT sales_credit_id
INTO l_sales_credit_id
FROM oe_sales_credits
WHERE header_id = p_x_header_rec.header_id
AND line_id = l_line_id
AND orig_sys_credit_ref =
p_x_line_scredit_tbl(I).orig_sys_credit_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING LINE CREDIT BUT THAT DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING LINECRDT ... ' ) ;
End IF; -- Update and Delete operation
IF p_x_line_payment_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_line_tbl(p_x_line_payment_tbl(I).line_index).operation
IN ('INSERT','CREATE') AND
p_x_line_payment_tbl(I).operation NOT IN ('INSERT','CREATE')) OR
(p_x_line_tbl(p_x_line_payment_tbl(I).line_index).operation
IN ('UPDATE') AND
p_x_line_payment_tbl(I).operation NOT IN ('INSERT','CREATE','UPDATE','DELETE')) OR
(p_x_line_tbl(p_x_line_payment_tbl(I).line_index).operation
IN ('DELETE') AND
p_x_line_payment_tbl(I).operation NOT IN ('DELETE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID LINE PAYMENTS OPERATION CODE...' ) ;
IF p_x_line_payment_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_header_rec.operation = 'UPDATE'
AND p_x_line_tbl(p_x_line_payment_tbl(I).line_index).operation
= 'UPDATE'
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'NEW PAYMENT FOR LINE LEVEL , THE EXISITNG HEARDER_ID:' || TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT 1 into l_count
FROM oe_payments
WHERE header_id = p_x_header_rec.header_id
AND line_id = l_line_id
AND orig_sys_payment_ref =
p_x_line_payment_tbl(I).orig_sys_payment_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW LINE PAYMENT WITH THE SAME HEADER_ID , LINE_ID AND ORIG_SYS_PAYMENT_REF... ' ) ;
oe_debug_pub.add( 'VALID LINE LEVEL PAYMENT FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW LINE PAYMENT... ' ) ;
END IF; -- Insert, Update Operation
IF p_x_line_payment_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_header_rec.operation IN ('UPDATE','DELETE')
AND p_x_line_tbl(p_x_line_payment_tbl(I).line_index).operation IN ('UPDATE','DELETE')
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'HEADER ID: '||TO_CHAR ( P_X_HEADER_REC.HEADER_ID ) ) ;
SELECT 1
INTO l_count
FROM oe_payments
WHERE header_id = p_x_header_rec.header_id
AND line_id = l_line_id
AND orig_sys_payment_ref =
p_x_line_payment_tbl(I).orig_sys_payment_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING LINE PAYMENT BUT THAT DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING PAYMENT ... ' ) ;
End IF; -- Update and Delete operation
IF p_x_lot_serial_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE') OR
(p_x_line_tbl(p_x_lot_serial_tbl(I).line_index).operation
IN ('INSERT','CREATE') AND
p_x_lot_serial_tbl(I).operation NOT IN ('INSERT','CREATE'))
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID LINE LOT SERIALS OPERATION CODE...' ) ;
IF p_x_lot_serial_tbl(I).operation IN ('INSERT', 'CREATE')
AND p_x_line_tbl(p_x_lot_serial_tbl(I).line_index).operation = 'UPDATE'
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'NEW LOT FOR LINE LEVEL , THE EXISITNG LINE_ID:' || L_LINE_ID ) ;
SELECT 1 into l_count
FROM oe_lot_serial_numbers
WHERE line_id = l_line_id
AND orig_sys_lotserial_ref =
p_x_lot_serial_tbl(I).orig_sys_lotserial_ref
AND rownum < 2;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO INSERT A NEW LOT WITH THE SAME LINE_ID AND ORIG_SYS_DISCOUNT_REF... ' ) ;
oe_debug_pub.add( 'VALID LINE LEVEL LOT FOR INSERT' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN INSERTING NEW LINE LOT... ' ) ;
END IF; -- Insert, Create Opearation
IF p_x_lot_serial_tbl(I).operation IN ('UPDATE','DELETE')
AND p_x_line_tbl(p_x_lot_serial_tbl(I).line_index).operation IN ('UPDATE','DELETE')
THEN
Begin
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
SELECT lot_serial_id
INTO l_lot_serial_id
FROM oe_lot_serial_numbers
WHERE line_id = l_line_id
AND orig_sys_lotserial_ref =
p_x_lot_serial_tbl(I).orig_sys_lotserial_ref;
oe_debug_pub.add( 'INVALID OPERATION CODE. TRYING TO UPDATE OR DELETE AN EXISTING LOT BUT THAT DOES NOT EXIST... ' ) ;
oe_debug_pub.add( 'OTHERS EXCEPTION WHEN TRYING TO UPDATE OR DELETE AN EXISTING LOT... ' ) ;
End IF; -- Update and Delete operation
IF p_x_reservation_tbl(I).operation NOT IN ('INSERT','CREATE',
'UPDATE','DELETE')
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INVALID RESERVATION OPERATION... ' ) ;
select unit_selling_price into l_unit_selling_price
from oe_order_lines
where line_id = p_x_line_tbl(I).line_id;