[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_History_Records
(p_header_rec IN OUT NOCOPY OE_Blanket_PUB.Header_Rec_Type
,p_line_tbl IN OUT NOCOPY OE_Blanket_PUB.Line_Tbl_Type
,p_version_flag in varchar2 := null
,p_phase_change_flag in varchar2 := null
,x_return_status IN OUT NOCOPY VARCHAR2
);
Select 'VALID'
Into l_dummy
From oe_ship_to_orgs_v
Where customer_id = p_sold_to_org_id
AND site_use_id = p_ship_to_org_id
AND status = 'A'
AND address_status ='A'; --bug 2752321
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
Into l_dummy
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE SITE.SITE_USE_ID = p_ship_to_org_id
AND SITE.SITE_USE_CODE = l_site_use_code
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE.STATUS = 'A'
AND ACCT_SITE.STATUS ='A' AND --bug 2752321
ACCT_SITE.CUST_ACCOUNT_ID in (
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE_ALL h WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and h.org_id=acct_site.org_id
and ship_to_flag = 'Y' and status = 'A')
AND EXISTS(SELECT 1 FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID =
ACCT_SITE.CUST_ACCOUNT_ID AND STATUS='A')
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM OE_SHIP_TO_ORGS_V SHP
WHERE SHP.ORGANIZATION_ID =p_ship_to_org_id
AND SHP.STATUS = 'A'
AND SHP.ADDRESS_STATUS ='A' --bug 2752321
AND SYSDATE BETWEEN NVL(SHP.START_DATE_ACTIVE, SYSDATE)
AND NVL(SHP.END_DATE_ACTIVE, SYSDATE);
SELECT 'VALID'
INTO l_dummy
FROM oe_deliver_to_orgs_v
WHERE customer_id = p_sold_to_org_id
AND site_use_id = p_deliver_to_org_id
AND status = 'A'
AND address_status ='A';--bug 2752321
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
Into l_dummy
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE SITE.SITE_USE_ID = p_deliver_to_org_id
AND SITE.SITE_USE_CODE = l_site_use_code
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE.STATUS = 'A'
AND ACCT_SITE.STATUS ='A' AND
ACCT_SITE.CUST_ACCOUNT_ID in (
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE_ALL h WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and h.org_id=acct_site.org_id
and ship_to_flag = 'Y' and status='A')
AND EXISTS(SELECT 1 FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID =
ACCT_SITE.CUST_ACCOUNT_ID AND STATUS='A')
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM OE_DELIVER_TO_ORGS_V DEL
WHERE DEL.ORGANIZATION_ID =p_deliver_to_org_id
AND DEL.STATUS = 'A'
AND DEL.ADDRESS_STATUS ='A' --bug 2752321
AND SYSDATE BETWEEN NVL(DEL.START_DATE_ACTIVE, SYSDATE)
AND NVL(DEL.END_DATE_ACTIVE, SYSDATE);
Select 'VALID'
Into l_dummy
From oe_invoice_to_orgs_v
Where customer_id = p_sold_to_org_id
And site_use_id = p_invoice_to_org_id
and status='A'
and address_status ='A';
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
Into l_dummy
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE SITE.SITE_USE_ID = p_invoice_to_org_id
AND SITE.SITE_USE_CODE = l_site_use_code
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE.STATUS = 'A'
AND ACCT_SITE.STATUS ='A' AND
ACCT_SITE.CUST_ACCOUNT_ID in (
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE_ALL h WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and h.org_id=acct_site.org_id
and bill_to_flag = 'Y' and status='A')
AND EXISTS(SELECT 1 FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID AND STATUS='A')
AND ROWNUM = 1;
oe_delayed_requests_pvt.g_delayed_requests.delete(I);
gpr_pricing_attr_tbl.delete;
Update oe_blanket_headers
set lock_control = lock_control + 1
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
where header_id = lheader_id ;
Update oe_blanket_headers_ext
set new_price_list_id = ppr_price_list_rec.list_header_id
where order_number = (select /* MOAC_SQL_CHANGE */ order_number
from oe_blanket_headers_all
where header_id = lheader_id);
Update oe_blanket_headers
set price_list_id = ppr_price_list_rec.list_header_id
where header_id = lheader_id;
Update oe_blanket_headers
set --qp_list_header_id = ppr_price_list_rec.list_header_id,
price_list_id = ppr_price_list_rec.list_header_id ,
lock_control = lock_control + 1
where header_id = lheader_id ;
Update oe_blanket_lines
set
price_list_id = ppr_price_list_rec.list_header_id,
lock_control = lock_control + 1
WHERE
line_id = g_line_id_tbl(K).line_id ;
UPDATE oe_blanket_lines_ext
SET
qp_list_line_id = ppr_price_list_line_tbl(K).list_line_id
WHERE
line_id = g_line_id_tbl(K).line_id ;
oe_delayed_requests_pvt.g_delayed_requests.delete(I);
gpr_pricing_attr_tbl.delete;
Update oe_blanket_lines
set
price_list_id = ppr_price_list_line_tbl(1).list_header_id,
lock_control = lock_control + 1
WHERE
line_id =
oe_delayed_requests_pvt.g_delayed_requests(I).entity_id ;
Update oe_blanket_lines_ext
set qp_list_line_id = ppr_price_list_line_tbl(1).list_line_id
WHERE
line_id =
oe_delayed_requests_pvt.g_delayed_requests(I).entity_id ;
gpr_price_list_line_tbl(K).operation := QP_GLOBALS.G_OPR_UPDATE;
Update oe_blanket_lines_ext
set qp_list_line_id = Null
WHERE line_id = oe_delayed_requests_pvt.g_delayed_requests(p_req_ind).entity_id ;
SELECT 'VALID' into l_dummy
FROM oe_transaction_types_vl
WHERE SALES_DOCUMENT_TYPE_CODE = 'B'
AND transaction_type_id = p_x_header_rec.order_type_id
AND trunc(sysdate) between start_date_Active and
nvl(end_date_active,trunc(sysdate));
( p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
(p_x_header_rec.attribute1 IS NOT NULL AND
( p_x_header_rec.attribute1 <>
p_old_header_rec.attribute1 OR
p_old_header_rec.attribute1 IS NULL ))
OR (p_x_header_rec.attribute10 IS NOT NULL AND
( p_x_header_rec.attribute10 <>
p_old_header_rec.attribute10 OR
p_old_header_rec.attribute10 IS NULL ))
OR (p_x_header_rec.attribute11 IS NOT NULL AND
( p_x_header_rec.attribute11 <>
p_old_header_rec.attribute11 OR
p_old_header_rec.attribute11 IS NULL ))
OR (p_x_header_rec.attribute12 IS NOT NULL AND
( p_x_header_rec.attribute12 <>
p_old_header_rec.attribute12 OR
p_old_header_rec.attribute12 IS NULL ))
OR (p_x_header_rec.attribute13 IS NOT NULL AND
( p_x_header_rec.attribute13 <>
p_old_header_rec.attribute13 OR
p_old_header_rec.attribute13 IS NULL ))
OR (p_x_header_rec.attribute14 IS NOT NULL AND
( p_x_header_rec.attribute14 <>
p_old_header_rec.attribute14 OR
p_old_header_rec.attribute14 IS NULL ))
OR (p_x_header_rec.attribute15 IS NOT NULL AND
( p_x_header_rec.attribute15 <>
p_old_header_rec.attribute15 OR
p_old_header_rec.attribute15 IS NULL ))
OR (p_x_header_rec.attribute16 IS NOT NULL AND --For bug 2184255
( p_x_header_rec.attribute16 <>
p_old_header_rec.attribute16 OR
p_old_header_rec.attribute16 IS NULL ))
OR (p_x_header_rec.attribute17 IS NOT NULL AND
( p_x_header_rec.attribute17 <>
p_old_header_rec.attribute17 OR
p_old_header_rec.attribute17 IS NULL ))
OR (p_x_header_rec.attribute18 IS NOT NULL AND
( p_x_header_rec.attribute18 <>
p_old_header_rec.attribute18 OR
p_old_header_rec.attribute18 IS NULL ))
OR (p_x_header_rec.attribute19 IS NOT NULL AND
( p_x_header_rec.attribute19 <>
p_old_header_rec.attribute19 OR
p_old_header_rec.attribute19 IS NULL ))
OR (p_x_header_rec.attribute2 IS NOT NULL AND
( p_x_header_rec.attribute2 <>
p_old_header_rec.attribute2 OR
p_old_header_rec.attribute2 IS NULL ))
OR (p_x_header_rec.attribute20 IS NOT NULL AND -- for bug 2184255
( p_x_header_rec.attribute20 <>
p_old_header_rec.attribute20 OR
p_old_header_rec.attribute20 IS NULL ))
OR (p_x_header_rec.attribute3 IS NOT NULL AND
( p_x_header_rec.attribute3 <>
p_old_header_rec.attribute3 OR
p_old_header_rec.attribute3 IS NULL ))
OR (p_x_header_rec.attribute4 IS NOT NULL AND
( p_x_header_rec.attribute4 <>
p_old_header_rec.attribute4 OR
p_old_header_rec.attribute4 IS NULL ))
OR (p_x_header_rec.attribute5 IS NOT NULL AND
( p_x_header_rec.attribute5 <>
p_old_header_rec.attribute5 OR
p_old_header_rec.attribute5 IS NULL ))
OR (p_x_header_rec.attribute6 IS NOT NULL AND
( p_x_header_rec.attribute6 <>
p_old_header_rec.attribute6 OR
p_old_header_rec.attribute6 IS NULL ))
OR (p_x_header_rec.attribute7 IS NOT NULL AND
( p_x_header_rec.attribute7 <>
p_old_header_rec.attribute7 OR
p_old_header_rec.attribute7 IS NULL ))
OR (p_x_header_rec.attribute8 IS NOT NULL AND
( p_x_header_rec.attribute8 <>
p_old_header_rec.attribute8 OR
p_old_header_rec.attribute8 IS NULL ))
OR (p_x_header_rec.attribute9 IS NOT NULL AND
( p_x_header_rec.attribute9 <>
p_old_header_rec.attribute9 OR
p_old_header_rec.attribute9 IS NULL ))
OR (p_x_header_rec.context IS NOT NULL AND
( p_x_header_rec.context <>
p_old_header_rec.context OR
p_old_header_rec.context IS NULL )))
THEN
oe_debug_pub.add('Before calling header_desc_flex',2);
SELECT context_required_flag, default_context_field_name
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE (application_id = 660)
AND (descriptive_flexfield_name = l_flex_name);
(p_x_line_rec.operation = oe_globals.g_opr_update AND
(p_x_line_rec.attribute1 IS NOT NULL AND
( p_x_line_rec.attribute1 <>
p_old_line_rec.attribute1 OR
p_old_line_rec.attribute1 IS NULL ))
OR (p_x_line_rec.attribute10 IS NOT NULL AND
( p_x_line_rec.attribute10 <>
p_old_line_rec.attribute10 OR
p_old_line_rec.attribute10 IS NULL ))
OR (p_x_line_rec.attribute11 IS NOT NULL AND
( p_x_line_rec.attribute11 <>
p_old_line_rec.attribute11 OR
p_old_line_rec.attribute11 IS NULL ))
OR (p_x_line_rec.attribute12 IS NOT NULL AND
( p_x_line_rec.attribute12 <>
p_old_line_rec.attribute12 OR
p_old_line_rec.attribute12 IS NULL ))
OR (p_x_line_rec.attribute13 IS NOT NULL AND
( p_x_line_rec.attribute13 <>
p_old_line_rec.attribute13 OR
p_old_line_rec.attribute13 IS NULL ))
OR (p_x_line_rec.attribute14 IS NOT NULL AND
( p_x_line_rec.attribute14 <>
p_old_line_rec.attribute14 OR
p_old_line_rec.attribute14 IS NULL ))
OR (p_x_line_rec.attribute15 IS NOT NULL AND
( p_x_line_rec.attribute15 <>
p_old_line_rec.attribute15 OR
p_old_line_rec.attribute15 IS NULL ))
OR (p_x_line_rec.attribute16 IS NOT NULL AND -- For bug 2184255
( p_x_line_rec.attribute16 <>
p_old_line_rec.attribute16 OR
p_old_line_rec.attribute16 IS NULL ))
OR (p_x_line_rec.attribute17 IS NOT NULL AND
( p_x_line_rec.attribute17 <>
p_old_line_rec.attribute17 OR
p_old_line_rec.attribute17 IS NULL ))
OR (p_x_line_rec.attribute18 IS NOT NULL AND
( p_x_line_rec.attribute18 <>
p_old_line_rec.attribute18 OR
p_old_line_rec.attribute18 IS NULL ))
OR (p_x_line_rec.attribute19 IS NOT NULL AND
( p_x_line_rec.attribute19 <>
p_old_line_rec.attribute19 OR
p_old_line_rec.attribute19 IS NULL ))
OR (p_x_line_rec.attribute2 IS NOT NULL AND
( p_x_line_rec.attribute2 <>
p_old_line_rec.attribute2 OR
p_old_line_rec.attribute2 IS NULL ))
OR (p_x_line_rec.attribute20 IS NOT NULL AND
( p_x_line_rec.attribute20 <>
p_old_line_rec.attribute20 OR
p_old_line_rec.attribute20 IS NULL ))
OR (p_x_line_rec.attribute3 IS NOT NULL AND
( p_x_line_rec.attribute3 <>
p_old_line_rec.attribute3 OR
p_old_line_rec.attribute3 IS NULL ))
OR (p_x_line_rec.attribute4 IS NOT NULL AND
( p_x_line_rec.attribute4 <>
p_old_line_rec.attribute4 OR
p_old_line_rec.attribute4 IS NULL ))
OR (p_x_line_rec.attribute5 IS NOT NULL AND
( p_x_line_rec.attribute5 <>
p_old_line_rec.attribute5 OR
p_old_line_rec.attribute5 IS NULL ))
OR (p_x_line_rec.attribute6 IS NOT NULL AND
( p_x_line_rec.attribute6 <>
p_old_line_rec.attribute6 OR
p_old_line_rec.attribute6 IS NULL ))
OR (p_x_line_rec.attribute7 IS NOT NULL AND
( p_x_line_rec.attribute7 <>
p_old_line_rec.attribute7 OR
p_old_line_rec.attribute7 IS NULL ))
OR (p_x_line_rec.attribute8 IS NOT NULL AND
( p_x_line_rec.attribute8 <>
p_old_line_rec.attribute8 OR
p_old_line_rec.attribute8 IS NULL ))
OR (p_x_line_rec.attribute9 IS NOT NULL AND
( p_x_line_rec.attribute9 <>
p_old_line_rec.attribute9 OR
p_old_line_rec.attribute9 IS NULL ))
OR (p_x_line_rec.context IS NOT NULL AND
( p_x_line_rec.context <>
p_old_line_rec.context OR
p_old_line_rec.context IS NULL )))
THEN
oe_debug_pub.add('Before calling line_desc_flex',2);
SELECT DECODE(items.org_independent_flag, 'Y', 1,
DECODE(items.organization_id, item_val_org, 1, 2))
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.organization_id = item_val_org
AND sitems.inventory_item_id = p_inventory_item_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item
ORDER BY 1;
SELECT 'valid'
INTO l_dummy
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')
AND customer_order_enabled_flag = 'Y';
SELECT 'valid'
INTO l_dummy
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id =
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND citems.inactive_flag = 'N'
AND cxref.inactive_flag = 'N';
SELECT 'VALID'
INTO l_dummy
FROM MTL_CATEGORIES_VL
WHERE CATEGORY_ID = p_inventory_item_id
AND ENABLED_FLAG = 'Y';
/* SELECT replaced for with the following for 2219230 */
if l_debug_level > 0 then
oe_debug_pub.add('Validating generic item, item_val_org:'||to_char(item_val_org),5);
SELECT 'VALID'
INTO l_dummy
FROM MTL_CATEGORIES_VL
WHERE CATEGORY_ID = p_inventory_item_id
AND ENABLED_FLAG = 'Y';
select service_item_flag,bom_item_type
into l_service_item_flag, l_bom_item_type
from MTL_SYSTEM_ITEMS
where inventory_item_id = p_inventory_item_id
AND organization_id = l_item_org;
SELECT type
INTO l_rule_type
FROM ra_rules
WHERE rule_id = p_accounting_rule_id;
IF p_line_rec.operation = oe_globals.g_opr_update THEN
IF l_old_line_rec.line_id is null THEN
l_old_line_rec := Query_Row(p_line_id => p_line_rec.line_id);
IF p_line_rec.operation = oe_globals.g_opr_update
THEN
If p_line_rec.last_updated_by <> FND_GLOBAL.USER_ID or
l_old_line_rec.last_updated_by <> FND_GLOBAL.USER_ID
THEN
p_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_old_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
p_line_rec.last_update_date := sysdate;
l_old_line_rec.last_update_date := sysdate;
IF (p_line_rec.operation <> OE_GLOBALS.g_opr_delete) THEN
-----------------------------------------------------------
-- Check required attributes.
-----------------------------------------------------------
if l_debug_level > 0 then
oe_debug_pub.add('Blanket In validate entity 1');
IF (p_line_rec.operation = oe_globals.g_opr_update or p_line_rec.operation = oe_globals.g_opr_create) AND
p_line_rec.ITEM_IDENTIFIER_TYPE = 'CUST' then
oe_blanket_util.Get_Inventory_Item(p_x_line_rec => p_line_rec,
x_return_status => l_return_status);
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_LINE_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_LINE_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_LINE_ATTRIBUTE');
IF p_line_rec.operation = oe_globals.g_opr_update THEN
if l_debug_level > 0 then
oe_debug_pub.add('Blanket In validate entity 4: update');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_ATTR_CLOSED');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_LINE_ATTRIBUTE');
SELECT 'ERROR'
INTO l_dummy
FROM OE_ORDER_LINES
WHERE trunc(request_date) > trunc(p_line_rec.end_date_active)
AND BLANKET_NUMBER = p_line_rec.order_number
AND BLANKET_LINE_NUMBER = p_line_rec.line_number
AND ROWNUM = 1;
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
oe_debug_pub.add('Blanket In validate entity update 1');
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
oe_debug_pub.add('Blanket In validate entity update 2');
END IF; --if g_opr_update
SELECT 'Y'
INTO l_list_line_exists
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_line_rec.qp_list_line_id;
fnd_message.set_name('ONT','OE_BLKT_CANNOT_UPDATE_DIS');
IF p_header_rec.operation = oe_globals.g_opr_update THEN
if l_old_header_rec.header_id is null THEN
Query_Header(
p_header_id => p_header_rec.header_id,
x_header_rec => l_old_header_Rec,
x_return_status => l_return_status);
IF p_header_rec.operation = oe_globals.g_opr_update
THEN
If p_header_rec.last_updated_by <> FND_GLOBAL.USER_ID or
l_old_header_rec.last_updated_by <> FND_GLOBAL.USER_ID
THEN
p_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_old_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
p_header_rec.last_update_date := sysdate;
l_old_header_rec.last_update_date := sysdate;
SELECT 'VALID'
INTO l_dummy
FROM
HZ_CUST_SITE_USES SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE
SITE.SITE_USE_ID = p_header_rec.sold_to_site_use_id
AND SITE.SITE_USE_CODE = 'SOLD_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id
AND SITE.STATUS = 'A'
AND ACCT_SITE.STATUS='A';
SELECT 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.sold_to_contact_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ROWNUM = 1;
SELECT list_header_id
INTO p_header_rec.price_list_id
FROM qp_list_headers_vl
WHERE list_type_code in ('PRL' ,'AGR')
AND p_header_rec.price_list_name = name
AND (((nvl(global_flag,'Y') = 'Y'
OR orig_org_id = mo_global.get_current_org_id)
AND qp_security.security_on = 'Y') or qp_security.security_on = 'N');
SELECT list_header_id
INTO l_list_header_id
FROM qp_list_headers_vl
WHERE name = p_header_rec.new_price_list_name
AND rownum = 1;
SELECT list_header_id
INTO l_list_header_id
FROM qp_list_headers_vl
WHERE name = p_header_rec.new_modifier_list_name
AND rownum = 1;
(p_header_rec.operation = oe_globals.g_opr_update)
THEN
IF NOT OE_GLOBALS.EQUAL(p_header_rec.end_date_active
,l_old_header_rec.end_date_active)
THEN
p_header_rec.end_date_active :=
trunc(p_header_rec.end_date_active,'DD') +
((24*60*60)-1)/(24*60*60);
IF p_header_rec.operation = oe_globals.g_opr_update THEN
--validate for updates
--commented because waiting for message
--not allowed to change any attributes on a previously closed blanket
IF p_header_rec.end_date_active < trunc(sysdate) and
l_old_header_rec.end_date_active < trunc(sysdate) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_ATTR_CLOSED');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_HDR_ATTRIBUTE');
SELECT 'ERROR'
INTO l_dummy
FROM OE_BLANKET_LINES_EXT
WHERE ORDER_NUMBER = p_header_rec.order_number
AND END_DATE_ACTIVE > p_header_rec.end_date_active
AND ROWNUM = 1;
SELECT /* MOAC_SQL_NO_CHANGE */ 'ERROR'
INTO l_dummy
FROM OE_ORDER_LINES
WHERE trunc(request_date) > trunc(p_header_rec.end_date_active)
AND BLANKET_NUMBER = p_header_rec.order_number
AND ROWNUM = 1;
SELECT 'CASCADE'
INTO l_dummy
FROM OE_BLANKET_LINES_EXT
WHERE ORDER_NUMBER = p_header_rec.order_number
AND END_DATE_ACTIVE IS NULL
AND ROWNUM = 1;
SELECT 'WARNING'
INTO l_dummy
FROM OE_BLANKET_LINES_EXT OLX, OE_BLANKET_LINES OL
WHERE OLX.LINE_ID = OL.LINE_ID
AND OLX.ORDER_NUMBER = p_header_rec.order_number
AND END_DATE_ACTIVE IS NULL
AND OVERRIDE_BLANKET_CONTROLS_FLAG = 'N'
AND (BLANKET_LINE_MIN_AMOUNT < RELEASED_AMOUNT
OR BLANKET_MIN_QUANTITY < RELEASED_QUANTITY)
AND ROWNUM = 1;
fnd_message.set_name('ONT', 'OE_BLKT_UPDATE_MAX_LIMIT');
SELECT /* MOAC_SQL_CHANGE */ version_number
INTO l_db_version_number
FROM OE_BLANKET_HEADERS_ALL
WHERE HEADER_ID = p_header_rec.header_id;
SELECT 'VALID'
INTO l_dummy
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = 'REVISION_REASON'
AND LOOKUP_CODE = p_header_rec.revision_change_reason_code;
END IF; -- if update operation
p_header_rec.operation = oe_globals.g_opr_update) THEN
OE_Delayed_Requests_PVT.Log_Request
(p_entity_code => OE_BLANKET_pub.G_ENTITY_BLANKET_HEADER
,p_Entity_Id => p_header_rec.header_id
,p_param1 => p_header_rec.sold_to_org_id
,p_requesting_entity_code =>
OE_BLANKET_pub.G_ENTITY_BLANKET_HEADER
,p_requesting_entity_id => p_header_rec.header_id
,p_request_type => 'VALIDATE_BLANKET_SOLD_TO'
,x_return_status => l_return_status);
PROCEDURE Insert_Row
(p_header_rec IN OE_Blanket_PUB.Header_rec_type
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_org_id NUMBER;
oe_debug_pub.add('Entering OE_BLANKET_UTIL.INSERT_ROW - Header ID :'||
p_header_rec.header_id);
INSERT INTO OE_BLANKET_HEADERS
( ACCOUNTING_RULE_ID
, AGREEMENT_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORDER_NUMBER
, ORDER_TYPE_ID
, PAYMENT_TERM_ID
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, SHIP_TO_ORG_ID
, SOLD_TO_CONTACT_ID
, SOLD_TO_ORG_ID
, TRANSACTIONAL_CURR_CODE
, conversion_type_code
, lock_control
, open_flag
, booked_flag
, VERSION_NUMBER
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, ORG_ID
, SALES_DOCUMENT_TYPE_CODE
, ORDER_CATEGORY_CODE
, SOURCE_DOCUMENT_TYPE_ID
, SOURCE_DOCUMENT_ID
-- hashraf start of Pack J
, SALES_DOCUMENT_NAME
, TRANSACTION_PHASE_CODE
, USER_STATUS_CODE
, flow_status_code
, SUPPLIER_SIGNATURE
, SUPPLIER_SIGNATURE_DATE
, CUSTOMER_SIGNATURE
, CUSTOMER_SIGNATURE_DATE
, SOLD_TO_SITE_USE_ID
, DRAFT_SUBMITTED_FLAG
, SOURCE_DOCUMENT_VERSION_NUMBER
) -- end of Pack J
VALUES
( p_header_rec.accounting_rule_id
, p_header_rec.agreement_id
, p_header_rec.context
, p_header_rec.attribute1
, p_header_rec.attribute10
, p_header_rec.attribute11
, p_header_rec.attribute12
, p_header_rec.attribute13
, p_header_rec.attribute14
, p_header_rec.attribute15
, p_header_rec.attribute16
, p_header_rec.attribute17
, p_header_rec.attribute18
, p_header_rec.attribute19
, p_header_rec.attribute20
, p_header_rec.attribute2
, p_header_rec.attribute3
, p_header_rec.attribute4
, p_header_rec.attribute5
, p_header_rec.attribute6
, p_header_rec.attribute7
, p_header_rec.attribute8
, p_header_rec.attribute9
, p_header_rec.created_by
, p_header_rec.creation_date
, p_header_rec.cust_po_number
, p_header_rec.deliver_to_org_id
, p_header_rec.freight_terms_code
, p_header_rec.header_id
, p_header_rec.invoice_to_org_id
, p_header_rec.invoicing_rule_id
, p_header_rec.last_updated_by
, p_header_rec.last_update_date
, p_header_rec.last_update_login
, p_header_rec.order_number
, p_header_rec.order_type_id
, p_header_rec.payment_term_id
, p_header_rec.price_list_id
, p_header_rec.program_application_id
, p_header_rec.program_id
, p_header_rec.program_update_date
, p_header_rec.request_id
, p_header_rec.salesrep_id
, p_header_rec.shipping_method_code
, p_header_rec.ship_from_org_id
, p_header_rec.ship_to_org_id
, p_header_rec.sold_to_contact_id
, p_header_rec.sold_to_org_id
, p_header_rec.transactional_curr_code
, p_header_rec.conversion_type_code
, l_lock_control
, 'Y'
, 'N'
, p_header_rec.version_number
, p_header_rec.shipping_instructions
, p_header_rec.packing_instructions
, mo_global.get_current_org_id
, 'B'
, 'ORDER'
, p_header_rec.source_document_type_id
, p_header_rec.source_document_id
, p_header_rec.SALES_DOCUMENT_NAME -- hashraf ... start of pack J
, p_header_rec.TRANSACTION_PHASE_CODE
, p_header_rec.USER_STATUS_CODE
, p_header_rec.FLOW_STATUS_CODE
, p_header_rec.SUPPLIER_SIGNATURE
, p_header_rec.SUPPLIER_SIGNATURE_DATE
, p_header_rec.CUSTOMER_SIGNATURE
, p_header_rec.CUSTOMER_SIGNATURE_DATE
, p_header_rec.sold_to_site_use_id
, p_header_rec.draft_submitted_flag
, p_header_rec.source_document_version_number
);
INSERT INTO OE_BLANKET_HEADERS_EXT
( ORDER_NUMBER
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, on_hold_flag
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, OVERRIDE_AMOUNT_FLAG
, BLANKET_MAX_AMOUNT
, BLANKET_MIN_AMOUNT
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, REVISION_CHANGE_REASON_CODE
, REVISION_CHANGE_COMMENTS
, REVISION_CHANGE_DATE
-- 11i10 Pricing Changes
, NEW_PRICE_LIST_ID
, NEW_MODIFIER_LIST_ID
, DEFAULT_DISCOUNT_PERCENT
, DEFAULT_DISCOUNT_AMOUNT
)
VALUES
( p_header_rec.order_number
, p_header_rec.START_DATE_ACTIVE
, p_header_rec.END_DATE_ACTIVE
, p_header_rec.on_hold_flag
, p_header_rec.ENFORCE_PRICE_LIST_FLAG
, p_header_rec.enforce_ship_to_flag
, p_header_rec.enforce_invoice_to_flag
, p_header_rec.enforce_freight_term_flag
, p_header_rec.enforce_shipping_method_flag
, p_header_rec.enforce_payment_term_flag
, p_header_rec.enforce_accounting_rule_flag
, p_header_rec.enforce_invoicing_rule_flag
, p_header_rec.OVERRIDE_AMOUNT_FLAG
, p_header_rec.Blanket_Max_Amount
, p_header_rec.Blanket_Min_Amount
, p_header_rec.RELEASED_AMOUNT
, p_header_rec.FULFILLED_AMOUNT
, p_header_rec.REVISION_CHANGE_REASON_CODE
, p_header_rec.REVISION_CHANGE_COMMENTS
, p_header_rec.REVISION_CHANGE_DATE
-- 11i10 Pricing Changes
, p_header_rec.NEW_PRICE_LIST_ID
, p_header_rec.NEW_MODIFIER_LIST_ID
, p_header_rec.default_DISCOUNT_PERCENT
, p_header_rec.default_DISCOUNT_AMOUNT
);
oe_debug_pub.add('Exiting OE_BLANKET_UTIL.INSERT_ROW- Header', 1);
, 'Insert_Row'
);
End Insert_Row;
PROCEDURE Update_Row
( p_header_rec IN OE_Blanket_PUB.Header_rec_type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1);
UPDATE OE_BLANKET_HEADERS
SET ACCOUNTING_RULE_ID = p_header_rec.accounting_rule_id
, AGREEMENT_ID = p_header_rec.agreement_id
, CONTEXT = p_header_rec.context
, ATTRIBUTE1 = p_header_rec.attribute1
, ATTRIBUTE10 = p_header_rec.attribute10
, ATTRIBUTE11 = p_header_rec.attribute11
, ATTRIBUTE12 = p_header_rec.attribute12
, ATTRIBUTE13 = p_header_rec.attribute13
, ATTRIBUTE14 = p_header_rec.attribute14
, ATTRIBUTE15 = p_header_rec.attribute15
, ATTRIBUTE16 = p_header_rec.attribute16
, ATTRIBUTE17 = p_header_rec.attribute17
, ATTRIBUTE18 = p_header_rec.attribute18
, ATTRIBUTE19 = p_header_rec.attribute19
, ATTRIBUTE20 = p_header_rec.attribute20
, ATTRIBUTE2 = p_header_rec.attribute2
, ATTRIBUTE3 = p_header_rec.attribute3
, ATTRIBUTE4 = p_header_rec.attribute4
, ATTRIBUTE5 = p_header_rec.attribute5
, ATTRIBUTE6 = p_header_rec.attribute6
, ATTRIBUTE7 = p_header_rec.attribute7
, ATTRIBUTE8 = p_header_rec.attribute8
, ATTRIBUTE9 = p_header_rec.attribute9
-- , CREATED_BY = p_header_rec.created_by
-- , CREATION_DATE = p_header_rec.creation_date
, CUST_PO_NUMBER = p_header_rec.cust_po_number
, DELIVER_TO_ORG_ID = p_header_rec.deliver_to_org_id
, FREIGHT_TERMS_CODE = p_header_rec.freight_terms_code
, INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
, INVOICING_RULE_ID = p_header_rec.invoicing_rule_id
, LAST_UPDATED_BY = nvl(p_header_rec.last_updated_by, FND_GLOBAL.USER_ID)
, LAST_UPDATE_DATE = nvl(p_header_rec.last_update_date, sysdate)
, LAST_UPDATE_LOGIN = p_header_rec.last_update_login
, ORDER_TYPE_ID = p_header_rec.order_type_id
, PAYMENT_TERM_ID = p_header_rec.payment_term_id
, PRICE_LIST_ID = p_header_rec.price_list_id
, PROGRAM_APPLICATION_ID = p_header_rec.program_application_id
, PROGRAM_ID = p_header_rec.program_id
, PROGRAM_UPDATE_DATE = p_header_rec.program_update_date
, REQUEST_ID = p_header_rec.request_id
, salesrep_id = p_header_rec.salesrep_id
, SHIPPING_METHOD_CODE = p_header_rec.shipping_method_code
, ship_from_org_id = p_header_rec.ship_from_org_id
, SHIP_TO_ORG_ID = p_header_rec.ship_to_org_id
, SOLD_TO_CONTACT_ID = p_header_rec.sold_to_contact_id
, SOLD_TO_ORG_ID = p_header_rec.sold_to_org_id
, TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
, CONVERSION_TYPE_CODE = p_header_rec.CONVERSION_TYPE_CODE
, LOCK_CONTROL = LOCK_CONTROL + 1
, VERSION_NUMBER = p_header_rec.version_number
, SHIPPING_INSTRUCTIONS = p_header_rec.shipping_instructions
, PACKING_INSTRUCTIONS = p_header_rec.packing_instructions
, SOURCE_DOCUMENT_TYPE_ID = p_header_rec.source_document_type_id
, SOURCE_DOCUMENT_ID = p_header_rec.source_document_id
-- hashraf ... start of pack J
, SALES_DOCUMENT_NAME = p_header_rec.SALES_DOCUMENT_NAME
, TRANSACTION_PHASE_CODE = p_header_rec.TRANSACTION_PHASE_CODE
, USER_STATUS_CODE = p_header_rec.USER_STATUS_CODE
, FLOW_STATUS_CODE = p_header_rec.FLOW_STATUS_CODE
, SUPPLIER_SIGNATURE = p_header_rec.SUPPLIER_SIGNATURE
, SUPPLIER_SIGNATURE_DATE = p_header_rec.SUPPLIER_SIGNATURE_DATE
, CUSTOMER_SIGNATURE = p_header_rec.CUSTOMER_SIGNATURE
, CUSTOMER_SIGNATURE_DATE = p_header_rec.CUSTOMER_SIGNATURE_DATE
, sold_to_site_use_id = p_header_rec.sold_to_site_use_id
, draft_submitted_flag = p_header_rec.draft_submitted_flag
, source_document_version_number = p_header_rec.source_document_version_number
-- hashraf ... end of pack J
WHERE header_id = p_header_rec.header_id;
UPDATE OE_BLANKET_HEADERS_EXT
SET
START_DATE_ACTIVE = p_header_rec.START_DATE_ACTIVE
, END_DATE_ACTIVE = p_header_rec.END_DATE_ACTIVE
, on_hold_flag = p_header_rec.on_hold_flag
, ENFORCE_PRICE_LIST_FLAG = p_header_rec.ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag = p_header_rec.enforce_ship_to_flag
, enforce_invoice_to_flag = p_header_rec.enforce_invoice_to_flag
, enforce_freight_term_flag = p_header_rec.enforce_freight_term_flag
, enforce_shipping_method_flag = p_header_rec.enforce_shipping_method_flag
, enforce_payment_term_flag = p_header_rec.enforce_payment_term_flag
, enforce_accounting_rule_flag = p_header_rec.enforce_accounting_rule_flag
, enforce_invoicing_rule_flag = p_header_rec.enforce_invoicing_rule_flag
, OVERRIDE_AMOUNT_FLAG = p_header_rec.OVERRIDE_AMOUNT_FLAG
, blanket_max_amount = p_header_rec.Blanket_Max_Amount
, blanket_min_amount = p_header_rec.Blanket_Min_Amount
-- , released_amount = p_header_rec.released_amount
-- , fulfilled_amount = p_header_rec.fulfilled_amount
-- , returned_amount = p_header_rec.returned_amount
, REVISION_CHANGE_REASON_CODE = p_header_rec.revision_change_reason_code
, REVISION_CHANGE_COMMENTS = p_header_rec.revision_change_comments
, REVISION_CHANGE_DATE = p_header_rec.revision_change_date
-- 11i10 Pricing Changes
, new_price_list_id = p_header_rec.new_price_list_id
, new_modifier_list_id = p_header_rec.new_modifier_list_id
, default_discount_percent = p_header_rec.default_discount_percent
, default_discount_amount = p_header_rec.default_discount_amount
WHERE order_number = p_header_rec.order_number;
UPDATE OE_BLANKET_LINES_EXT
SET
END_DATE_ACTIVE = p_header_rec.end_date_active
WHERE order_number = p_header_rec.order_number
AND END_DATE_ACTIVE IS NULL;
oe_debug_pub.add('Exiting OE_BLANKET_UTIL.UPDATE_ROW', 1);
, 'Update_Row'
);
End Update_Row;
PROCEDURE Delete_Row
( p_header_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2)
IS
--
l_transaction_phase_code VARCHAR2(30);
SELECT line_id
FROM OE_BLANKET_LINES
WHERE header_id=p_header_id;
oe_debug_pub.add('Entering OE_BLANKET_UTIL.DELETE_ROW - HEADER ID :'||
p_header_id);
SELECT /* MOAC_SQL_CHANGE */ transaction_phase_code
INTO l_transaction_phase_code
FROM oe_blanket_headers_all
WHERE header_id = p_header_id;
SELECT order_number
INTO l_order_number
FROM OE_BLANKET_HEADERS_ALL
WHERE header_id=p_header_id;
oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row WF negotiate');
OE_Order_WF_Util.delete_row(p_type=>'NEGOTIATE', p_id => p_header_id);
oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row WF blanket');
OE_Order_WF_Util.delete_row(p_type=>'BLANKET', p_id => p_header_id);
oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row after WF delete');
OE_Atchmt_Util.Delete_Attachments
( p_entity_code => OE_GLOBALS.G_ENTITY_HEADER
, p_entity_id => p_header_id
, x_return_status => l_return_status
);
OE_CONTRACTS_UTIL.delete_articles
(
p_api_version =>1,
p_doc_type => OE_CONTRACTS_UTIL.G_BSA_DOC_TYPE,
p_doc_id =>p_header_id,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data);
OE_Blanket_Util.Delete_Row(p_line_id => r_line_id.line_id
,x_return_status => X_RETURN_STATUS);
DELETE FROM OE_BLANKET_HEADERS
WHERE header_id = p_header_id;
DELETE FROM OE_BLANKET_HEADERS_HIST
WHERE header_id = p_header_id;
DELETE FROM OE_BLANKET_HEADERS_EXT
WHERE order_number=l_order_number;
oe_debug_pub.add('Exiting OE_BLANKET_UTIL.DELETE_ROW', 1);
, 'Delete_Row'
);
End Delete_Row;
PROCEDURE Insert_Row
( p_line_rec IN OE_Blanket_PUB.Line_rec_type
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_org_id NUMBER ;
oe_debug_pub.add('Entering OE_BLANKET_UTIL.INSERT_ROW - Line ID :'||
p_line_rec.line_id);
INSERT INTO OE_BLANKET_LINES
( ACCOUNTING_RULE_ID
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id
, inventory_item_id
, SOLD_TO_ORG_ID
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, ORDERED_ITEM_ID
, item_identifier_type
, lock_control
, open_flag
, ORDERED_ITEM
, ITEM_TYPE_CODE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, line_id
, line_type_id
, line_number
, ORG_ID
, PAYMENT_TERM_ID
, PREFERRED_GRADE --OPM Added 02/JUN/00
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, ship_to_org_id
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, ORDER_QUANTITY_UOM
, SALES_DOCUMENT_TYPE_CODE
, SHIPMENT_NUMBER
, LINE_CATEGORY_CODE
, BOOKED_FLAG
, SOURCE_DOCUMENT_TYPE_ID
, SOURCE_DOCUMENT_ID
, SOURCE_DOCUMENT_LINE_ID
, transaction_phase_code -- hashraf pack J
, source_document_version_number
)
VALUES
( p_line_rec.accounting_rule_id
, p_line_rec.agreement_id
, p_line_rec.attribute1
, p_line_rec.attribute10
, p_line_rec.attribute11
, p_line_rec.attribute12
, p_line_rec.attribute13
, p_line_rec.attribute14
, p_line_rec.attribute15
, p_line_rec.attribute16
, p_line_rec.attribute17
, p_line_rec.attribute18
, p_line_rec.attribute19
, p_line_rec.attribute20
, p_line_rec.attribute2
, p_line_rec.attribute3
, p_line_rec.attribute4
, p_line_rec.attribute5
, p_line_rec.attribute6
, p_line_rec.attribute7
, p_line_rec.attribute8
, p_line_rec.attribute9
, p_line_rec.context
, p_line_rec.created_by
, p_line_rec.creation_date
, p_line_rec.cust_po_number
, p_line_rec.deliver_to_org_id
, p_line_rec.freight_terms_code
, p_line_rec.header_id
, p_line_rec.inventory_item_id
, p_line_rec.sold_to_org_id
, p_line_rec.invoice_to_org_id
, p_line_rec.invoicing_rule_id
, p_line_rec.ordered_item_id
, p_line_rec.item_identifier_type
, l_lock_control
, 'Y'
, p_line_rec.ordered_item
, p_line_rec.item_type_code
, p_line_rec.last_updated_by
, p_line_rec.last_update_date
, p_line_rec.last_update_login
, p_line_rec.line_id
, p_line_rec.line_type_id
, p_line_rec.line_number
, MO_GLOBAL.GET_CURRENT_ORG_ID
, p_line_rec.payment_term_id
, p_line_rec.preferred_grade --OPM 02/JUN/00
, p_line_rec.price_list_id
, p_line_rec.program_application_id
, p_line_rec.program_id
, p_line_rec.program_update_date
, p_line_rec.request_id
, p_line_rec.salesrep_id
, p_line_rec.shipping_method_code
, p_line_rec.ship_from_org_id
, p_line_rec.ship_to_org_id
, p_line_rec.shipping_instructions
, p_line_rec.packing_instructions
, p_line_rec.ORDER_QUANTITY_UOM
, 'B'
, 1
, 'ORDER'
, 'N'
, p_line_rec.source_document_type_id
, p_line_rec.source_document_id
, p_line_rec.source_document_line_id
, p_line_rec.transaction_phase_code -- hashraf pack J
, p_line_rec.source_document_version_number
);
INSERT INTO OE_BLANKET_LINES_EXT
( START_DATE_ACTIVE
, END_DATE_ACTIVE
, MAX_RELEASE_AMOUNT
, MIN_RELEASE_AMOUNT
, min_release_quantity
, max_release_quantity
, BLANKET_LINE_MAX_AMOUNT
, BLANKET_LINE_MIN_AMOUNT
, BLANKET_MAX_QUANTITY
, BLANKET_MIN_QUANTITY
, OVERRIDE_BLANKET_CONTROLS_FLAG
, OVERRIDE_RELEASE_CONTROLS_FLAG
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, RELEASED_QUANTITY
, FULFILLED_QUANTITY
, RETURNED_QUANTITY
, ORDER_NUMBER
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, line_id
, line_number
)
VALUES
( p_line_rec.START_DATE_ACTIVE
, p_line_rec.END_DATE_ACTIVE
, p_line_rec.MAX_RELEASE_AMOUNT
, p_line_rec.MIN_RELEASE_AMOUNT
, p_line_rec.min_release_quantity
, p_line_rec.max_release_quantity
, p_line_rec.BLANKET_MAX_AMOUNT
, p_line_rec.BLANKET_MIN_AMOUNT
, p_line_rec.BLANKET_MAX_QUANTITY
, p_line_rec.BLANKET_MIN_QUANTITY
, p_line_rec.OVERRIDE_BLANKET_CONTROLS_FLAG
, p_line_rec.OVERRIDE_RELEASE_CONTROLS_FLAG
, p_line_rec.ENFORCE_PRICE_LIST_FLAG
, p_line_rec.enforce_ship_to_flag
, p_line_rec.enforce_invoice_to_flag
, p_line_rec.enforce_freight_term_flag
, p_line_rec.enforce_shipping_method_flag
, p_line_rec.enforce_payment_term_flag
, p_line_rec.enforce_accounting_rule_flag
, p_line_rec.enforce_invoicing_rule_flag
, p_line_rec.RELEASED_QUANTITY
, p_line_rec.FULFILLED_QUANTITY
, p_line_rec.RETURNED_QUANTITY
, p_line_rec.ORDER_NUMBER
, p_line_rec.RELEASED_AMOUNT
, p_line_rec.FULFILLED_AMOUNT
, p_line_rec.line_id
, p_line_rec.line_number
);
oe_debug_pub.add('Exiting OE_blanket_UTIL.INSERT_ROW', 1);
, 'Insert_Row'
);
End Insert_Row;
PROCEDURE Update_Row
( p_line_rec IN OE_Blanket_PUB.Line_rec_type
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
oe_debug_pub.add('Entering OE_Blanket_UTIL.UPDATE_ROW - LINE', 1);
UPDATE OE_BLANKET_LINES
SET ACCOUNTING_RULE_ID = p_line_rec.accounting_rule_id
, AGREEMENT_ID = p_line_rec.agreement_id
, ATTRIBUTE1 = p_line_rec.attribute1
, ATTRIBUTE10 = p_line_rec.attribute10
, ATTRIBUTE11 = p_line_rec.attribute11
, ATTRIBUTE12 = p_line_rec.attribute12
, ATTRIBUTE13 = p_line_rec.attribute13
, ATTRIBUTE14 = p_line_rec.attribute14
, ATTRIBUTE15 = p_line_rec.attribute15
, ATTRIBUTE16 = p_line_rec.attribute16
, ATTRIBUTE17 = p_line_rec.attribute17
, ATTRIBUTE18 = p_line_rec.attribute18
, ATTRIBUTE19 = p_line_rec.attribute19
, ATTRIBUTE20 = p_line_rec.attribute20
, ATTRIBUTE2 = p_line_rec.attribute2
, ATTRIBUTE3 = p_line_rec.attribute3
, ATTRIBUTE4 = p_line_rec.attribute4
, ATTRIBUTE5 = p_line_rec.attribute5
, ATTRIBUTE6 = p_line_rec.attribute6
, ATTRIBUTE7 = p_line_rec.attribute7
, ATTRIBUTE8 = p_line_rec.attribute8
, ATTRIBUTE9 = p_line_rec.attribute9
, CONTEXT = p_line_rec.context
-- , CREATED_BY = p_line_rec.created_by
-- , CREATION_DATE = p_line_rec.creation_date
, CUST_PO_NUMBER = p_line_rec.cust_po_number
, DELIVER_TO_ORG_ID = p_line_rec.deliver_to_org_id
, FREIGHT_TERMS_CODE = p_line_rec.freight_terms_code
, header_id = p_line_rec.header_id
, inventory_item_id = p_line_rec.inventory_item_id
, INVOICE_TO_ORG_ID = p_line_rec.invoice_to_org_id
, INVOICING_RULE_ID = p_line_rec.invoicing_rule_id
, ORDERED_ITEM_ID = p_line_rec.ordered_item_id
, item_identifier_type = p_line_rec.item_identifier_type
, lock_control = lock_control + 1
, ORDERED_ITEM = p_line_rec.ordered_item
, ITEM_TYPE_CODE = p_line_rec.item_type_code
, LAST_UPDATED_BY = nvl(p_line_rec.last_updated_by, FND_GLOBAL.USER_ID)
, LAST_UPDATE_DATE = nvl(p_line_rec.last_update_date, sysdate)
, LAST_UPDATE_LOGIN = p_line_rec.last_update_login
, line_number = p_line_rec.line_number
, PAYMENT_TERM_ID = p_line_rec.payment_term_id
, PREFERRED_GRADE = p_line_rec.preferred_grade
, PRICE_LIST_ID = p_line_rec.price_list_id
, PROGRAM_APPLICATION_ID = p_line_rec.program_application_id
, PROGRAM_ID = p_line_rec.program_id
, PROGRAM_UPDATE_DATE = p_line_rec.program_update_date
, REQUEST_ID = p_line_rec.request_id
, SALESREP_ID = p_line_rec.salesrep_id
, SHIPPING_METHOD_CODE = p_line_rec.shipping_method_code
, ship_from_org_id = p_line_rec.ship_from_org_id
, SHIP_TO_ORG_ID = p_line_rec.ship_to_org_id
, SHIPPING_INSTRUCTIONS = p_line_rec.shipping_instructions
, PACKING_INSTRUCTIONS = p_line_rec.packing_instructions
, ORDER_QUANTITY_UOM = p_line_rec.ORDER_QUANTITY_UOM
-- hashraf ... start of pack J
, transaction_phase_code = p_line_rec.transaction_phase_code
, source_document_version_number = p_line_rec.source_document_version_number
-- hashraf ... end of pack J
WHERE line_id = p_line_rec.line_id
AND header_id = p_line_rec.header_id ;
UPDATE OE_BLANKET_LINES_EXT
SET
min_release_quantity = p_line_rec.min_release_quantity
, max_release_quantity = p_line_rec.max_release_quantity
, START_DATE_ACTIVE = p_line_rec.START_DATE_ACTIVE
, END_DATE_ACTIVE = p_line_rec.END_DATE_ACTIVE
, MAX_RELEASE_AMOUNT = p_line_rec.MAX_RELEASE_AMOUNT
, MIN_RELEASE_AMOUNT = p_line_rec.MIN_RELEASE_AMOUNT
, BLANKET_LINE_MAX_AMOUNT = p_line_rec.BLANKET_MAX_AMOUNT
, BLANKET_LINE_MIN_AMOUNT = p_line_rec.BLANKET_MIN_AMOUNT
, BLANKET_MAX_QUANTITY = p_line_rec.BLANKET_MAX_QUANTITY
, BLANKET_MIN_QUANTITY = p_line_rec.BLANKET_MIN_QUANTITY
, OVERRIDE_BLANKET_CONTROLS_FLAG = p_line_rec.OVERRIDE_BLANKET_CONTROLS_FLAG
, OVERRIDE_RELEASE_CONTROLS_FLAG = p_line_rec.OVERRIDE_RELEASE_CONTROLS_FLAG
, ENFORCE_PRICE_LIST_FLAG = p_line_rec.ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag = p_line_rec.enforce_ship_to_flag
, enforce_invoice_to_flag = p_line_rec.enforce_invoice_to_flag
, enforce_freight_term_flag = p_line_rec.enforce_freight_term_flag
, enforce_shipping_method_flag = p_line_rec.enforce_shipping_method_flag
, enforce_payment_term_flag = p_line_rec.enforce_payment_term_flag
, enforce_accounting_rule_flag = p_line_rec.enforce_accounting_rule_flag
, enforce_invoicing_rule_flag = p_line_rec.enforce_invoicing_rule_flag
-- , RELEASED_QUANTITY = p_line_rec.RELEASED_QUANTITY
-- , FULFILLED_QUANTITY = p_line_rec.FULFILLED_QUANTITY
-- , RETURNED_QUANTITY = p_line_rec.RETURNED_QUANTITY
-- , released_amount = p_line_rec.released_amount
-- , fulfilled_amount = p_line_rec.fulfilled_amount
-- , returned_amount = p_line_rec.returned_amount
, line_number = p_line_rec.line_number --bug5894169
WHERE line_id = p_line_rec.line_id;
oe_debug_pub.add('Exiting OE_Blanket_UTIL.UPDATE_ROW - LINE', 1);
, 'Update_Row'
);
End Update_Row;
PROCEDURE Delete_Row
( p_line_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_header_id NUMBER := g_header_rec.header_id;
oe_debug_pub.add('Entering OE_BLANKET_UTIL.DELETE_ROW - LINE ID :'||
p_line_id);
SELECT /* MOAC_SQL_CHANGE */ header_id into l_header_id
FROM OE_BLANKET_LINES_all
WHERE line_id = p_line_id;
OE_Atchmt_Util.Delete_Attachments
( p_entity_code => OE_GLOBALS.G_ENTITY_LINE
, p_entity_id => p_line_id
, x_return_status => l_return_status
);
DELETE FROM OE_BLANKET_LINES
WHERE line_id = p_line_id;
DELETE FROM OE_BLANKET_LINES_EXT
WHERE line_id = p_line_id;
DELETE FROM OE_BLANKET_LINES_HIST
WHERE line_id = p_line_id;
oe_debug_pub.add('Exiting OE_BLANKET_UTIL.DELETE_ROW', 1);
, 'Delete_Row'
);
End Delete_Row;
SELECT ACCOUNTING_RULE_ID
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, bh.ORDER_NUMBER
, ORDER_TYPE_ID
, ORG_ID
, PAYMENT_TERM_ID
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, SHIP_TO_ORG_ID
, SOLD_TO_CONTACT_ID
, SOLD_TO_ORG_ID
, TRANSACTIONAL_CURR_CODE
, conversion_type_code
, LOCK_CONTROL
, VERSION_NUMBER
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, on_hold_flag
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, OVERRIDE_AMOUNT_FLAG
, BLANKET_MAX_AMOUNT
, BLANKET_MIN_AMOUNT
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, RETURNED_AMOUNT
, source_document_id
, source_document_type_id
, SALES_DOCUMENT_NAME -- hashraf ... start of pack J
, TRANSACTION_PHASE_CODE
, USER_STATUS_CODE
, FLOW_STATUS_CODE
, SUPPLIER_SIGNATURE
, SUPPLIER_SIGNATURE_DATE
, CUSTOMER_SIGNATURE
, CUSTOMER_SIGNATURE_DATE
, sold_to_site_use_id
, draft_submitted_flag
, source_document_version_number -- hashraf ... end of pack J
-- 11i10 Pricing Changes
, new_price_list_id
, new_modifier_list_id
, default_discount_percent
, default_discount_amount
FROM OE_BLANKET_HEADERS bh, OE_BLANKET_HEADERS_EXT bhx
WHERE bh.order_number = bhx.order_number
AND bh.header_id = p_header_id
AND bh.sales_document_type_code = 'B';
SELECT ACCOUNTING_RULE_ID
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, bh.ORDER_NUMBER
, ORDER_TYPE_ID
, ORG_ID
, PAYMENT_TERM_ID
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, SHIP_TO_ORG_ID
, SOLD_TO_CONTACT_ID
, SOLD_TO_ORG_ID
, TRANSACTIONAL_CURR_CODE
, conversion_type_code
, NULL -- , LOCK_CONTROL
, VERSION_NUMBER
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, on_hold_flag
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, OVERRIDE_AMOUNT_FLAG
, BLANKET_MAX_AMOUNT
, BLANKET_MIN_AMOUNT
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, RETURNED_AMOUNT
, source_document_id
, source_document_type_id
, SALES_DOCUMENT_NAME -- hashraf ... start of pack J
, TRANSACTION_PHASE_CODE
, USER_STATUS_CODE
, FLOW_STATUS_CODE
, SUPPLIER_SIGNATURE
, SUPPLIER_SIGNATURE_DATE
, CUSTOMER_SIGNATURE
, CUSTOMER_SIGNATURE_DATE
, sold_to_site_use_id
, draft_submitted_flag
, source_document_version_number -- hashraf ... end of pack J
-- 11i10 Pricing Changes
, new_price_list_id
, new_modifier_list_id
, default_discount_percent
, default_discount_amount
FROM OE_BLANKET_HEADERS_HIST bh
WHERE bh.header_id = p_header_id
AND bh.sales_document_type_code = 'B'
AND bh.version_number = p_version_number
AND (PHASE_CHANGE_FLAG = p_phase_change_flag
OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'));
SELECT /* MOAC_SQL_CHANGE */ version_number INTO l_current_version_number FROM OE_BLANKET_HEADERS_ALL WHERE header_id = p_header_id;
, x_header_rec.last_updated_by
, x_header_rec.last_update_date
, x_header_rec.last_update_login
, x_header_rec.order_number
, x_header_rec.order_type_id
, x_header_rec.org_id
, x_header_rec.payment_term_id
, x_header_rec.price_list_id
, x_header_rec.program_application_id
, x_header_rec.program_id
, x_header_rec.program_update_date
, x_header_rec.request_id
, x_header_rec.salesrep_id
, x_header_rec.shipping_method_code
, x_header_rec.ship_from_org_id
, x_header_rec.ship_to_org_id
, x_header_rec.sold_to_contact_id
, x_header_rec.sold_to_org_id
, x_header_rec.transactional_curr_code
, x_header_rec.conversion_type_code
, x_header_rec.LOCK_CONTROL
, x_header_rec.version_number
, x_header_rec.shipping_instructions
, x_header_rec.packing_instructions
, x_header_rec.START_DATE_ACTIVE
, x_header_rec.END_DATE_ACTIVE
, x_header_rec.on_hold_flag
, x_header_rec.ENFORCE_PRICE_LIST_FLAG
, x_header_rec.enforce_ship_to_flag
, x_header_rec.enforce_invoice_to_flag
, x_header_rec.enforce_freight_term_flag
, x_header_rec.enforce_shipping_method_flag
, x_header_rec.enforce_payment_term_flag
, x_header_rec.enforce_accounting_rule_flag
, x_header_rec.enforce_invoicing_rule_flag
, x_header_rec.OVERRIDE_AMOUNT_FLAG
, x_header_rec.Blanket_Max_Amount
, x_header_rec.Blanket_Min_Amount
, x_header_rec.RELEASED_AMOUNT
, x_header_rec.FULFILLED_AMOUNT
, x_header_rec.RETURNED_AMOUNT
, x_header_rec.source_document_id
, x_header_rec.source_document_type_id
, x_header_rec.SALES_DOCUMENT_NAME -- hashraf ... start of pack J
, x_header_rec.TRANSACTION_PHASE_CODE
, x_header_rec.USER_STATUS_CODE
, x_header_rec.FLOW_STATUS_CODE
, x_header_rec.SUPPLIER_SIGNATURE
, x_header_rec.SUPPLIER_SIGNATURE_DATE
, x_header_rec.CUSTOMER_SIGNATURE
, x_header_rec.CUSTOMER_SIGNATURE_DATE
, x_header_rec.sold_to_site_use_id
, x_header_rec.draft_submitted_flag
, x_header_rec.source_document_version_number -- hashraf ... end of pack J
-- 11i10 Pricing Changes
, x_header_rec.new_price_list_id
, x_header_rec.new_modifier_list_id
, x_header_rec.default_discount_percent
, x_header_rec.default_discount_amount
;
SELECT name
INTO x_header_rec.new_price_list_name
FROM qp_list_headers_vl
WHERE list_header_id = x_header_rec.new_price_list_id;
SELECT name
INTO x_header_rec.new_modifier_list_name
FROM qp_list_headers_vl
WHERE list_header_id = x_header_rec.new_modifier_list_id;
, x_header_rec.last_updated_by
, x_header_rec.last_update_date
, x_header_rec.last_update_login
, x_header_rec.order_number
, x_header_rec.order_type_id
, x_header_rec.org_id
, x_header_rec.payment_term_id
, x_header_rec.price_list_id
, x_header_rec.program_application_id
, x_header_rec.program_id
, x_header_rec.program_update_date
, x_header_rec.request_id
, x_header_rec.salesrep_id
, x_header_rec.shipping_method_code
, x_header_rec.ship_from_org_id
, x_header_rec.ship_to_org_id
, x_header_rec.sold_to_contact_id
, x_header_rec.sold_to_org_id
, x_header_rec.transactional_curr_code
, x_header_rec.conversion_type_code
, x_header_rec.LOCK_CONTROL
, x_header_rec.version_number
, x_header_rec.shipping_instructions
, x_header_rec.packing_instructions
, x_header_rec.START_DATE_ACTIVE
, x_header_rec.END_DATE_ACTIVE
, x_header_rec.on_hold_flag
, x_header_rec.ENFORCE_PRICE_LIST_FLAG
, x_header_rec.enforce_ship_to_flag
, x_header_rec.enforce_invoice_to_flag
, x_header_rec.enforce_freight_term_flag
, x_header_rec.enforce_shipping_method_flag
, x_header_rec.enforce_payment_term_flag
, x_header_rec.enforce_accounting_rule_flag
, x_header_rec.enforce_invoicing_rule_flag
, x_header_rec.OVERRIDE_AMOUNT_FLAG
, x_header_rec.Blanket_Max_Amount
, x_header_rec.Blanket_Min_Amount
, x_header_rec.RELEASED_AMOUNT
, x_header_rec.FULFILLED_AMOUNT
, x_header_rec.RETURNED_AMOUNT
, x_header_rec.source_document_id
, x_header_rec.source_document_type_id
, x_header_rec.SALES_DOCUMENT_NAME -- hashraf ... start of pack J
, x_header_rec.TRANSACTION_PHASE_CODE
, x_header_rec.USER_STATUS_CODE
, x_header_rec.FLOW_STATUS_CODE
, x_header_rec.SUPPLIER_SIGNATURE
, x_header_rec.SUPPLIER_SIGNATURE_DATE
, x_header_rec.CUSTOMER_SIGNATURE
, x_header_rec.CUSTOMER_SIGNATURE_DATE
, x_header_rec.sold_to_site_use_id
, x_header_rec.draft_submitted_flag
, x_header_rec.source_document_version_number -- hashraf ... end of pack J
-- 11i10 Pricing Changes
, x_header_rec.new_price_list_id
, x_header_rec.new_modifier_list_id
, x_header_rec.default_discount_percent
, x_header_rec.default_discount_amount
;
SELECT name
INTO x_header_rec.new_price_list_name
FROM qp_list_headers_vl
WHERE list_header_id = x_header_rec.new_price_list_id;
SELECT name
INTO x_header_rec.new_modifier_list_name
FROM qp_list_headers_vl
WHERE list_header_id = x_header_rec.new_modifier_list_id;
SELECT ACCOUNTING_RULE_ID
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id
, min_release_quantity
, max_release_quantity
, inventory_item_id
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, ORDERED_ITEM_ID
, item_identifier_type
, lock_control
, ORDERED_ITEM
, ITEM_TYPE_CODE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, bl.line_id
, bl.line_number
, PAYMENT_TERM_ID
, PREFERRED_GRADE --OPM 02/JUN/00
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, SHIP_TO_ORG_ID
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, MAX_RELEASE_AMOUNT
, MIN_RELEASE_AMOUNT
, BLANKET_LINE_MAX_AMOUNT
, BLANKET_LINE_MIN_AMOUNT
, BLANKET_MAX_QUANTITY
, BLANKET_MIN_QUANTITY
, OVERRIDE_BLANKET_CONTROLS_FLAG
, OVERRIDE_RELEASE_CONTROLS_FLAG
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, ORDER_QUANTITY_UOM
, RELEASED_QUANTITY
, blx.FULFILLED_QUANTITY
, RETURNED_QUANTITY
, ORDER_NUMBER
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, RETURNED_AMOUNT
, TRANSACTION_PHASE_CODE -- hashraf ... start of pack J
, source_document_version_number
-- 11i10 Pricing Changes
, modifier_list_line_id
, blx.qp_list_line_id
FROM OE_BLANKET_LINES bl, OE_BLANKET_LINES_EXT blx
WHERE bl.line_id = blx.line_id
AND bl.sales_document_type_code = 'B'
AND ( bl.line_id = p_line_id OR
header_id = p_header_id) ;
SELECT ACCOUNTING_RULE_ID
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id
, min_release_quantity
, max_release_quantity
, inventory_item_id
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, ORDERED_ITEM_ID
, item_identifier_type
, NULL -- , lock_control
, ORDERED_ITEM
, ITEM_TYPE_CODE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, bl.line_id
, bl.line_number
, PAYMENT_TERM_ID
, PREFERRED_GRADE --OPM 02/JUN/00
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, SHIP_TO_ORG_ID
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, MAX_RELEASE_AMOUNT
, MIN_RELEASE_AMOUNT
, BLANKET_LINE_MAX_AMOUNT
, BLANKET_LINE_MIN_AMOUNT
, BLANKET_MAX_QUANTITY
, BLANKET_MIN_QUANTITY
, OVERRIDE_BLANKET_CONTROLS_FLAG
, OVERRIDE_RELEASE_CONTROLS_FLAG
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, ORDER_QUANTITY_UOM
, RELEASED_QUANTITY
, FULFILLED_QUANTITY
, RETURNED_QUANTITY
, ORDER_NUMBER
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, RETURNED_AMOUNT
, TRANSACTION_PHASE_CODE -- hashraf ... start of pack J
, source_document_version_number
-- 11i10 Pricing Changes
, modifier_list_line_id
, qp_list_line_id
FROM OE_BLANKET_LINES_HIST bl
WHERE bl.sales_document_type_code = 'B'
AND ( bl.line_id = p_line_id OR
header_id = p_header_id)
AND version_number = p_version_number
AND (PHASE_CHANGE_FLAG = p_phase_change_flag
OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'));
SELECT /* MOAC_SQL_CHANGE */ version_number INTO l_current_version_number FROM OE_BLANKET_HEADERS_ALL WHERE (header_id = p_header_id OR header_id = (select header_id from oe_blanket_lines_ALL where line_id = p_line_id));
x_line_tbl(l_count).last_updated_by := l_rec.LAST_UPDATED_BY;
x_line_tbl(l_count).last_update_date := l_rec.LAST_UPDATE_DATE;
x_line_tbl(l_count).last_update_login := l_rec.LAST_UPDATE_LOGIN;
x_line_tbl(l_count).program_update_date := l_rec.PROGRAM_UPDATE_DATE;
select decode(arithmetic_operator,'%',operand,null)
,decode(arithmetic_operator,'AMT',operand,null)
into x_line_tbl(l_count).discount_percent
,x_line_tbl(l_count).discount_amount
from qp_list_lines
where list_line_id = x_line_tbl(l_count).modifier_list_line_id;
x_line_tbl(l_count).last_updated_by := l_rec.LAST_UPDATED_BY;
x_line_tbl(l_count).last_update_date := l_rec.LAST_UPDATE_DATE;
x_line_tbl(l_count).last_update_login := l_rec.LAST_UPDATE_LOGIN;
x_line_tbl(l_count).program_update_date := l_rec.PROGRAM_UPDATE_DATE;
select decode(arithmetic_operator,'%',operand,null)
,decode(arithmetic_operator,'AMT',operand,null)
into x_line_tbl(l_count).discount_percent
,x_line_tbl(l_count).discount_amount
from qp_list_lines
where list_line_id = x_line_tbl(l_count).modifier_list_line_id;
SELECT /* MOAC_SQL_CHANGE */ version_number INTO l_current_version_number FROM OE_BLANKET_HEADERS_ALL WHERE header_id = p_header_id;
SELECT /* MOAC_SQL_CHANGE */ bh.lock_control
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_HEADERS_EXT BHX
WHERE BH.ORDER_NUMBER = BHX.ORDER_NUMBER
AND BH.ORDER_NUMBER = p_blanket_id
AND ORG_ID = MO_GLOBAL.get_current_org_id
AND BH.SALES_DOCUMENT_TYPE_CODE = 'B'
FOR UPDATE NOWAIT;
SELECT /* MOAC_SQL_CHANGE */ bl.line_id, bl.lock_control
FROM OE_BLANKET_LINES_ALL BL, OE_BLANKET_LINES_EXT BLX
WHERE BL.LINE_ID = BLX.LINE_ID
AND BLX.ORDER_NUMBER = l_blanket_id
AND BL.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID
AND BL.SALES_DOCUMENT_TYPE_CODE = 'B'
FOR UPDATE NOWAIT;
SELECT ORDER_NUMBER
INTO l_blanket_id
FROM OE_BLANKET_LINES_EXT
WHERE LINE_ID = p_blanket_line_id;
oe_debug_pub.add('selected for update, now compare', 3);
fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
SELECT contract_template_id
FROM oe_transaction_types_all
WHERE transaction_type_id = cp_trans_type_id;
SELECT OE_ORDER_HEADERS_S.NEXTVAL
INTO p_x_header_rec.header_id
FROM DUAL;
p_x_header_rec.last_update_date := sysdate;
p_x_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
SELECT CURRENCY_CODE
INTO p_x_header_rec.transactional_curr_code
FROM OE_GL_SETS_OF_BOOKS_V
WHERE SET_OF_BOOKS_ID = OE_Sys_Parameters.VALUE('SET_OF_BOOKS_ID');
Select /* MOAC_SQL_CHANGE */ DEF_TRANSACTION_PHASE_CODE
INTO p_x_header_rec.transaction_phase_code
from oe_transaction_types_all
where TRANSACTION_TYPE_ID = p_x_header_rec.order_type_id;
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO p_x_line_rec.line_id
FROM DUAL;
SELECT NVL(MAX(line_number)+1,1)
INTO p_x_line_rec.line_number
FROM OE_BLANKET_LINES
WHERE header_id = p_x_line_rec.header_id;
SELECT PRIMARY_UOM_CODE
INTO p_x_line_rec.order_quantity_uom
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_x_line_rec.inventory_item_id
AND ORGANIZATION_ID = g_header_rec.org_id;
p_x_line_rec.last_update_date := sysdate;
p_x_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_select_stmt VARCHAR2(240);
SELECT meaning
INTO l_column_name
FROM fnd_lookups
WHERE lookup_type = 'SEQUENCE_METHOD'
AND lookup_code = t;
Select Count(header_id) into
lcount From
OE_BLANKET_HEADERS_ALL
WHERE order_number = X_doc_sequence_value;
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
SELECT 'N'
INTO l_valid
FROM oe_blanket_lines L
WHERE L.line_number =
oe_delayed_requests_pvt.g_delayed_requests(p_req_ind).param2
AND L.header_id =
oe_delayed_requests_pvt.g_delayed_requests(p_req_ind).param1
AND L.line_id <>
oe_delayed_requests_pvt.g_delayed_requests(p_req_ind).entity_id;
select count(1) into lcount
from OE_BLANKET_LINES L, OE_BLANKET_LINES_EXT LX
where L.line_id = LX.line_id
AND L.header_id = l_header_id
AND L.line_id <> l_line_id
AND ( ( l_item_identifier_type NOT IN ('ALL','CAT')
AND L.item_identifier_type NOT IN ('ALL','CAT')
)
OR (L.item_identifier_type = l_item_identifier_type)
)
AND nvl(L.inventory_item_id,-1) = nvl(l_item_id,-1)
AND L.item_identifier_type = l_item_identifier_type
AND NVL( to_char(L.ordered_item_id),
decode (L.item_identifier_type,'INT', to_char(NVL(L.inventory_item_id,-1)),
'ALL', to_char(NVL(L.inventory_item_id,-1)),
'CAT', to_char(NVL(L.inventory_item_id,-1)),
'CUST', to_char(NVL(L.ordered_item_id, NVL(L.inventory_item_id,-1) )),
NVL(L.Ordered_item,'XXXX') ) ) = decode ( l_item_identifier_type , 'INT', to_char(nvl(l_item_id,-1))
, 'ALL', to_char(nvl(l_item_id,-1))
, 'CAT' , to_char(nvl(l_item_id,-1))
, 'CUST', to_char(nvl(l_ordered_item_id,-1))
, to_char(nvl(l_ordered_item,'XXXX') ))
AND ( (l_end_date_active IS NULL
-- Duplicate blanket line date cannot be effective
-- beyond the start date of this blanket line
AND l_start_date_active <=
nvl(LX.end_date_active,l_start_date_active)
)
OR (l_end_date_active IS NOT NULL
-- Dates for this line cannot be between effectivity dates
-- for duplicate blanket line
AND (l_end_date_active BETWEEN
LX.start_date_active AND
nvl(LX.end_date_active,l_end_date_active + 1)
OR l_start_date_active BETWEEN
LX.start_date_active AND
nvl(LX.end_date_active,l_end_date_active + 1)
)
)
);
SELECT 'VALID'
INTO l_dummy
FROM QP_LIST_HEADERS
WHERE LIST_HEADER_ID = p_price_list_id
AND LIST_SOURCE_CODE = 'BLKT';
SELECT 'VALID'
INTO l_dummy
FROM QP_LIST_HEADERS
WHERE LIST_HEADER_ID = p_price_list_id
AND LIST_SOURCE_CODE = 'BSO'
AND orig_system_header_ref = p_blanket_header_id
;
SELECT /* MOAC_SQL_CHANGE */ version_number
INTO l_new_version_number
FROM oe_blanket_headers_all
WHERE header_id = g_old_header_hist_rec.header_id;
Insert_History_Records
(p_header_rec => g_old_header_hist_rec
,p_line_tbl => g_old_line_hist_tbl
,p_version_flag => p_version_flag
,p_phase_change_flag => p_phase_change_flag
,x_return_status => l_return_status);
SELECT OE_ORDER_HEADERS_S.NEXTVAL
INTO l_header_rec.header_id
FROM DUAL;
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_line_tbl(l_count).line_id
FROM DUAL;
PROCEDURE Insert_History_Records
(p_header_rec IN OUT NOCOPY OE_Blanket_PUB.Header_Rec_Type
,p_line_tbl IN OUT NOCOPY OE_Blanket_PUB.Line_Tbl_Type
,p_version_flag in varchar2 := null
,p_phase_change_flag in varchar2 := null
,x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_org_id NUMBER;
oe_debug_pub.ADD('Before inserting blanket history header_id ' || p_header_rec.header_id, 5);
INSERT INTO OE_BLANKET_HEADERS_HIST
( ACCOUNTING_RULE_ID
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
--adding context since it was missing
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORDER_NUMBER
, ORDER_TYPE_ID
, PAYMENT_TERM_ID
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, SHIP_TO_ORG_ID
, SOLD_TO_CONTACT_ID
, SOLD_TO_ORG_ID
, TRANSACTIONAL_CURR_CODE
, conversion_type_code
, VERSION_NUMBER
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, on_hold_flag
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, OVERRIDE_AMOUNT_FLAG
, BLANKET_MAX_AMOUNT
, BLANKET_MIN_AMOUNT
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, RETURNED_AMOUNT
, ORG_ID
, REVISION_CHANGE_REASON_CODE
, REVISION_CHANGE_COMMENTS
, REVISION_CHANGE_DATE
, RESPONSIBILITY_ID
, HIST_TYPE_CODE
, HIST_CREATION_DATE
, HIST_CREATED_BY
, SALES_DOCUMENT_TYPE_CODE
, source_document_id
, source_document_type_id
, SALES_DOCUMENT_NAME -- hashraf ... start of pack J
, TRANSACTION_PHASE_CODE
, USER_STATUS_CODE
, FLOW_STATUS_CODE
, SUPPLIER_SIGNATURE
, SUPPLIER_SIGNATURE_DATE
, CUSTOMER_SIGNATURE
, CUSTOMER_SIGNATURE_DATE
, sold_to_site_use_id
, draft_submitted_flag
, source_document_version_number -- hashraf ... end of pack J
, version_flag
, phase_change_flag
, new_modifier_list_id
, new_price_list_id
, default_discount_amount
, default_discount_percent
)
VALUES
( p_header_rec.accounting_rule_id
, p_header_rec.agreement_id
, p_header_rec.attribute1
, p_header_rec.attribute10
, p_header_rec.attribute11
, p_header_rec.attribute12
, p_header_rec.attribute13
, p_header_rec.attribute14
, p_header_rec.attribute15
, p_header_rec.attribute16
, p_header_rec.attribute17
, p_header_rec.attribute18
, p_header_rec.attribute19
, p_header_rec.attribute20
, p_header_rec.attribute2
, p_header_rec.attribute3
, p_header_rec.attribute4
, p_header_rec.attribute5
, p_header_rec.attribute6
, p_header_rec.attribute7
, p_header_rec.attribute8
, p_header_rec.attribute9
--added context ,since it was missing
, p_header_rec.context
, p_header_rec.created_by
, p_header_rec.creation_date
, p_header_rec.cust_po_number
, p_header_rec.deliver_to_org_id
, p_header_rec.freight_terms_code
, p_header_rec.header_id
, p_header_rec.invoice_to_org_id
, p_header_rec.invoicing_rule_id
, p_header_rec.last_updated_by
, p_header_rec.last_update_date
, p_header_rec.last_update_login
, p_header_rec.order_number
, p_header_rec.order_type_id
, p_header_rec.payment_term_id
, p_header_rec.price_list_id
, p_header_rec.program_application_id
, p_header_rec.program_id
, p_header_rec.program_update_date
, p_header_rec.request_id
, p_header_rec.salesrep_id
, p_header_rec.shipping_method_code
, p_header_rec.ship_from_org_id
, p_header_rec.ship_to_org_id
, p_header_rec.sold_to_contact_id
, p_header_rec.sold_to_org_id
, p_header_rec.transactional_curr_code
, p_header_rec.conversion_type_code
, p_header_rec.version_number
, p_header_rec.shipping_instructions
, p_header_rec.packing_instructions
, p_header_rec.START_DATE_ACTIVE
, p_header_rec.END_DATE_ACTIVE
, p_header_rec.on_hold_flag
, p_header_rec.ENFORCE_PRICE_LIST_FLAG
, p_header_rec.enforce_ship_to_flag
, p_header_rec.enforce_invoice_to_flag
, p_header_rec.enforce_freight_term_flag
, p_header_rec.enforce_shipping_method_flag
, p_header_rec.enforce_payment_term_flag
, p_header_rec.enforce_accounting_rule_flag
, p_header_rec.enforce_invoicing_rule_flag
, p_header_rec.OVERRIDE_AMOUNT_FLAG
, p_header_rec.Blanket_Max_Amount
, p_header_rec.Blanket_Min_Amount
, p_header_rec.RELEASED_AMOUNT
, p_header_rec.FULFILLED_AMOUNT
, p_header_rec.RETURNED_AMOUNT
, p_header_rec.ORG_ID
, p_header_rec.REVISION_CHANGE_REASON_CODE
, p_header_rec.REVISION_CHANGE_COMMENTS
, p_header_rec.REVISION_CHANGE_DATE
, nvl(FND_GLOBAL.RESP_ID,-1)
, 'UPDATE'
, sysdate
, nvl(FND_GLOBAL.USER_ID, -1)
, 'B'
, p_header_rec.source_document_id
, p_header_rec.source_document_type_id
, p_header_rec.SALES_DOCUMENT_NAME -- hashraf ... start of pack J
, p_header_rec.TRANSACTION_PHASE_CODE
, p_header_rec.USER_STATUS_CODE
, p_header_rec.FLOW_STATUS_CODE
, p_header_rec.SUPPLIER_SIGNATURE
, p_header_rec.SUPPLIER_SIGNATURE_DATE
, p_header_rec.CUSTOMER_SIGNATURE
, p_header_rec.CUSTOMER_SIGNATURE_DATE
, p_header_rec.sold_to_site_use_id
, p_header_rec.draft_submitted_flag
, p_header_rec.source_document_version_number -- hashraf ... end of pack J
, p_version_flag
, p_phase_change_flag
, p_header_rec.new_modifier_list_id
, p_header_rec.new_price_list_id
, p_header_rec.default_discount_amount
, p_header_rec.default_discount_percent
);
oe_debug_pub.ADD('Before inserting blanket line history line_id ' || p_line_tbl(I).line_id, 5);
INSERT INTO OE_BLANKET_LINES_HIST
( ACCOUNTING_RULE_ID
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_ORG_ID
, FREIGHT_TERMS_CODE
, header_id --header_id
, min_release_quantity
, max_release_quantity
, inventory_item_id
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, ORDERED_ITEM_ID
, item_identifier_type
, ORDERED_ITEM
, ITEM_TYPE_CODE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, line_id --line_id
, line_number --blanket_line_number
, PAYMENT_TERM_ID
, PREFERRED_GRADE --OPM Added 02/JUN/00
, PRICE_LIST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SALESREP_ID
, SHIPPING_METHOD_CODE
, ship_from_org_id
, ship_to_org_id
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, MAX_RELEASE_AMOUNT
, MIN_RELEASE_AMOUNT
, BLANKET_LINE_MAX_AMOUNT
, BLANKET_LINE_MIN_AMOUNT
, BLANKET_MAX_QUANTITY
, BLANKET_MIN_QUANTITY
, OVERRIDE_BLANKET_CONTROLS_FLAG
, OVERRIDE_RELEASE_CONTROLS_FLAG
, ENFORCE_PRICE_LIST_FLAG
, enforce_ship_to_flag
, enforce_invoice_to_flag
, enforce_freight_term_flag
, enforce_shipping_method_flag
, enforce_payment_term_flag
, enforce_accounting_rule_flag
, enforce_invoicing_rule_flag
, ORDER_QUANTITY_UOM
, RELEASED_QUANTITY
, FULFILLED_QUANTITY
, RETURNED_QUANTITY
, ORDER_NUMBER
, RELEASED_AMOUNT
, FULFILLED_AMOUNT
, RETURNED_AMOUNT
, RESPONSIBILITY_ID
, HIST_TYPE_CODE
, HIST_CREATION_DATE
, HIST_CREATED_BY
, VERSION_NUMBER
, SALES_DOCUMENT_TYPE_CODE
, TRANSACTION_PHASE_CODE -- hashraf ... start of pack J
, source_document_version_number
, version_flag
, phase_change_flag
, modifier_list_line_id
)
VALUES
( p_line_tbl(I).accounting_rule_id
, p_line_tbl(I).agreement_id
, p_line_tbl(I).attribute1
, p_line_tbl(I).attribute10
, p_line_tbl(I).attribute11
, p_line_tbl(I).attribute12
, p_line_tbl(I).attribute13
, p_line_tbl(I).attribute14
, p_line_tbl(I).attribute15
, p_line_tbl(I).attribute16
, p_line_tbl(I).attribute17
, p_line_tbl(I).attribute18
, p_line_tbl(I).attribute19
, p_line_tbl(I).attribute20
, p_line_tbl(I).attribute2
, p_line_tbl(I).attribute3
, p_line_tbl(I).attribute4
, p_line_tbl(I).attribute5
, p_line_tbl(I).attribute6
, p_line_tbl(I).attribute7
, p_line_tbl(I).attribute8
, p_line_tbl(I).attribute9
, p_line_tbl(I).context
, p_line_tbl(I).created_by
, p_line_tbl(I).creation_date
, p_line_tbl(I).cust_po_number
, p_line_tbl(I).deliver_to_org_id
, p_line_tbl(I).freight_terms_code
, p_line_tbl(I).header_id
, p_line_tbl(I).min_release_quantity
, p_line_tbl(I).max_release_quantity
, p_line_tbl(I).inventory_item_id
, p_line_tbl(I).invoice_to_org_id
, p_line_tbl(I).invoicing_rule_id
, p_line_tbl(I).ordered_item_id
, p_line_tbl(I).item_identifier_type
, p_line_tbl(I).ordered_item
, p_line_tbl(I).item_type_code
, p_line_tbl(I).last_updated_by
, p_line_tbl(I).last_update_date
, p_line_tbl(I).last_update_login
, p_line_tbl(I).line_id
, p_line_tbl(I).line_number
, p_line_tbl(I).payment_term_id
, p_line_tbl(I).preferred_grade --OPM 02/JUN/00
, p_line_tbl(I).price_list_id
, p_line_tbl(I).program_application_id
, p_line_tbl(I).program_id
, p_line_tbl(I).program_update_date
, p_line_tbl(I).request_id
, p_line_tbl(I).salesrep_id
, p_line_tbl(I).shipping_method_code
, p_line_tbl(I).ship_from_org_id
, p_line_tbl(I).ship_to_org_id
, p_line_tbl(I).shipping_instructions
, p_line_tbl(I).packing_instructions
, p_line_tbl(I).START_DATE_ACTIVE
, p_line_tbl(I).END_DATE_ACTIVE
, p_line_tbl(I).MAX_RELEASE_AMOUNT
, p_line_tbl(I).MIN_RELEASE_AMOUNT
, p_line_tbl(I).BLANKET_MAX_AMOUNT
, p_line_tbl(I).BLANKET_MIN_AMOUNT
, p_line_tbl(I).BLANKET_MAX_QUANTITY
, p_line_tbl(I).BLANKET_MIN_QUANTITY
, p_line_tbl(I).OVERRIDE_BLANKET_CONTROLS_FLAG
, p_line_tbl(I).OVERRIDE_RELEASE_CONTROLS_FLAG
, p_line_tbl(I).ENFORCE_PRICE_LIST_FLAG
, p_line_tbl(I).enforce_ship_to_flag
, p_line_tbl(I).enforce_invoice_to_flag
, p_line_tbl(I).enforce_freight_term_flag
, p_line_tbl(I).enforce_shipping_method_flag
, p_line_tbl(I).enforce_payment_term_flag
, p_line_tbl(I).enforce_accounting_rule_flag
, p_line_tbl(I).enforce_invoicing_rule_flag
, p_line_tbl(I).ORDER_QUANTITY_UOM
, p_line_tbl(I).RELEASED_QUANTITY
, p_line_tbl(I).FULFILLED_QUANTITY
, p_line_tbl(I).RETURNED_QUANTITY
, p_line_tbl(I).ORDER_NUMBER
, p_line_tbl(I).RELEASED_AMOUNT
, p_line_tbl(I).FULFILLED_AMOUNT
, p_line_tbl(I).RETURNED_AMOUNT
, nvl(FND_GLOBAL.RESP_ID,-1)
, 'UPDATE'
, sysdate
, nvl(FND_GLOBAL.USER_ID, -1)
, p_header_rec.version_number
, 'B'
, p_line_tbl(I).TRANSACTION_PHASE_CODE -- hashraf ... start of pack J
, p_line_tbl(I).source_document_version_number
, p_version_flag
, p_phase_change_flag
, p_line_tbl(I).modifier_list_line_id
);
oe_debug_pub.ADD('After inserting blanket history', 1);
END Insert_History_Records;
Select 1 into p_result
from oe_order_lines
where blanket_number = l_blanket_number
and blanket_line_number = l_blanket_line_number
and line_category_code = 'RETURN'
and rownum = 1;
Select 1 into p_result
from oe_order_lines
where blanket_number = l_blanket_number
and line_category_code = 'RETURN'
and rownum = 1;
Select 1 into p_result
from oe_order_lines
where blanket_number = l_blanket_number
and blanket_line_number = l_blanket_line_number
and rownum = 1;
Select 1 into p_result
from oe_order_lines
where blanket_number = l_blanket_number
and rownum = 1;
Select 1 into p_result
from oe_order_headers_all
where blanket_number = l_blanket_number
and rownum = 1;
Select 1 into p_result
from oe_order_headers_all
where blanket_number = l_blanket_number
and rownum = 1;
Select 1 into p_result
from oe_order_lines
where blanket_number = l_blanket_number
and blanket_line_number = l_blanket_line_number
and open_flag = 'Y'
and rownum = 1;
Select 1 into p_result
from oe_order_lines
where blanket_number = l_blanket_number
and open_flag = 'Y'
and rownum = 1;
Select 1 into p_result
from oe_order_headers_all
where blanket_number = l_blanket_number
and open_flag = 'Y'
and rownum = 1;
Select 1 into p_result
from oe_order_headers_all
where blanket_number = l_blanket_number
and open_flag = 'Y'
and rownum = 1;
l_rowtype_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
l_rowtype_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
l_rowtype_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
l_rowtype_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
x_rowtype_rec.LAST_UPDATED_BY := p_header_rec.LAST_UPDATED_BY;
x_rowtype_rec.LAST_UPDATE_DATE := p_header_rec.LAST_UPDATE_DATE;
x_rowtype_rec.LAST_UPDATE_LOGIN := p_header_rec.LAST_UPDATE_LOGIN;
x_rowtype_rec.PROGRAM_UPDATE_DATE := p_header_rec.PROGRAM_UPDATE_DATE;
l_update_inventory_item VARCHAR2(1) := FND_API.G_FALSE;
SELECT u.cust_acct_site_id,s.cust_account_id
INTO l_address_id,
l_cust_id
FROM HZ_CUST_SITE_USES u,HZ_CUST_ACCT_SITES s
WHERE u.cust_acct_site_id = s.cust_acct_site_id
AND u.site_use_id = p_x_line_rec.ship_to_org_id
AND u.site_use_code = 'SHIP_TO';
l_update_inventory_item := FND_API.G_TRUE;
select sold_to_org_id,
line_id,
ship_to_org_id,
deliver_to_org_id,
invoice_to_org_id
from
oe_blanket_lines_all where header_id = p_header_id;
SELECT 'VALID' INTO l_dummy
FROM oe_ship_to_orgs_v
WHERE customer_id = p_sold_to_org_id
AND site_use_id = mc1x.ship_to_org_id
AND status = 'A'
and address_status='A';
SELECT 'VALID' INTO l_dummy
FROM oe_deliver_to_orgs_v
WHERE customer_id = p_sold_to_org_id
AND site_use_id = mc1x.deliver_to_org_id
AND status = 'A'
and address_status='A'; --2752321
Select 'VALID' Into l_dummy
from oe_invoice_to_orgs_v
Where customer_id = p_sold_to_org_id
AND site_use_id = mc1x.invoice_to_org_id;
update oe_blanket_lines_all set sold_to_org_id = p_sold_to_org_id
where header_id = p_header_id;