[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| last_reservation_deleted - inform the order line workflow that|
| the last reservation has been deleted for this sales order |
| line. |
| workflow_build_status - to determine if a particular |
| sales order line is at the released phase of the workflow. |
| |
| |
| HISTORY : |
| July 22, 99 James Chiu Initial version |
| 12/18/2000 Renga Kannan |
| Added one utility procedure CTO_DEBUG which |
| will write the CTO debug messages |
| |
| 01/11/2001 Renga Kannan |
| The exception handling in CTO_DEBUG is added |
| so that the cto_debug will not fail in the case |
| of invalid directry specified. This fix is part |
| of bug # 1577006 |
| 08/16/2001 Kiran Konada, fix for bug#1874380 |
| to support ATO item under a PTO |
| item_type_code for an ato item under PTO |
| is 'OPTION' and top_model_line_id will NOT be |
| null, UNLIKE an ato item order, where |
| item_type_code = 'Standard' and |
| top_model_lined_id is null |
| This fix has actually been provided in |
| branched code 115.15.115.3 |
| |
| 06/01/2005 Renga Kannan
| Added NoCopy Hint |
=============================================================================*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_WIP_WORKFLOW_API_PK';
* select ltrim(rtrim(substr(value, instr(value,',',-1,1)+1)))
* into file_dir
* from v$parameter
* where name= 'utl_file_dir';
select ltrim(rtrim(value))
into file_dir
from (select value from v$parameter2
where name='utl_file_dir'
order by rownum desc)
where rownum <2;
select item_type_code, ato_line_id
into v_item_type_code, v_ato_line_id
from oe_order_lines_all
where line_id = order_line_id;
Procedure: last_wo_reservation_deleted
Parameters: order_line_id - order_line_id
x_return_status - standard API output parameter
x_msg_count - "
x_msg_data - "
Description: This callback is used to inform the order line workflow that
the last reservation has been deleted for this sales order
line.
*****************************************************************************/
PROCEDURE last_wo_reservation_deleted(
order_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(40) := 'last_wo_reservation_deleted';
select item_type_code, ato_line_id
into v_item_type_code, v_ato_line_id
from oe_order_lines_all
where line_id = order_line_id;
select count(*) into v_counter
from mtl_reservations
--where demand_source_type_id = 2
where demand_source_type_id =
decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
and demand_source_line_id = order_line_id
and primary_reservation_quantity > 0;
select 1
into v_counter2
from dual
where exists(select 'x'
from wip_flow_schedules
where demand_source_type = 2
and demand_source_line = to_char(order_line_id)); --Bugfix 6330114
CTO_DEBUG('LAST_WO_RESERVATION_DELETED',
'Calling Display_wf_status procedure for order_line_id='||to_char(order_line_id));
oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised expected error.',1);
oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised unexpected error.',1);
oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised others exception: '||sqlerrm,1);
END last_wo_reservation_deleted;
select item_type_code, ato_line_id
into v_item_type_code, v_ato_line_id
from oe_order_lines_all
where line_id = order_line_id;
the last flow schedule has been deleted for this sales order
line.
*****************************************************************************/
PROCEDURE flow_deletion(
order_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(40) := 'flow_deletion';
select item_type_code, ato_line_id
into v_item_type_code, v_ato_line_id
from oe_order_lines_all
where line_id = order_line_id;
select count(*) into v_counter
from mtl_reservations
-- where demand_source_type_id = 2
where demand_source_type_id =
decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
and demand_source_line_id = order_line_id
and primary_reservation_quantity > 0;
select 1
into v_counter2
from dual
where exists(select 'x'
from wip_flow_schedules
where demand_source_type = 2
and demand_source_line = to_char(order_line_id)); --Bugfix 6330114
select activity_status
into p_activity_status
from wf_item_activity_statuses was
where was.item_type = p_itemtype
and was.item_key = p_itemkey
and was.process_activity in
(SELECT wpa.instance_id
FROM wf_process_activities wpa
WHERE wpa.activity_name = p_activity_name);