OM Stagging Tables:
----------------------
create table xxcus.cb9_om_orders_tab
(
order_number varchar2(100),
customer_name varchar2(100),
bill_to varchar2(100),
ship_to varchar2(100),
order_type varchar2(100),
price_list_name varchar2(100),
salesrep varchar2(100),
process_flag varchar2(1),
error_message varchar2(1000),
creation_date date,
created_by number,
last_update_date date,
last_updated_by number
);
create table xxcus.cb9_om_order_lines_tab
(
order_number varchar2(100),
line_number number,
organization_name varchar2(100),
item varchar2(100),
qty number,
process_flag varchar2(1),
error_message varchar2(1000),
creation_date date,
created_by number,
last_update_date date,
last_updated_by number
);
create synonym cb9_om_orders_tab for xxcus.cb9_om_orders_tab;
create synonym cb9_om_order_lines_tab for xxcus.cb9_om_order_lines_tab;
OM API Interface:
-------------------
CREATE OR REPLACE package body APPS.ee_om_ord_cre_pkg
is
procedure main(x_errbuf out varchar2,x_retcode out varchar2)
is
cursor cur_hea
is
select * from ee_om_order_hea_stg;
cursor cur_line(p_order_number varchar2)
is
select * from ee_om_order_line_stg
where order_number = p_order_number;
v_Header_Rec_Type oe_order_pub.Header_Rec_Type;
v_Header_Val_Rec_Type oe_order_pub.Header_Val_Rec_Type;
v_Header_Adj_Tbl_Type oe_order_pub.Header_Adj_Tbl_Type;
v_Header_Adj_Val_Tbl_Type oe_order_pub.Header_Adj_Val_Tbl_Type;
v_Header_Price_Att_Tbl_Type oe_order_pub.Header_Price_Att_Tbl_Type;
v_Header_Adj_Att_Tbl_Type oe_order_pub.Header_Adj_Att_Tbl_Type;
v_Header_Adj_Assoc_Tbl_Type oe_order_pub.Header_Adj_Assoc_Tbl_Type;
v_Header_Scredit_Tbl_Type oe_order_pub.Header_Scredit_Tbl_Type;
v_Header_Scredit_Val_Tbl_Type oe_order_pub.Header_Scredit_Val_Tbl_Type;
v_Line_Tbl_Type oe_order_pub.Line_Tbl_Type;
v_Line_Val_Tbl_Type oe_order_pub.Line_Val_Tbl_Type;
v_Line_Adj_Tbl_Type oe_order_pub.Line_Adj_Tbl_Type;
v_Line_Adj_Val_Tbl_Type oe_order_pub.Line_Adj_Val_Tbl_Type;
v_Line_Price_Att_Tbl_Type oe_order_pub.Line_Price_Att_Tbl_Type;
v_Line_Adj_Att_Tbl_Type oe_order_pub.Line_Adj_Att_Tbl_Type;
v_Line_Adj_Assoc_Tbl_Type oe_order_pub.Line_Adj_Assoc_Tbl_Type;
v_Line_Scredit_Tbl_Type oe_order_pub.Line_Scredit_Tbl_Type;
v_Line_Scredit_Val_Tbl_Type oe_order_pub.Line_Scredit_Val_Tbl_Type;
v_Lot_Serial_Tbl_Type oe_order_pub.Lot_Serial_Tbl_Type;
v_Lot_Serial_Val_Tbl_Type oe_order_pub.Lot_Serial_Val_Tbl_Type;
v_Request_Tbl_Type oe_order_pub.Request_Tbl_Type;
v_process_flag varchar2(1);
v_error_message varchar2(1000);
v_count number;
v_order_exist exception;
V_API_VERSION_NUMBER number := 1.0;
v_return_status varchar2(1);
v_msg_count number;
v_msg_data varchar2(1000);
--v_debug_file oe_msg_pub.G_FILE;
begin
-- v_debug_file := 'OM_DEBUG.sql';
for rec_hea in cur_hea
loop -- <AA>
v_Header_Rec_Type := oe_order_pub.G_MISS_HEADER_REC;
v_process_flag := 'S';
v_error_message := null;
-- v_Header_Rec_Type.flow_status_code := 'BOOKED';
begin
select organization_id
into v_Header_Rec_Type.org_id
from hr_operating_units
where name=rec_hea.ou_name;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid OU -> '||rec_hea.ou_name;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in OU -> '||rec_hea.ou_name||' '||sqlerrm;
end;
-- order type
begin
select transaction_type_id
into v_Header_Rec_Type.order_type_id
from oe_transaction_types_tl
where name = rec_hea.order_type
and language = userenv('LANG');
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid Trans type -> '||rec_hea.order_type;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in Trans type -> '||rec_hea.order_type||' '||sqlerrm;
end;
-- check order is already exists
select count(1)
into v_count
from oe_order_headers_all
where order_type_id = v_Header_Rec_Type.order_type_id
and order_number = rec_hea.order_number;
if v_count >0
then
raise v_order_exist;
end if;
-- Validate customer
begin
select cust_account_id
into v_Header_Rec_Type.sold_to_org_id
from hz_parties hp,
hz_cust_accounts_all hca
where party_name = rec_hea.customer_name
and hp.party_id = hca.party_id;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid customer -> '||rec_hea.customer_name;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in customer -> '||rec_hea.customer_name||' '||sqlerrm;
end;
-- Vlaidate price list
begin
select list_header_id
into v_Header_Rec_Type.price_list_id
from qp_list_headers
where name = rec_hea.price_list
and active_flag = 'Y';
-- v_Header_Rec_Type.price_list_id := 7987;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid price list -> '||rec_hea.price_list;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in price list -> '||rec_hea.price_list||' '||sqlerrm;
end;
v_Header_Rec_Type.operation := 'CREATE';
v_Header_Rec_Type.attribute1 := rec_hea.order_number;
v_count := 0;
for rec_line in cur_line(rec_hea.order_number)
loop-- <BB>
v_count := v_count + 1;
v_Line_Tbl_Type(v_count) := oe_order_pub.GET_G_MISS_LINE_REC;
begin
select organization_id
into v_Line_Tbl_Type(v_count).ship_from_org_id
from org_organization_definitions
where ORGANIZATION_NAME = rec_line.ship_from_organization;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid Organization -> '||rec_line.ship_from_organization;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in Organization -> '||rec_line.ship_from_organization||' '||sqlerrm;
end;
-- validate item
begin
select inventory_item_id
into v_Line_Tbl_Type(v_count).inventory_item_id
from mtl_system_items_b
where organization_id = v_Line_Tbl_Type(v_count).ship_from_org_id
and segment1 = rec_line.item;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid Item -> '||rec_line.item;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in Item -> '||rec_line.item||' '||sqlerrm;
end;
if rec_line.quantity is null or rec_line.price is null
then
v_process_flag := 'E';
v_error_message:= 'Qty or price is null';
end if;
v_Line_Tbl_Type(v_count).ordered_quantity := rec_line.quantity;
v_Line_Tbl_Type(v_count).unit_list_price := rec_line.price;
v_Line_Tbl_Type(v_count).line_number := rec_line.line_number;
v_Line_Tbl_Type(v_count).operation := 'CREATE';
end loop;-- <BB>
v_count := 0;
v_count := v_count + 1;
v_Request_Tbl_Type := oe_order_pub.G_MISS_REQUEST_TBL;
v_Request_Tbl_Type(v_count).Entity_code := 'ORDER';
if v_process_flag = 'S'
then
oe_order_pub.process_order
( p_api_version_number => v_api_version_number
, p_init_msg_list => 'T'-- FND_API.G_TRUE
, p_return_values => FND_API.G_FALSE
, p_action_commit => FND_API.G_FALSE
, x_return_status => v_return_status
, x_msg_count => v_msg_count
, x_msg_data => v_msg_data
, p_header_rec => v_Header_Rec_Type
, p_old_header_val_rec => v_Header_Val_Rec_Type
, p_Header_Adj_tbl => v_Header_Adj_Tbl_Type
, p_old_Header_Adj_tbl => v_Header_Adj_Tbl_Type
, p_Header_Adj_val_tbl => v_Header_Adj_Val_Tbl_Type
, p_old_Header_Adj_val_tbl => v_Header_Adj_Val_Tbl_Type
, p_Header_price_Att_tbl => v_Header_Price_Att_Tbl_Type
, p_old_Header_Price_Att_tbl => v_Header_Price_Att_Tbl_Type
, p_Header_Adj_Att_tbl => v_Header_Adj_Att_Tbl_Type
, p_old_Header_Adj_Att_tbl => v_Header_Adj_Att_Tbl_Type
, p_Header_Adj_Assoc_tbl => v_Header_Adj_Assoc_Tbl_Type
, p_old_Header_Adj_Assoc_tbl => v_Header_Adj_Assoc_Tbl_Type
, p_Header_Scredit_tbl => v_Header_Scredit_Tbl_Type
, p_old_Header_Scredit_tbl => v_Header_Scredit_Tbl_Type
, p_Header_Scredit_val_tbl => v_Header_Scredit_Val_Tbl_Type
, p_old_Header_Scredit_val_tbl => v_Header_Scredit_Val_Tbl_Type
, p_line_tbl => v_Line_Tbl_Type
, p_old_line_tbl => v_Line_Tbl_Type
, p_line_val_tbl => v_Line_Val_Tbl_Type
, p_old_line_val_tbl => v_Line_Val_Tbl_Type
, p_Line_Adj_tbl => v_Line_Adj_Tbl_Type
, p_old_Line_Adj_tbl => v_Line_Adj_Tbl_Type
, p_Line_Adj_val_tbl => v_Line_Adj_Val_Tbl_Type
, p_old_Line_Adj_val_tbl => v_Line_Adj_Val_Tbl_Type
, p_Line_price_Att_tbl => v_Line_Price_Att_Tbl_Type
, p_old_Line_Price_Att_tbl => v_Line_Price_Att_Tbl_Type
, p_Line_Adj_Att_tbl => v_Line_Adj_Att_Tbl_Type
, p_old_Line_Adj_Att_tbl => v_Line_Adj_Att_Tbl_Type
, p_Line_Adj_Assoc_tbl => v_Line_Adj_Assoc_Tbl_Type
, p_old_Line_Adj_Assoc_tbl => v_Line_Adj_Assoc_Tbl_Type
, p_Line_Scredit_tbl => v_Line_Scredit_Tbl_Type
, p_old_Line_Scredit_tbl => v_Line_Scredit_Tbl_Type
, p_Line_Scredit_val_tbl => v_Line_Scredit_Val_Tbl_Type
, p_old_Line_Scredit_val_tbl => v_Line_Scredit_Val_Tbl_Type
, p_Lot_Serial_tbl => v_Lot_Serial_Tbl_Type
, p_old_Lot_Serial_tbl => v_Lot_Serial_Tbl_Type
, p_Lot_Serial_val_tbl => v_Lot_Serial_Val_Tbl_Type
, p_old_Lot_Serial_val_tbl => v_Lot_Serial_Val_Tbl_Type
, p_action_request_tbl => v_Request_Tbl_Type
-- out parameters
, x_header_rec => v_Header_Rec_Type
, x_header_val_rec => v_Header_Val_Rec_Type
, x_Header_Adj_tbl => v_Header_Adj_Tbl_Type
, x_Header_Adj_val_tbl => v_Header_Adj_Val_Tbl_Type
, x_Header_price_Att_tbl => v_Header_Price_Att_Tbl_Type
, x_Header_Adj_Att_tbl => v_Header_Adj_Att_Tbl_Type
, x_Header_Adj_Assoc_tbl => v_Header_Adj_Assoc_Tbl_Type
, x_Header_Scredit_tbl => v_Header_Scredit_Tbl_Type
, x_Header_Scredit_val_tbl => v_Header_Scredit_Val_Tbl_Type
, x_line_tbl => v_Line_Tbl_Type
, x_line_val_tbl => v_Line_Val_Tbl_Type
, x_Line_Adj_tbl => v_Line_Adj_Tbl_Type
, x_Line_Adj_val_tbl => v_Line_Adj_Val_Tbl_Type
, x_Line_price_Att_tbl => v_Line_Price_Att_Tbl_Type
, x_Line_Adj_Att_tbl => v_Line_Adj_Att_Tbl_Type
, x_Line_Adj_Assoc_tbl => v_Line_Adj_Assoc_Tbl_Type
, x_Line_Scredit_tbl => v_Line_Scredit_Tbl_Type
, x_Line_Scredit_val_tbl => v_Line_Scredit_Val_Tbl_Type
, x_Lot_Serial_tbl => v_Lot_Serial_Tbl_Type
, x_Lot_Serial_val_tbl => v_Lot_Serial_Val_Tbl_Type
, x_action_request_tbl => v_Request_Tbl_Type
--For bug 3390458
, p_rtrim_data => 'N'
);
dis_log('Status -> '||v_return_status);
dis_log('error count -> '||v_msg_count);
if v_return_status <> 'S'
then
v_process_flag := 'E';
for i in 1..v_msg_count
loop
dis_log(oe_msg_pub.get(i));
V_error_message := oe_msg_pub.get(i);
end loop;
end if;
end if;
-- dis_log('OM Debug file:'||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
update ee_om_order_hea_stg
set process_flag = v_process_flag
,error_message = V_error_message
where order_number = rec_hea.order_number;
end loop;-- <AA>
exception
when others then
dis_log('Exception in main proc -> '||sqlerrm);
end;
end ee_om_ord_cre_pkg;
/
----------------------
create table xxcus.cb9_om_orders_tab
(
order_number varchar2(100),
customer_name varchar2(100),
bill_to varchar2(100),
ship_to varchar2(100),
order_type varchar2(100),
price_list_name varchar2(100),
salesrep varchar2(100),
process_flag varchar2(1),
error_message varchar2(1000),
creation_date date,
created_by number,
last_update_date date,
last_updated_by number
);
create table xxcus.cb9_om_order_lines_tab
(
order_number varchar2(100),
line_number number,
organization_name varchar2(100),
item varchar2(100),
qty number,
process_flag varchar2(1),
error_message varchar2(1000),
creation_date date,
created_by number,
last_update_date date,
last_updated_by number
);
create synonym cb9_om_orders_tab for xxcus.cb9_om_orders_tab;
create synonym cb9_om_order_lines_tab for xxcus.cb9_om_order_lines_tab;
OM API Interface:
-------------------
CREATE OR REPLACE package body APPS.ee_om_ord_cre_pkg
is
procedure main(x_errbuf out varchar2,x_retcode out varchar2)
is
cursor cur_hea
is
select * from ee_om_order_hea_stg;
cursor cur_line(p_order_number varchar2)
is
select * from ee_om_order_line_stg
where order_number = p_order_number;
v_Header_Rec_Type oe_order_pub.Header_Rec_Type;
v_Header_Val_Rec_Type oe_order_pub.Header_Val_Rec_Type;
v_Header_Adj_Tbl_Type oe_order_pub.Header_Adj_Tbl_Type;
v_Header_Adj_Val_Tbl_Type oe_order_pub.Header_Adj_Val_Tbl_Type;
v_Header_Price_Att_Tbl_Type oe_order_pub.Header_Price_Att_Tbl_Type;
v_Header_Adj_Att_Tbl_Type oe_order_pub.Header_Adj_Att_Tbl_Type;
v_Header_Adj_Assoc_Tbl_Type oe_order_pub.Header_Adj_Assoc_Tbl_Type;
v_Header_Scredit_Tbl_Type oe_order_pub.Header_Scredit_Tbl_Type;
v_Header_Scredit_Val_Tbl_Type oe_order_pub.Header_Scredit_Val_Tbl_Type;
v_Line_Tbl_Type oe_order_pub.Line_Tbl_Type;
v_Line_Val_Tbl_Type oe_order_pub.Line_Val_Tbl_Type;
v_Line_Adj_Tbl_Type oe_order_pub.Line_Adj_Tbl_Type;
v_Line_Adj_Val_Tbl_Type oe_order_pub.Line_Adj_Val_Tbl_Type;
v_Line_Price_Att_Tbl_Type oe_order_pub.Line_Price_Att_Tbl_Type;
v_Line_Adj_Att_Tbl_Type oe_order_pub.Line_Adj_Att_Tbl_Type;
v_Line_Adj_Assoc_Tbl_Type oe_order_pub.Line_Adj_Assoc_Tbl_Type;
v_Line_Scredit_Tbl_Type oe_order_pub.Line_Scredit_Tbl_Type;
v_Line_Scredit_Val_Tbl_Type oe_order_pub.Line_Scredit_Val_Tbl_Type;
v_Lot_Serial_Tbl_Type oe_order_pub.Lot_Serial_Tbl_Type;
v_Lot_Serial_Val_Tbl_Type oe_order_pub.Lot_Serial_Val_Tbl_Type;
v_Request_Tbl_Type oe_order_pub.Request_Tbl_Type;
v_process_flag varchar2(1);
v_error_message varchar2(1000);
v_count number;
v_order_exist exception;
V_API_VERSION_NUMBER number := 1.0;
v_return_status varchar2(1);
v_msg_count number;
v_msg_data varchar2(1000);
--v_debug_file oe_msg_pub.G_FILE;
begin
-- v_debug_file := 'OM_DEBUG.sql';
for rec_hea in cur_hea
loop -- <AA>
v_Header_Rec_Type := oe_order_pub.G_MISS_HEADER_REC;
v_process_flag := 'S';
v_error_message := null;
-- v_Header_Rec_Type.flow_status_code := 'BOOKED';
begin
select organization_id
into v_Header_Rec_Type.org_id
from hr_operating_units
where name=rec_hea.ou_name;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid OU -> '||rec_hea.ou_name;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in OU -> '||rec_hea.ou_name||' '||sqlerrm;
end;
-- order type
begin
select transaction_type_id
into v_Header_Rec_Type.order_type_id
from oe_transaction_types_tl
where name = rec_hea.order_type
and language = userenv('LANG');
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid Trans type -> '||rec_hea.order_type;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in Trans type -> '||rec_hea.order_type||' '||sqlerrm;
end;
-- check order is already exists
select count(1)
into v_count
from oe_order_headers_all
where order_type_id = v_Header_Rec_Type.order_type_id
and order_number = rec_hea.order_number;
if v_count >0
then
raise v_order_exist;
end if;
-- Validate customer
begin
select cust_account_id
into v_Header_Rec_Type.sold_to_org_id
from hz_parties hp,
hz_cust_accounts_all hca
where party_name = rec_hea.customer_name
and hp.party_id = hca.party_id;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid customer -> '||rec_hea.customer_name;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in customer -> '||rec_hea.customer_name||' '||sqlerrm;
end;
-- Vlaidate price list
begin
select list_header_id
into v_Header_Rec_Type.price_list_id
from qp_list_headers
where name = rec_hea.price_list
and active_flag = 'Y';
-- v_Header_Rec_Type.price_list_id := 7987;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid price list -> '||rec_hea.price_list;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in price list -> '||rec_hea.price_list||' '||sqlerrm;
end;
v_Header_Rec_Type.operation := 'CREATE';
v_Header_Rec_Type.attribute1 := rec_hea.order_number;
v_count := 0;
for rec_line in cur_line(rec_hea.order_number)
loop-- <BB>
v_count := v_count + 1;
v_Line_Tbl_Type(v_count) := oe_order_pub.GET_G_MISS_LINE_REC;
begin
select organization_id
into v_Line_Tbl_Type(v_count).ship_from_org_id
from org_organization_definitions
where ORGANIZATION_NAME = rec_line.ship_from_organization;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid Organization -> '||rec_line.ship_from_organization;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in Organization -> '||rec_line.ship_from_organization||' '||sqlerrm;
end;
-- validate item
begin
select inventory_item_id
into v_Line_Tbl_Type(v_count).inventory_item_id
from mtl_system_items_b
where organization_id = v_Line_Tbl_Type(v_count).ship_from_org_id
and segment1 = rec_line.item;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message:= 'Invalid Item -> '||rec_line.item;
when others then
v_process_flag := 'E';
v_error_message:= 'exception in Item -> '||rec_line.item||' '||sqlerrm;
end;
if rec_line.quantity is null or rec_line.price is null
then
v_process_flag := 'E';
v_error_message:= 'Qty or price is null';
end if;
v_Line_Tbl_Type(v_count).ordered_quantity := rec_line.quantity;
v_Line_Tbl_Type(v_count).unit_list_price := rec_line.price;
v_Line_Tbl_Type(v_count).line_number := rec_line.line_number;
v_Line_Tbl_Type(v_count).operation := 'CREATE';
end loop;-- <BB>
v_count := 0;
v_count := v_count + 1;
v_Request_Tbl_Type := oe_order_pub.G_MISS_REQUEST_TBL;
v_Request_Tbl_Type(v_count).Entity_code := 'ORDER';
if v_process_flag = 'S'
then
oe_order_pub.process_order
( p_api_version_number => v_api_version_number
, p_init_msg_list => 'T'-- FND_API.G_TRUE
, p_return_values => FND_API.G_FALSE
, p_action_commit => FND_API.G_FALSE
, x_return_status => v_return_status
, x_msg_count => v_msg_count
, x_msg_data => v_msg_data
, p_header_rec => v_Header_Rec_Type
, p_old_header_val_rec => v_Header_Val_Rec_Type
, p_Header_Adj_tbl => v_Header_Adj_Tbl_Type
, p_old_Header_Adj_tbl => v_Header_Adj_Tbl_Type
, p_Header_Adj_val_tbl => v_Header_Adj_Val_Tbl_Type
, p_old_Header_Adj_val_tbl => v_Header_Adj_Val_Tbl_Type
, p_Header_price_Att_tbl => v_Header_Price_Att_Tbl_Type
, p_old_Header_Price_Att_tbl => v_Header_Price_Att_Tbl_Type
, p_Header_Adj_Att_tbl => v_Header_Adj_Att_Tbl_Type
, p_old_Header_Adj_Att_tbl => v_Header_Adj_Att_Tbl_Type
, p_Header_Adj_Assoc_tbl => v_Header_Adj_Assoc_Tbl_Type
, p_old_Header_Adj_Assoc_tbl => v_Header_Adj_Assoc_Tbl_Type
, p_Header_Scredit_tbl => v_Header_Scredit_Tbl_Type
, p_old_Header_Scredit_tbl => v_Header_Scredit_Tbl_Type
, p_Header_Scredit_val_tbl => v_Header_Scredit_Val_Tbl_Type
, p_old_Header_Scredit_val_tbl => v_Header_Scredit_Val_Tbl_Type
, p_line_tbl => v_Line_Tbl_Type
, p_old_line_tbl => v_Line_Tbl_Type
, p_line_val_tbl => v_Line_Val_Tbl_Type
, p_old_line_val_tbl => v_Line_Val_Tbl_Type
, p_Line_Adj_tbl => v_Line_Adj_Tbl_Type
, p_old_Line_Adj_tbl => v_Line_Adj_Tbl_Type
, p_Line_Adj_val_tbl => v_Line_Adj_Val_Tbl_Type
, p_old_Line_Adj_val_tbl => v_Line_Adj_Val_Tbl_Type
, p_Line_price_Att_tbl => v_Line_Price_Att_Tbl_Type
, p_old_Line_Price_Att_tbl => v_Line_Price_Att_Tbl_Type
, p_Line_Adj_Att_tbl => v_Line_Adj_Att_Tbl_Type
, p_old_Line_Adj_Att_tbl => v_Line_Adj_Att_Tbl_Type
, p_Line_Adj_Assoc_tbl => v_Line_Adj_Assoc_Tbl_Type
, p_old_Line_Adj_Assoc_tbl => v_Line_Adj_Assoc_Tbl_Type
, p_Line_Scredit_tbl => v_Line_Scredit_Tbl_Type
, p_old_Line_Scredit_tbl => v_Line_Scredit_Tbl_Type
, p_Line_Scredit_val_tbl => v_Line_Scredit_Val_Tbl_Type
, p_old_Line_Scredit_val_tbl => v_Line_Scredit_Val_Tbl_Type
, p_Lot_Serial_tbl => v_Lot_Serial_Tbl_Type
, p_old_Lot_Serial_tbl => v_Lot_Serial_Tbl_Type
, p_Lot_Serial_val_tbl => v_Lot_Serial_Val_Tbl_Type
, p_old_Lot_Serial_val_tbl => v_Lot_Serial_Val_Tbl_Type
, p_action_request_tbl => v_Request_Tbl_Type
-- out parameters
, x_header_rec => v_Header_Rec_Type
, x_header_val_rec => v_Header_Val_Rec_Type
, x_Header_Adj_tbl => v_Header_Adj_Tbl_Type
, x_Header_Adj_val_tbl => v_Header_Adj_Val_Tbl_Type
, x_Header_price_Att_tbl => v_Header_Price_Att_Tbl_Type
, x_Header_Adj_Att_tbl => v_Header_Adj_Att_Tbl_Type
, x_Header_Adj_Assoc_tbl => v_Header_Adj_Assoc_Tbl_Type
, x_Header_Scredit_tbl => v_Header_Scredit_Tbl_Type
, x_Header_Scredit_val_tbl => v_Header_Scredit_Val_Tbl_Type
, x_line_tbl => v_Line_Tbl_Type
, x_line_val_tbl => v_Line_Val_Tbl_Type
, x_Line_Adj_tbl => v_Line_Adj_Tbl_Type
, x_Line_Adj_val_tbl => v_Line_Adj_Val_Tbl_Type
, x_Line_price_Att_tbl => v_Line_Price_Att_Tbl_Type
, x_Line_Adj_Att_tbl => v_Line_Adj_Att_Tbl_Type
, x_Line_Adj_Assoc_tbl => v_Line_Adj_Assoc_Tbl_Type
, x_Line_Scredit_tbl => v_Line_Scredit_Tbl_Type
, x_Line_Scredit_val_tbl => v_Line_Scredit_Val_Tbl_Type
, x_Lot_Serial_tbl => v_Lot_Serial_Tbl_Type
, x_Lot_Serial_val_tbl => v_Lot_Serial_Val_Tbl_Type
, x_action_request_tbl => v_Request_Tbl_Type
--For bug 3390458
, p_rtrim_data => 'N'
);
dis_log('Status -> '||v_return_status);
dis_log('error count -> '||v_msg_count);
if v_return_status <> 'S'
then
v_process_flag := 'E';
for i in 1..v_msg_count
loop
dis_log(oe_msg_pub.get(i));
V_error_message := oe_msg_pub.get(i);
end loop;
end if;
end if;
-- dis_log('OM Debug file:'||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
update ee_om_order_hea_stg
set process_flag = v_process_flag
,error_message = V_error_message
where order_number = rec_hea.order_number;
end loop;-- <AA>
exception
when others then
dis_log('Exception in main proc -> '||sqlerrm);
end;
end ee_om_ord_cre_pkg;
/
No comments:
Post a Comment