[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| wf_update_after_inv_reserv |
| wf_update_after_inv_unreserv |
| 09-20-2001 RaviKumar V Addepalli |
| Changed the following procedures |
| 1. display_wf_status() |
| 2. wf_update_after_inv_reserv |
| 3. wf_update_after_inv_unreserv |
| For the the AutoCreate Purchase requisitions, For|
| a ATO Buy item the procedure will have to update |
| the line status and the workflow differently. |
|
|
|
| 13-NOV-2001 Modified by Renga Kannan |
| |
| Ato buy item workflow is modified |
| Wait for PO RECEIPT activity is |
| removed and hence the corresponding code |
| is modified in wf_update_inv_unreserve |
| and wf_update_inv_reserve |
| |
| 03/22/2002 bugfix#2234858 |
| added new functionality to support DROP SHIP |
|
| 04/18/2002 bugfix#2312701
| added exception to disallow reservation for |
| dropshipped orders before po approval |
| |
| 05/06/2002 bugfix#2358576 |
| restrict reservation for dropshippped orders |
| only to config items and ato items |
|
| 07/23/2002 bugfix#2466429
| Modified by Kiran Konada
| primary_reservation_quantity will have qty as per
| primary UoM
| 12/12/2002 KIran Konadad
| added a parametr in call to populate_req_interface
| for MLSUPPLY feture
|
|
| 08/13/ 2003 Kiran Konada
| for bug# 3063156
propagte bugfix 3042904 to main
| passed new paremeters project_id and task_id to
| populate_req_interface
| these are passed as NULL values as populate_req_interface
| calculates them for top_most buy items
|
Kiran Konada
| Muiple sources for DMF-J enahncement in
| wf_update_after_inv_unresv
| added a call to procedure check_cto_can _create_supply
| If cto cannot create supply(planning supply), worlflow will
| remain at shipping
| If cto can create supply, workflow would be moved appropriately
| depending on amount of qty unreserved
|
|
|
| 08/28/2003 Kiran Konada
| chnaging the procedure wf_update_after_inv_unresv
| getting inventory_item_id and oship from org_id
| from oe_order_lines_all This is added during Unit test
| for support to handle mutiple sources situations during supply
| creation
|
| 09/25/2003 Shashi Bhaskaran
| Bugfix : 3077912
| Offset need_by_date with postprocessing lead time.
| Bugfix : 3076061
| When reservations are removed, CTO need to move the
| order line status to 'Production Eligible' and WF status
| to 'Create Supply Eligible' if the flow schedules are all
| completed or no flow schedules exists.
| Bugfix : 2972186
| Split line status is showing "Production Complete" instead
| of "Shipped".
|
| 10/23/2003 Kiran Konada
| changed dsiplay_wf-status for patchset J
| introduced a prpcedure Get_Resv_Qty_and_Code
|
| 10/25/2003 Kiran Konada
| removed t_rsv_code.count and added
| t_rsv_code IS NOT NULL
|
| 11/03/2003 Kiran Konada
| Main propagation bug#3140641
|
| reverted changes made on 08/13/ 2003
| removed project_id and task_id as parameters to populate_req_interface
| revrting bufix 3042904 ude to bug#3129117
|
| 11/04/2003 Kiran Konada
| Made a call to CTOUTILB.check_cto_can_create_supply
| from display_wf_status
|
|
| 11/19/2003 Kiran Konada
| There was a full table scan on fnd_concurrent_programs
| hece, added where clause
| and application_id = 702;BOM , bugfix 2885568
| select concurrent_program_id
into p_program_id
from fnd_concurrent_programs a
where concurrent_program_name = 'CTOACREQ'
|
|
| new query
| select concurrent_program_id
| into p_program_id
| from fnd_concurrent_programs a
| where concurrent_program_name = 'CTOACREQ'
| and application_id = 702
|
|
|
| 01/23/2004 Sushant Sawant
| Added condition config_orgs <> 3 to avoid warehouse change for items
| not based on 'Based on Model'.
|
|
| 02/06/2004 Sushant Sawant fixed bug 3424802
| split line should be allowed irrespective of CIB attribute.
|
|
| 02/23/2004 Sushant Sawant fixed Bug 3419221
| New LINE_FLOW_STATUS code 'SUPPLY_ELIGIBLE' was introduced.
| Config Lines with Internal and External source types will be assigned this status.
|
|
| Modified : 13-APR-2004 Sushant Sawant
| Added check for booked orders to provide SUPPLY_ELIGIBLE/ENTERED
| as status of config line.
|
|
| Modified : 14-APR-2004 Sushant Sawant
| Fixed bug 3419221. following status for config item/ato item are available
| as replacement for BOM_AND_RTG_CREATED
| New Status: ENTERED/BOOKED/SUPPLY_ELIGIBLE
|
|
| Modified : 17-Nov-2004 Kiran Konada
| bugfix 3875420, as flow rsv is not visible in
mtl_reservations, we check for rsv_code FO
| Modified : 06-Jun-2005 Renga Kannan
| Bug Fix 4380768
|
|
| 16-Jun-2005 Kiran Konada
| changes for OPM and Ireq project
| comment string : OPM
| get_resv_qty_and_code API
| -cursor c-resv chnaged to include secondary
| reservation qty
| -sec rsv qty has been assigned to record
| structure x_rsv_rec(l_index) and debug messages
| -qyery on wip_flow_schedules has been removed
| and existing FLM api get_flow_qty is used to
| get flow qty in both cases of fresh order line
| and split order line
| -new query to get external and internal
| req data from iface table and assigned to x_rsv_rec(l_index
| 24-Jun-2005 Renga Kannan
| Get_resv_qty_and_code API is not handling uom
conversion.
| Added code to pass the qty in primary uom
|
| 05-Jun-2005 Renga Kannan
| Modified function complete_activity for MOAC
|
|
| 09-Aug-2005 Kiran Konada
| 4545070
| Replaced call to OE_ORDER_WF_UTIL.update_flow_status_code with
| call to CTO_WORKFLOW_API_PK.display_wf_status
|
|
| 25-Sep-2005 Kiran Konada
| bugfix4637281
| changing IF..ELSIF into multiple IF..ENDIF's
|
| 12-OCT-2005 Kiran Koanda
| r12 bugfix 4666778, changed query to look at models CIB attribute
|
| 15-NOV-2005 Kiran Konada
| bug#4739807,when rsv is only bcos of receiving
|
| 16-NOV-2005 Kiran Konada
| bug# 4743430
| For homogenous supply with receiving as one of
| reservations types. L_toekn2 is null, So the status
| would be just IN_RECEIVING
|
|
| 01-Dec-2005 Kiran Konada
| FP of bugfix 4051282
| Main line bug#4350569
|
=============================================================================*/
G_PKG_NAME CONSTANT VARCHAR2(30):='CTO_WORKFLOW_API_PK';
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);
Select org_id
into l_line_org_id
from oe_order_lines_all
where line_id = p_itemkey;
function to update the line status, we set the org
context to the org_id on oe_order_lines_all. After
returning from the OM function, we reset the org
context to the WIP operating unit (profile MO:op unit)
fix for bug 1895563 :
Removed the top most if statement which was
checking if order was booked and (scheduled or reserved)
*********************************************************************************/
FUNCTION display_wf_status(
p_order_line_id IN NUMBER
)
return INTEGER is
v_ordered_quantity NUMBER;
select oel.header_id,
INV_CONVERT.inv_um_convert( --bug 2317701
oel.inventory_item_id,
5, -- pass precision of 5
oel.ordered_quantity,
oel.order_quantity_uom,
msi.primary_uom_code,
null,
null
),
oel.org_id,
oel.ship_from_org_id ,
oel.inventory_item_id,
oel.item_type_code,
oel.source_type_code,
nvl(oel.shipped_quantity,0),
oel.ato_line_id, --added for patchset J
oel.top_model_line_id, --added for patchset J
oel.booked_flag
into v_header_id,
v_ordered_quantity,
l_oe_org_id,
l_ship_from_org_id,
v_inv_item_id,
l_item_type_code,
v_source_type_code,
v_shipped_qty,
l_ato_line_id,
l_top_model_line_id,
l_booked_flag
from oe_order_lines_all oel,
mtl_system_items msi
where line_id = p_order_line_id
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id;
select 'Y'
INTO l_req_created
from oe_drop_ship_sources
where line_id = p_order_line_id;
SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
INTO l_original_org_id
FROM DUAL;
select upper(item_type_code)
, ato_line_id
, source_type_code
, header_id
into v_item_type_code
, v_ato_line_id
, v_source_type_code
, v_header_id
from oe_order_lines_all
where line_id = p_order_line_id;
select poh.authorization_status
into v_authorization_status
from po_headers_all poh,
po_lines_all pol,
oe_drop_ship_sources ods
where ods.header_id = v_header_id
and ods.line_id = p_order_line_id
and ods.po_header_id = poh.po_header_id
and pol.po_line_id = ods.po_line_id
and nvl(pol.cancel_flag, 'N') <> 'Y';
Procedure: wf_update_after_inv_reserv
Parameters: p_order_line_id
x_return_status - standard API output parameter
x_msg_count - "
x_msg_data - "
Description: update an order line status after inventory reservation
*****************************************************************************/
PROCEDURE wf_update_after_inv_reserv(
p_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) := 'wf_update_after_inv_reserv';
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Entering wf_update_after_inv_reserv');
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Entering wf_update_after_inv_reserv', 2);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'Before selecting info from oe_order_lines_all');
select item_type_code,
ato_line_id
into v_item_type_code,
v_ato_line_id
from oe_order_lines_all
where line_id = p_order_line_id;
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'ato_line_id = '||v_ato_line_id);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'v_item_type_code = '||v_item_type_code);
oe_debug_pub.add('wf_update_after_inv_reserv: ato_line_id:' || v_ato_line_id);
oe_debug_pub.add('wf_update_after_inv_reserv: v_item_type_code:' || v_item_type_code);
oe_debug_pub.add('wf_update_after_inv_reserv: p_order_line_id:' || p_order_line_id);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'item_type_code is STANDARD/OPTION/INCLUDED => an ato item', 4);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Before querying wf_activity_status..');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','v_activity_status_code = '||
upper(v_activity_status_code));
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Complete the workflow
stmt no : 30');
oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity is successful',5);
oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity function returned error..',5);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Before calling display_wf_status..'||
'from l_stmt_num=>'||l_stmt_num);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Before calling display_wf_status.. from l_stmt_num=>'||l_stmt_num,2);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Return value after display_wf_status'
||to_char(return_value));
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Return value after display_wf_status at l_stmt_num=>'||l_stmt_num||'is'
||to_char(return_value),1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Stmt no : 40, query
wf_activity status..');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','v_activity_status_code = '||
v_activity_status_code);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Stmt no : 50
querying wf_activity_status');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'v_actitivity_status_code ='||
v_activity_status_code);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
'Before calling display_wf_status..'||
'from l_stmt_num=>'||l_stmt_num);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Before calling display_wf_status.. from l_stmt_num=>'||l_stmt_num,2);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
'Return value after display_wf_status'
||to_char(return_value));
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Return value after display_wf_status at l_stmt_num=>'||l_stmt_num||'is'
||to_char(return_value),1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Before calling display_wf_status..'||
'from l_stmt_num=>'||l_stmt_num);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Before calling display_wf_status.. from l_stmt_num=>'||l_stmt_num,2);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Return value after display_wf_status'
||to_char(return_value));
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Return value after display_wf_status at l_stmt_num=>'||l_stmt_num||'is'
||to_char(return_value),1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Raising CTO_ERROR_FROM_DISPLAY_STATUS');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
' Stmt no : 60 , querying activity_status');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' v_actitivity_status_code = '||
v_activity_status_code);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Stmt no : 70, Completing
workflow activity..');
oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: complete_activity is sucessful ',5);
oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity function returned error..',5);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Stmt no : 80 querying
wf_activity_status');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','v_activity_status_code = '||
v_activity_status_code);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Stmt no : 90,
querying wf_activity_status');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', ' v_activity_status_code = '||
v_activity_status_code);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_reserv', 'v_activity_status_code <> NOTIFIED');
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_reserv', 'Raising CTO_INVALID_ACTIVITY_STATUS');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Before calling display_wf_status');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Return_value from display_wf_status = '||
to_char(return_value));
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Raising CTO_ERROR_FROM_DISPLAY_STATUS');
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Exiting wf_update_after_inv_reserv');
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Exiting wf_update_after_inv_reserv', 2);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'exp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_reserv::stmt number '||to_char(l_stmt_num), 1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
'Exp error in CTO_WORKFLOW_API_PK.wf_update_inv_reserv::
stmt no : '||to_char(l_stmt_num)||'::'||sqlerrm);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_reserv::stmt number '||to_char(l_stmt_num), 1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
'UNExp error in CTO_WORKFLOW_API_PK.wf_update_inv_reserv::
stmt no : '||to_char(l_stmt_num)||'::'||sqlerrm);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_reserv::stmt number '||to_char(l_stmt_num), 1);
oe_debug_pub.add('wf_update_after_inv_reserv: ' || sqlerrm, 1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'OTHERS excpn: stmt no : '||to_char(l_stmt_num));
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', sqlerrm);
END wf_update_after_inv_reserv;
select item_type_code, ato_line_id
into v_item_type_code, v_ato_line_id
from oe_order_lines_all
where line_id = p_order_line_id;
Procedure: wf_update_after_inv_unreserv
Parameters: p_order_line_id
x_return_status - standard API output parameter
x_msg_count - "
x_msg_data - "
Description: update an order line status after inventory unreservation
The logic for this procedure is as follows:
1. IF the Workflow is in Ship Notified then check for the reservation (All kinds)
for this line. If there is no reservation found, Move the workflow to Create
Supply order eligible.
2. IF the line is not in Ship notified then, Check if it is a config line. If it is
a config line error out.
3. Call display_wf_status for all the scenarios.
4. In the case ATO item, No action is taken if the line is not in Ship notified.
5. This API is called for all types if items.
*****************************************************************************/
PROCEDURE wf_update_after_inv_unreserv(
p_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) := 'wf_update_after_inv_unreserv';
SELECT interface_source_line_id
FROM po_requisitions_interface_alL
WHERE interface_source_line_id = l_split_from_line_id
AND process_flag is NULL
and item_id = l_inventory_item_id
FOR UPDATE of interface_source_line_id NOWAIT;
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'In wf_update_after_inv_unreserv');
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Entering wf_update_after_inv_unreserv', 2);
select item_type_code,
ato_line_id,
split_from_line_id,
inventory_item_id, --added by kiran
ship_from_org_id, --added by kiran
nvl(shipping_interfaced_flag,'N'), -- Bug fix 4863275
open_flag -- Bugfix 7214005
into
v_item_type_code,
v_ato_line_id,
l_split_from_line_id,
l_inventory_item_id,
l_ship_from_org_id,
l_ship_xfaced_flag, -- Bug fix 4863275
v_open_flag -- Bugfix 7214005
from oe_order_lines_all
where line_id = p_order_line_id;
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'ATO Line Id = '||v_ato_line_id);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Item Type Code = '||v_item_type_code);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Open Flag = '||v_open_flag);
oe_debug_pub.add('wf_update_after_inv_unreserv ' || 'ATO Line Id ='||v_ato_line_id, 1);
oe_debug_pub.add('wf_update_after_inv_unreserv ' || 'Item Type Code ='||v_item_type_code, 1);
oe_debug_pub.add('wf_update_after_inv_unreserv ' || 'Open Flag ='||v_open_flag, 1);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Ship Line Notified...',1);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' ||'check_cto_can_create_supply'
||'x_return_status=> ' || x_return_status);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' ||
'l_can_create_supply is=>'||l_can_create_supply,1);
select count(*)
into v_counter
from mtl_reservations
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 = p_order_line_id
and primary_reservation_quantity > 0;
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'v_counter = '||v_counter);
select count(*)
into v_counter2
from wip_flow_schedules
where demand_source_type = 2
and demand_source_line = to_char(p_order_line_id)
and status <> 2; -- 3076061 Flow Schedule status : 1 = Open 2 = Closed/Completed
-- Since flow does not update the schedule with new line_id when the order line is split, we need
-- to call the following function which will determine the open quantity.
-- If open_qty exists, we should keep the workflow in SHIP_LINE.
if v_counter2 = 0 then
v_counter2 :=
MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY( p_demand_source_line => to_char(p_order_line_id),
p_demand_source_type => inv_reservation_global.g_source_type_oe,
p_demand_source_delivery => NULL,
p_use_open_quantity => 'Y');
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'v_counter2 = '||v_counter2);
UPDATE PO_REQUISITIONS_INTERFACE_ALL
SET interface_source_line_id = p_order_line_id
WHERE CURRENT OF INT_REQ;
SELECT Nvl(Sum(quantity),0)
INTO l_po_req_qty
FROM po_requisitions_interface_all
WHERE interface_source_line_id = p_order_line_id
and item_id = l_inventory_item_id
AND process_flag is null;
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'l_po_req_qty = '||l_po_req_qty);
-- We need to check from OM if its okay to update the workflow back to create supply order
-- eligible. We will call Get_Min_max_Tolerance_Quantity to see whether OM will fulfil this line.
-- If so, we will not update the w/f
-- Eg, OQ=10, ship tolerance belo=20%; WO Qty=10; Complete=8; Ship=8; Inv i/f;
-- OM/Shipping will call Inv to delete any reservations existing and inv will unreserve qty=2
-- after validating against CTO. Now, wf_update_after_inv_unreserv will make the line back to
-- create supply order eligible !! and OM i/f will fail since it expect it to be in ship
-- notified.
--
-- Get_Min_Max_Tolerance_Quantity will return x_min_remaining_quantity=0 if fulfilled.
-- If this value is 0, then, we do not update the w/f status back to create supply eligible.
--
If l_ship_xfaced_flag = 'Y' Then
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add ('wf_update_after_inv_unreserv: ' || 'CTO: Calling OE_Shipping_Integration_Pub.Get_Min_Max_Tolerance_Quantity..', 4);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv',
'Failed in WSH_Integration_Pub.Get Tolerance');
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'CTO:Failed in WSH_Integration_Pub.Get Tolerance :' || v_x_return_status, 1);
oe_debug_pub.add ('wf_update_after_inv_unreserv: ' || 'CTO: Returned from WSH_Integration_Pub.Get_Min_Max_Tolerance_Quantity.', 4);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'v_counter = '|| v_counter, 4);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'v_counter2 = '|| v_counter2, 4);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_min_remaining_quantity = '|| l_out_attributes.min_remaining_quantity, 4);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_max_remaining_quantity = '|| l_out_attributes.max_remaining_quantity, 4);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_min_remaining_quantity2 = '|| l_out_attributes.min_remaining_quantity2, 4);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_max_remaining_quantity2 = '|| l_out_attributes.max_remaining_quantity2, 4);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'v_x_min_remaining_quantity = '|| l_out_attributes.min_remaining_quantity);
cto_wip_workflow_api_pk.cto_debug('Wf_update_after_inv_unreserve',' Shipping interfaced flag = '||l_ship_xfaced_flag);
oe_debug_pub.add('Wf_update_after_inv_unreserve: Shipping interfaced flag = '||l_ship_xfaced_flag);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'No Reservations exists... Updating workflow.');
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'No Reservation Exists...',1);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Renga: Updating the shipping attributes..',1);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv',
'Renga : CAlling Shipping update attribute api');
WSH_INTERFACE.Update_Shipping_Attributes
( p_source_code => 'OE'
, p_changed_attributes => l_changed_attributes
, x_return_status => v_x_return_status
);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv',
'WSH_INTERFACE.update_shipping_attributes');
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'CTO:Failed in WSH_INTERFACE.update_shipping_attributes :', 1);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || ' Renga: Success in update_shipping attributes..', 2);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserve',
' Renga: Success in update_shipping attributes..');
oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity is successful',5);
oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity function returned error..',5);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Reservation Exists. Will not update workflow status.',1);
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Raising CTO_ERROR_FROM_DISPLAY_STATUS');
cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Exiting wf_update_after_inv_unreserv');
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Exiting wf_update_after_inv_unreserv', 2);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'exp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv::stmt number '||to_char(l_stmt_num), 1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE','exp erro stmt no :'|| to_char(l_stmt_num));
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv::stmt number '||to_char(l_stmt_num));
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE','Unexp err stmt no:'|| to_char(l_stmt_num));
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv::stmt number '||to_char(l_stmt_num), 1);
oe_debug_pub.add('wf_update_after_inv_unreserv: ' || sqlerrm, 1);
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE','Unexp errr stmt no :'|| to_char(l_stmt_num));
cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE',sqlerrm);
END wf_update_after_inv_unreserv;
select count(*) into v_count
from oe_order_lines_all
where header_id = v_header_id
and ato_line_id = v_model_id
and item_type_code = 'CONFIG';
select ato_line_id, header_id
into l_ato_line_id, l_header_id
from oe_order_lines_all
where line_id = oe_line_security.g_record.line_id
and item_type_code <> 'STANDARD'
and ato_line_id is not null;
select 1
into l_config_exists
from oe_order_lines_all
where header_id = l_header_id
and ato_line_id = l_ato_line_id
and item_type_code = 'CONFIG';
Procedure: Update_Config_Line
Parameters: p_application_id (standard signature format)
p_entity_short_name
p_validation_entity_short_name
p_validation_tmplt_short_name
p_record_set_short_name
p_scope
x_result
Description: This API with standard signature format is called from
the security constraints to validate whether a change is
allowed on an order line.
This API gets called for every item type. It returns
x_result = 0 if the item is a config item and the line is
being updated by a system action (like scheduling or
cascading).
Otherwise, it returns x_result = 1.
*****************************************************************************/
PROCEDURE Update_Config_Line(
p_application_id IN NUMBER,
p_entity_short_name IN VARCHAR2,
p_validation_entity_short_name IN VARCHAR2,
p_validation_tmplt_short_name IN VARCHAR2,
p_record_set_short_name IN VARCHAR2,
p_scope IN VARCHAR2,
x_result OUT NOCOPY NUMBER )
IS
l_config_item NUMBER;
oe_debug_pub.add('configuration_created: ' || 'Entering Update_Config_Line', 5);
select 1
into l_config_item
from oe_order_lines_all
where line_id = oe_line_security.g_record.line_id
and item_type_code = 'CONFIG';
oe_debug_pub.add('configuration_created: ' || 'This is a config item. Check if update is user or system', 5);
oe_debug_pub.add('configuration_created: ' || 'Cascading or scheduling change, allow update', 5);
oe_debug_pub.add('configuration_created: ' || 'User change, update not allowed', 5);
oe_debug_pub.add('configuration_created: ' || 'Exiting Update_Config_Line', 5);
oe_debug_pub.add('configuration_created: ' || 'CTO_WORKFLOW_API_PK.Update_Config_Line::stmt number '||to_char(l_stmt_num)||'not config item',1);
oe_debug_pub.add('configuration_created: ' || 'CTO_WORKFLOW_API_PK.Update_Config_Line::stmt number '||to_char(l_stmt_num)||'others',1);
END Update_Config_Line;
select ato_line_id
from oe_order_lines_all
where top_model_line_id = oe_line_security.g_record.top_model_line_id
and item_type_code = 'CONFIG';
select 1
into l_config_exists
from oe_order_lines_all
where top_model_line_id = oe_line_security.g_record.top_model_line_id
and item_type_code = 'CONFIG'
and rownum = 1;
select link_to_line_id
into l_current_model_line
from oe_order_lines_all
where line_id = l_current_model_line;
select 1
into l_top_ato_model
from oe_order_lines_all
where line_id = oe_line_security.g_record.line_id
and ato_line_id = line_id;
being updated by a system action (like scheduling or
cascading).
Otherwise, it returns x_result = 1.
*****************************************************************************/
PROCEDURE Config_Line(
p_application_id IN NUMBER,
p_entity_short_name IN VARCHAR2,
p_validation_entity_short_name IN VARCHAR2,
p_validation_tmplt_short_name IN VARCHAR2,
p_record_set_short_name IN VARCHAR2,
p_scope IN VARCHAR2,
x_result OUT NOCOPY NUMBER )
IS
l_config_item NUMBER;
select 1
into l_config_item
from oe_order_lines_all
where line_id = oe_line_security.g_record.line_id
and item_type_code = 'CONFIG';
Description: This API updates the line status to the in parameter (p_change_status)
based on the line_id provided(p_line_id).
*****************************************************************************/
PROCEDURE change_status_batch (
p_header_id NUMBER,
p_line_id NUMBER,
p_change_status VARCHAR2,
p_oe_org_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2) is
-- local parameters
l_message VARCHAR2(100);
select nvl(flow_status_code,'N'), cancelled_flag --Bugfix 7292113
into lFlowStatusCode, l_cancelled_flag
from oe_order_lines_all
where header_id = p_header_id
and line_id = p_line_id;
l_message := 'calling OE_ORDER_WF_UTIL.update_flow_status_code to update flow_status to '||p_change_status;
OE_ORDER_WF_UTIL.update_flow_status_code(
p_header_id => p_header_id,
p_line_id => p_line_id,
p_flow_status_code => p_change_status,
x_return_status => x_return_status);
select concurrent_program_id
into p_program_id
from fnd_concurrent_programs a
where concurrent_program_name = 'CTOACREQ'
and application_id = 702; --BOM , bugfix 2885568 for
SELECT *
INTO so_line
FROM oe_order_lines_all
WHERE line_id = to_number(p_itemkey);
SELECT order_number
INTO p_order_number
FROM oe_order_lines_all a, oe_order_headers_all b
WHERE a.header_id = b.header_id
AND a.line_id = to_number(p_itemkey);
oe_debug_pub.add('auto_create_pur_req: ' || 'This line has already been selected to be processed by the concurrent request :'||to_char(so_line.request_id),1);
oe_debug_pub.add('auto_create_pur_req: ' || 'Insert successful.',5);
oe_debug_pub.add('auto_create_pur_req: ' || 'Order updated to REQ_REQUESTED.', 5);
End; -- Insert record into the interface table.
SELECT item_type_code,
ato_line_id,
inventory_item_id,
ship_from_org_id
INTO l_item_type_code,
l_ato_line_id,
l_inv_item_id,
l_ship_org
FROM OE_ORDER_LINES_ALL
WHERE line_id = oe_line_security.g_record.line_id;
select ato_line_id, header_id , inventory_item_id
into l_ato_line_id, l_header_id , l_cfg_item_id
from oe_order_lines_all
where line_id = oe_line_security.g_record.line_id
and item_type_code <> 'STANDARD'
and ato_line_id is not null;
select line_id
into l_config_line_id
from oe_order_lines_all
where header_id = l_header_id
and ato_line_id = l_ato_line_id
and item_type_code = 'CONFIG';
oe_debug_pub.add( 'need to check for CIB attribute as update warehouse is enabled only for CIB = 3 ' , 1) ;
select nvl(msi.config_orgs,1)
into l_config_orgs
from oe_order_lines_all oel,
mtl_system_items msi
where oel.line_id = l_ato_line_id
and msi.inventory_item_id = oel.inventory_item_id
and msi.organization_id = oel.ship_from_org_id;
end if ; /* check CIB attribute for warehouse update */
select decode(l_make_flag,'Y',1,0)+decode(l_buy_fg,'Y',1,0)+decode(l_xfer_fg,'Y',1,0)
into l_hetro
from dual;