[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_SO_ATP_OVERRIDE
( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out nocopy varchar2)
IS
BEGIN
/* Change the VMI release type to ATP override.
Next node in workflow will call the release replenishment
*/
wf_engine.SetItemAttrNumber
( itemtype => itemtype,
itemkey => itemkey,
aname => 'VMI_RELEASE_TYPE',
avalue => G_PLANNER_OVERRIDE_ATP
);
END UPDATE_SO_ATP_OVERRIDE;
PROCEDURE DELETE_INTERFACE_RECORD
( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out nocopy varchar2
)
IS
lv_delete_release_id NUMBER :=
wf_engine.GetItemAttrNumber
(itemtype => itemtype,
itemkey => itemkey,
aname => 'RELEASE_ID'
);
/* delete from the interface table */
delete msc_so_release_interface
where release_id = lv_delete_release_id;
END DELETE_INTERFACE_RECORD;
SELECT FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO l_user_id,
l_user_name,
l_resp_name,
l_application_name
FROM dual;
/*SELECT FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO l_user_id,
l_user_name,
l_resp_name,
l_application_name
FROM dual;
select mtil.sr_tp_id
into lv_sr_cust_id
from msc_tp_id_lid mtil,
msc_trading_partners mtp,
msc_trading_partners mtp1
where mtp.partner_type = 3
and mtp.sr_tp_id = l_cust_organization_id
and mtp.sr_instance_id = l_sr_instance_id
and mtp.modeled_customer_id = mtil.tp_id
and mtil.sr_instance_id = mtp.sr_instance_id
and mtil.partner_type = 2
and mtil.sr_tp_id = mtp1.sr_tp_id -- bug #4929350
and mtp1.partner_type = 2
and mtil.sr_instance_id = mtp1.sr_instance_id
and mtp1.partner_id = mtp.modeled_customer_id
and mtp1.sr_instance_id = mtp.sr_instance_id;
select mtsil.sr_tp_site_id
into lv_sr_cust_site_id
from msc_tp_site_id_lid mtsil,
msc_trading_partner_sites mtps,
msc_trading_partners mtp
where mtp.partner_type = 3
and mtp.sr_tp_id = l_cust_organization_id
and mtp.sr_instance_id = l_sr_instance_id
and mtps.PARTNER_SITE_ID = mtp.modeled_customer_site_id
and mtps.partner_id = mtp.modeled_customer_id
and mtsil.sr_instance_id = mtp.sr_instance_id
and mtsil.PARTNER_TYPE = mtps.partner_type
and mtsil.tp_site_id = mtps.PARTNER_SITE_ID
and mtsil.sr_tp_site_id = mtps.sr_tp_site_id; -- bug #4929350
UPDATE msc_sup_dem_entries sd
SET sd.release_status = G_RELEASED,
sd.quantity_in_process = l_order_quantity
WHERE sd.publisher_id = 1
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = G_REPLENISHMENT_ORDER
AND sd.plan_id = -1
AND sd.transaction_id = l_rep_transaction_id
AND sd.release_status = G_UNRELEASED;
log_debug(' updated status of replenishment record to RELEASED');
/* update the Sales Order created as replenishment
to override the ATP schedule_ship_date
*/
log_message('Updating Sales Order with ATP Override.');
lv_action := G_UPDATE;
UPDATE MSC_SO_RELEASE_INTERFACE
SET action = lv_action,
atp_override = 'Y'
WHERE release_id = lv_rel_id;
/* If the action is to create, then insert the record in interface table */
select msc_so_release_s.nextval
into lv_rel_id
from dual;
log_debug('Selected the Released Id : ' || lv_rel_id );
/* Consigned CVMI Enh : Bug # 4247230 : Insert [ Order Number or Line Number or Release Number or
End Order Number or End Order Line Number or End Order Release Number] also */
insert into MSC_SO_RELEASE_INTERFACE(
RELEASE_ID ,
SR_INSTANCE_ID ,
SR_CUSTOMER_ID ,
SR_CUSTOMER_SITE_ID ,
SR_ITEM_ID ,
QUANTITY ,
UOM_CODE ,
ACTION ,
REQUEST_DATE ,
ATP_OVERRIDE ,
OE_TRANSACTION_TYPE,
SHIP_FROM_ORG_ID,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
ORDER_NUMBER ,
RELEASE_NUMBER ,
LINE_NUMBER ,
END_ORDER_NUMBER ,
END_ORDER_REL_NUMBER ,
END_ORDER_LINE_NUMBER )
values
( lv_rel_id,
l_sr_instance_id,
lv_sr_cust_id,
lv_sr_cust_site_id,
l_sr_inventory_item_id,
l_order_quantity,
l_uom_code,
lv_action, ---- create
l_request_date, --- request_date
lv_atp_override, --- atp override
lv_transaction_type,
l_cust_organization_id, --- For cons. advice , pass the cust model org
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
-1,
l_ORDER_NUMBER ,
l_RELEASE_NUMBER ,
l_LINE_NUMBER ,
l_END_ORDER_NUMBER ,
l_END_ORDER_REL_NUMBER ,
l_END_ORDER_LINE_NUMBER
);
log_debug('Action(1-create, 2-update) : ' || lv_action );
log_debug('Inserted into msc_so_release_interface: ' || lv_rel_id );
SELECT DECODE(apps.m2a_dblink
,NULL,' '
,'@' || m2a_dblink),
m2a_dblink
, a2m_dblink -- bug 3436758
, instance_code
INTO l_dblink,
null_dblink
, l_a2m_dblink
, l_instance_code
FROM msc_apps_instances apps
WHERE apps.instance_id = l_sr_instance_id;
log_debug('Selected the l_sr_instance_id Id : ' || l_sr_instance_id );
log_debug('Selected the l_dblink : ' || l_dblink );
select substr('Request Id '||lv_sr_req_id||' failed on the Source instance. Error Message:'
||ERROR_MESSAGE,1,1000)
INTO lv_error_message
from msc_so_release_interface
where RELEASE_ID = lv_rel_id;
select return_status,
OE_HEADER_ID,
OE_LINE_ID,
sales_order_number,
SHIP_FROM_ORG_ID,
schedule_ship_date,
schedule_arrival_date,
schedule_date_change,
ERROR_MESSAGE
INTO lv_return_status,
lv_header_id,
lv_line_id,
lv_sales_order_number,
lv_ship_from_org_id,
lv_schedule_ship_date,
lv_schedule_arrival_date,
lv_schedule_date_change,
lv_error_message
from msc_so_release_interface
where RELEASE_ID = lv_rel_id;
UPDATE msc_sup_dem_entries sd
SET sd.release_status = G_RELEASED,
sd.quantity_in_process = l_order_quantity
WHERE sd.publisher_id = 1
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = G_REPLENISHMENT_ORDER
AND sd.plan_id = -1
AND sd.transaction_id = l_rep_transaction_id
AND sd.release_status = G_UNRELEASED;
log_debug(' updated status of replenishment record to RELEASED');
/* update the interface table with Line Id
and Header Id of the Sales order created.
This information is required for updating the Sales Order
if planner overrides ATP schedule date */
update msc_so_release_interface
set oe_header_id = lv_header_id,
oe_line_id = lv_line_id
where release_id = lv_rel_id;
select organization_code
into lv_supplier_site_name
from msc_trading_partners
where partner_type = 3
and sr_instance_id = l_sr_instance_id
and sr_tp_id = lv_ship_from_org_id;
/* update the interface table with error message */
update msc_so_release_interface
set error_message = lv_error_message
where release_id = lv_rel_id;
select DECODE( A2M_DBLINK, NULL, ' ',
'@'||A2M_DBLINK),
INSTANCE_ID
into dest_dblink,
lv_instance_id
from MRP_AP_APPS_INSTANCES;
select DECODE( A2M_DBLINK, NULL, ' ','@'||A2M_DBLINK),
INSTANCE_ID
into dest_dblink,
lv_instance_id
from MRP_AP_APPS_INSTANCES_ALL
where instance_id = p_instance_id
and instance_code = p_instance_code
and nvl(a2m_dblink,'NULL_DBLINK') = nvl(p_a2m_dblink,'NULL_DBLINK')
and ALLOW_RELEASE_FLAG=1;
lv_sql_stmt := 'update msc_so_release_interface'||dest_dblink
||' set return_status = :lv_return_status, '
||' OE_HEADER_ID = :lv_header_id, '
||' OE_LINE_ID = :lv_line_id, '
||' sales_order_number = :lv_sales_order_number, '
||' SHIP_FROM_ORG_ID = :lv_ship_from_org_id, '
||' schedule_ship_date = :lv_schedule_ship_date, '
||' schedule_arrival_date = :lv_schedule_arrival_date, '
||' schedule_date_change = :lv_schedule_date_change, '
||' ERROR_MESSAGE = :lv_error_message '
||' where sr_instance_id = :lv_instance_id '
||' and RELEASE_ID = :pRelease_ID ';
log_message(' after update msc_so_release_interface in destination ') ;