[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
4. If user is valid, insert into PO_REQUISITION_INTERFACE and
OE_DROP_SHIP_SOURCES tables
This program will be called as a concurrent program or from the
workflow.
-----------------------------------------------------------------*/
Procedure Purchase_Release
( p_api_version_number IN NUMBER
, p_drop_ship_tbl IN Drop_Ship_Tbl_Type
, p_mode IN VARCHAR2 := G_MODE_ONLINE
, x_drop_ship_tbl OUT NOCOPY Drop_Ship_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
SELECT 'Yes '
FROM hr_employees_current_v
WHERE employee_id = x_employee_id;
SELECT payment_type_code
FROM oe_order_headers
WHERE header_id = x_header_id;
SELECT REQUEST_DATE
FROM OE_ORDER_LINES
WHERE LINE_ID = v_line_id;
SELECT item_type_code, ato_line_id
INTO l_item_type_code, l_ato_line_id
FROM oe_order_lines
WHERE line_id = l_drop_ship_line_rec.line_id;
SELECT nvl(ORDER_SOURCE_ID,0),nvl(SOURCE_DOCUMENT_TYPE_ID,0),org_id
INTO l_order_source_id,l_source_document_type_id,l_ou_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = l_drop_ship_line_rec.header_id;
SELECT PURCHASING_ENABLED_FLAG
INTO item_purchase_enabled
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_drop_ship_line_rec.inventory_item_id
AND ORGANIZATION_ID = l_drop_ship_line_rec.ship_from_org_id;
SELECT shippable_flag
INTO l_shippable_flag
FROM oe_order_lines
WHERE line_id = l_drop_ship_line_rec.line_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
select 'Y' -- added begin block for bug 7433481
into l_exists
from oe_drop_ship_sources
where line_id=l_drop_ship_line_rec.line_id;
SELECT oe_drop_ship_source_s.nextval
INTO l_drop_ship_source_id
FROM dual;
/* insert into po_requisition_interface table */
IF l_debug_level > 0 THEN
oe_debug_pub.add('Before inserting into Requisitions Interface Table' , 1 ) ;
Insert_Into_Po_Req_Interface
(p_drop_ship_line_rec => l_drop_ship_line_rec
,x_return_status => l_return_status
,p_user_id => l_user_id
,p_resp_id => l_resp_id
,p_application_id => l_application_id
,p_org_id => l_org_id
,p_login_id => l_login_id
,p_drop_ship_source_id => l_drop_ship_source_id
);
oe_debug_pub.add('After inserting ',1);
/* insert into oe_drop_ship_sources table */
IF l_debug_level > 0 THEN
oe_debug_pub.add('Inserting Dropship Source Record',1);
Insert_Drop_Ship_Source
( p_drop_ship_line_rec => l_drop_ship_line_rec
,x_return_status => l_return_status
,p_user_id => l_user_id
,p_resp_id => l_resp_id
,p_application_id => l_application_id
,p_org_id => l_org_id
,p_login_id => l_login_id
,p_drop_ship_source_id => l_drop_ship_source_id
);
oe_debug_pub.add('Inserted into Dropship Source Record', 1);
PROCEDURE : Insert_Into_Po_Req_Interface
DESCRIPTION:
-----------------------------------------------------------------*/
Procedure Insert_Into_Po_Req_Interface
(p_drop_ship_line_rec IN Drop_Ship_Line_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,p_user_id IN NUMBER
,p_resp_id IN NUMBER
,p_application_id IN NUMBER
,p_org_id IN NUMBER
,p_login_id IN NUMBER
,p_drop_ship_source_id IN NUMBER
)
IS
l_destination_type_code VARCHAR2(25) ;
oe_debug_pub.add( 'BEFORE INSERTING RECORDS INTO PO REQUISITIONS INTERFACE ' , 1 ) ;
SELECT msi.stock_enabled_flag ,revision_qty_control_code
INTO l_stock_enabled_flag, l_revision_control_code
FROM mtl_system_items msi,inv_organization_info_v org
WHERE msi.inventory_item_id = p_drop_ship_line_rec.inventory_item_id
AND org.organization_id = msi.organization_id
AND org.organization_id = p_drop_ship_line_rec.ship_from_org_id;
select MAX(revision)
into l_item_revision
from mtl_item_revisions mir,
mtl_system_items mti
where mir.inventory_item_id = mti.inventory_item_id
and mir.organization_id = mti.organization_id
and mti.inventory_item_id = p_drop_ship_line_rec.inventory_item_id
and mti.organization_id = p_drop_ship_line_rec.ship_from_org_id
and mti.REVISION_QTY_CONTROL_CODE =2 /* Means item is under revision control */
and mir.effectivity_date < SYSDATE+1
and mir.effectivity_date =
( select MAX(mir1.effectivity_date)
from mtl_item_revisions mir1
where mir1.inventory_item_id = mir.inventory_item_id
and mir1.organization_id = mir.organization_id
and mir1.effectivity_date < SYSDATE+1
);
oe_debug_pub.add( '------------INSERTING VALUES: ----------' , 1 ) ;
oe_debug_pub.add( 'LAST_UPDATED_BY : '||P_USER_ID , 1 ) ;
oe_debug_pub.add( 'LAST_UPDATE_LOGIN : '||P_LOGIN_ID , 1 ) ;
SELECT operating_unit
INTO l_ou_org_id
FROM inv_organization_info_v
WHERE organization_id = p_drop_ship_line_rec.ship_from_org_id;
INSERT INTO po_requisitions_interface_all
(interface_source_code,
destination_organization_id,
deliver_to_location_id,
deliver_to_requestor_id,
need_by_date,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
destination_type_code,
quantity,
uom_code,
secondary_quantity, -- OPM
secondary_uom_code, -- OPM
preferred_grade, -- OPM
authorization_status,
preparer_id,
item_id,
charge_account_id,
accrual_account_id, -- OPM
interface_source_line_id,
source_type_code,
unit_price,
project_id,
task_id,
end_item_unit_number,
project_accounting_context,
item_revision,
suggested_buyer_id, -- Fix for bug 2122969
item_description,
org_id)
VALUES
(
l_source_code,
p_drop_ship_line_rec.ship_from_org_id,
p_drop_ship_line_rec.deliver_to_location_id,
p_drop_ship_line_rec.employee_id,
l_schedule_ship_date,
p_user_id,
SYSDATE,
p_login_id,
SYSDATE,
p_user_id,
l_destination_type_code,
p_drop_ship_line_rec.open_quantity,
p_drop_ship_line_rec.uom_code,
p_drop_ship_line_rec.open_quantity2, -- OPM
p_drop_ship_line_rec.uom2_code, -- OPM
p_drop_ship_line_rec.preferred_grade, -- OPM
l_authorization_status,
p_drop_ship_line_rec.employee_id,
p_drop_ship_line_rec.inventory_item_id,
p_drop_ship_line_rec.charge_account_id,
p_drop_ship_line_rec.accrual_account_id, -- OPM
p_drop_ship_source_id,
'VENDOR',
NULL,
decode(p_drop_ship_line_rec.project_id, -1, NULL, p_drop_ship_line_rec.project_id),
decode(p_drop_ship_line_rec.task_id, -1, NULL, p_drop_ship_line_rec.task_id),
decode(p_drop_ship_line_rec.end_item_unit_number, '-1', NULL, p_drop_ship_line_rec.end_item_unit_number),
l_project_accounting_context,
l_item_revision,
decode(nvl(l_prof_value,0),1,p_drop_ship_line_rec.employee_id,NULL), -- Modified Fix for bug 2122969 through bug 2172019
p_drop_ship_line_rec.item_description,
l_ou_org_id);
oe_debug_pub.add( 'END OF INSERT_INTO_PO_REQ_INTERFACE' , 1 ) ;
, 'Insert_Into_Po_Req_Interface'
);
END Insert_Into_Po_Req_Interface;
PROCEDURE : Insert_Drop_Ship_Source
DESCRIPTION:
-----------------------------------------------------------------*/
Procedure Insert_Drop_Ship_Source
(p_drop_ship_line_rec IN Drop_Ship_Line_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,p_user_id IN NUMBER
,p_resp_id IN NUMBER
,p_application_id IN NUMBER
,p_org_id IN NUMBER
,p_login_id IN NUMBER
,p_drop_ship_source_id IN NUMBER
)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add( 'START OF INSERT_DROP_SHIP_SOURCE' , 1 ) ;
INSERT INTO oe_drop_ship_sources
(drop_ship_source_id,
header_id,
line_id,
destination_organization_id,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
org_id
)
VALUES
(p_drop_ship_source_id,
p_drop_ship_line_rec.header_id,
p_drop_ship_line_rec.line_id,
p_drop_ship_line_rec.ship_from_org_id,
p_user_id,
SYSDATE,
p_login_id,
SYSDATE,
p_user_id,
p_org_id
);
oe_debug_pub.add( 'END OF INSERT_DROP_SHIP_SOURCE' , 1 ) ;
, 'Insert_Drop_Ship_Source'
);
END Insert_Drop_Ship_Source;
SELECT /* MOAC_SQL_CHANGE */ sl.item_type_code, 'STANDARD',
sh.order_number, sl.line_number,
sl.header_id, sl.line_id, sl.ship_from_org_id,
nvl(sl.project_id, -1), nvl(sl.task_id, -1),
nvl(sl.end_item_unit_number,'-1'),fu.user_name,
nvl(fu.employee_id, -99), sl.request_date,
sl.schedule_ship_date,
sl.ordered_quantity,
sl.ordered_quantity2, -- OPM
sl.ordered_quantity_uom2, -- OPM
sl.preferred_grade, -- OPM
sl.inventory_item_id,
sl.source_type_code, decode(msi.inventory_asset_flag,
'Y', mp.material_account, nvl(msi.expense_account,
mp.expense_account)), nvl(pla.location_id, -1)
FROM po_location_associations pla, oe_order_lines_all sl,
mtl_parameters mp, fnd_user fu, mtl_system_items msi,
oe_order_headers sh
WHERE sl.header_id = sh.header_id
AND sl.line_id = p_line_id
AND fu.user_id = sh.created_by
AND sl.source_type_code is not null
AND sl.ship_from_org_id is not null
AND sl.inventory_item_id = msi.inventory_item_id
AND sl.ship_from_org_id = msi.organization_id
AND mp.organization_id = msi.organization_id
AND sl.ship_to_org_id = pla.site_use_id(+)
AND sl.source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL;
SELECT loc.location_id
INTO l_ship_to_location_id
FROM hz_cust_site_uses_all site_uses,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND site_uses.site_use_code = 'SHIP_TO'
AND site_uses.site_use_id = p_site_use_id;
l_sales_order_updated_date PO_TBL_DATE := PO_TBL_DATE();
SELECT requisition_header_id,po_header_id,
requisition_line_id,po_line_id,
line_location_id,po_release_id,drop_ship_source_id
INTO l_requisition_header_id,l_pur_header_id,
l_requisition_line_id,l_pur_line_id,
l_line_loc_id,l_pur_release_id,l_drop_ship_id
FROM oe_drop_ship_sources
WHERE line_id = p_request_tbl(I).entity_id;
l_sales_order_updated_date.extend;
l_sales_order_updated_date(l_count) := NULL;
IF p_request_tbl(I).param15 = 'UPDATE' THEN
IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param1
,p_request_tbl(I).param8) THEN
IF l_req_created = 'N' THEN
l_ds_quantity := p_request_tbl(I).param8;
l_sales_order_updated_date(l_count) := sysdate;
SELECT process_flag
INTO l_process_flag
FROM po_requisitions_interface_all
WHERE interface_source_line_id = l_drop_ship_id
AND interface_source_code = l_source_code;
SELECT RTRIM(line_number || '.' ||
shipment_number || '.' ||
option_number || '.' ||
component_number || '.' ||
service_number, '.')
INTO l_line_num
FROM oe_order_lines_all
WHERE line_id = p_request_tbl(I).entity_id;
UPDATE po_requisitions_interface_all
SET quantity = nvl(l_ds_quantity,quantity),
uom_code = nvl(l_ds_ordered_quantity_uom,
uom_code),
secondary_quantity = nvl(l_ds_ordered_quantity2,
secondary_quantity),
secondary_uom_code = nvl(l_ds_ordered_quantity_uom2,
secondary_uom_code),
preferred_grade = nvl(l_ds_preferred_grade,
preferred_grade),
need_by_date = nvl(l_ds_schedule_ship_date,
need_by_date),
deliver_to_location_id = nvl(l_ds_ship_to_location_id,
deliver_to_location_id)
WHERE interface_source_line_id = l_drop_ship_id
AND interface_source_code = l_source_code;
END IF; -- Update
SELECT process_flag
INTO l_process_flag
FROM po_requisitions_interface_all
WHERE interface_source_line_id = l_drop_ship_id
AND interface_source_code = l_source_code ;
SELECT RTRIM(line_number || '.' ||
shipment_number || '.' ||
option_number || '.' ||
component_number || '.' ||
service_number, '.')
INTO l_line_num
FROM oe_order_lines_all
WHERE line_id = p_request_tbl(I).entity_id;
deletion of the config line. Hence the above select would fail.
If line has been deleted, then its OK to have the line number NULL */
Null;
DELETE FROM po_requisitions_interface_all
WHERE interface_source_line_id = l_drop_ship_id
AND interface_source_code = l_source_code;
OE_DEBUG_PUB.Add('Before Calling Update_Req_PO...',2) ;
PO_OM_INTEGRATION_GRP.Update_Req_PO
(p_api_version => 1.0
,p_req_header_id => l_req_header_id
,p_req_line_id => l_req_line_id
,p_po_header_id => l_po_header_id
,p_po_release_id => l_po_release_id
,p_po_line_id => l_po_line_id
,p_po_line_location_id => l_po_line_location_id
,p_quantity => l_quantity
,p_secondary_quantity => l_secondary_quantity
,p_need_by_date => l_need_by_date
,p_ship_to_location_id => l_ship_to_location
,p_sales_order_update_date => l_sales_order_updated_date
,p_preferred_grade => l_grade -- INVCONV
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
OE_DEBUG_PUB.Add(' After Calling update_req_po...'||l_return_status,2) ;
oe_debug_pub.add('Messages from Update PO;'||l_msg_data,1 ) ;