[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select POSTPROCESSING_LEAD_TIME into v_leadTime
from msc_system_items
where inventory_item_id = itemId
and organization_id = orgid -- You need both organziation id and inventory_item_id to get an unique item
and msc_system_items.plan_id = planId
and msc_system_items.sr_instance_id = srInstanceId;
select calendar_code into calendarCode
from msc_trading_partners
where partner_type = 3
and sr_instance_id = srInstanceId
and sr_tp_id = orgId;
select n.calendar_date into d4
from msc_calendar_dates original,
msc_calendar_dates n
where original.calendar_code = calendarCode
and original.exception_set_id = -1
and original.sr_instance_id = srInstanceId
and original.calendar_date = newarrivalDate
and n.calendar_code = original.calendar_code
and n.exception_set_id = original.exception_set_id
and n.sr_instance_id = original.sr_instance_id
and n.seq_num = original.seq_num + v_leadTime;
procedure UpdateKeyDateInCP ( status OUT NOCOPY VARCHAR2) is
cursor getLineIds is
SELECT
po_line_location_id , UPDATED_ARRIVAL_DATE
FROM
MSC_transportation_updates
WHERE order_type = 1;
Update_CP(v_line_location_id,v_arrival_Date, status );
end UpdateKeyDateInCP;
procedure UpdateCP_1 ( tranzId IN NUMBER,
status OUT NOCOPY VARCHAR2) is
v_line_location_id NUMBER :=0;
status := 'NO_RECORD_TO_UPDATE';
SELECT po_line_location_id , UPDATED_ARRIVAL_DATE
INTO v_line_location_id, v_arrival_Date
FROM MSC_transportation_updates
WHERE order_type = 1
AND trans_Update_id = tranzId;
Update_CP(v_line_location_id,v_arrival_Date, status );
end UpdateCP_1;
procedure Update_CP ( lineLocationId IN NUMBER, arrivalDate IN DATE, status OUT NOCOPY VARCHAR2) is
v_cnt NUMBER :=0;
SELECT distinct order_number, purch_line_num, order_type
INTO v_order_Number, v_line_Number, v_order_type
FROM msc_supplies
WHERE msc_supplies.po_line_location_id = lineLocationId
and plan_id = -1
and msc_supplies.sr_instance_id = srInstanceId
and msc_supplies.order_type in (1, 11);
select decode(instr(v_order_Number,'('), 0, v_order_Number, substr(v_order_Number, 1, instr(v_order_Number,'(') - 1))
into orderNumber
from dual;
select decode(v_order_type, 1, nvl(substr(v_order_number,instr(v_order_number,'(')+1,instr(v_order_number,'(',1,2)-2
- instr(v_order_number,'(')),' ') , decode(instr(v_order_number,'('), 0, to_char(null),
substr(v_order_number, instr(v_order_number,'('))))
into releaseNumber
from dual;
select count(*) into v_cnt
from msc_sup_dem_entries
where msc_sup_dem_entries.order_number = orderNumber
and msc_sup_dem_entries.line_number = lineNumber
and msc_sup_dem_entries.release_number = releaseNumber
and msc_sup_dem_entries.publisher_order_type in (13, 15);
update msc_sup_dem_entries
set key_date = arrivalDate,
receipt_date = arrivalDate,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
where msc_sup_dem_entries.order_number = orderNumber
and msc_sup_dem_entries.line_number = lineNumber
and msc_sup_dem_entries.release_number = releaseNumber
and msc_sup_dem_entries.last_refresh_number = v_last_refresh_number
and msc_sup_dem_entries.publisher_order_type in (13, 15)
and msc_sup_dem_entries.plan_id = -1;
end Update_CP;
SELECT
last_refresh_number
FROM
msc_sup_dem_entries
WHERE
msc_sup_dem_entries.order_number = orN
and msc_sup_dem_entries.line_number = lN
and msc_sup_dem_entries.release_number = rN
ORDER BY last_refresh_number DESC;
SELECT
key_date into v_old_key_date
FROM
msc_sup_dem_entries
WHERE
msc_sup_dem_entries.order_number = orderNumber
and msc_sup_dem_entries.line_number = lineNumber
and msc_sup_dem_entries.release_number = releaseNumber
and msc_sup_dem_entries.last_refresh_number = lastRefreshNumber;
procedure UpdatePDS( status OUT nocopy VARCHAR2) is
cursor c_getLineIds is
SELECT
order_type, TRANS_UPDATE_ID
FROM
MSC_TRANSPORTATION_UPDATES;
status := 'No Plan to Update.';
UpdatePDS_Order(v_id, v_order_type, status );
END UpdatePDS;
procedure UpdatePDS_1( tranzId IN NUMBER,
bpelOrderType IN NUMBER,
status OUT nocopy VARCHAR2) is
plan_id NUMBER :=0;
status := 'NO_RECORD_TO_UPDATE';
status := 'No Plan to Update.';
UpdatePDS_Order(tranzId, bpelOrderType, status );
END UpdatePDS_1;
PROCEDURE UpdatePDS_Order( transId IN NUMBER ,
order_type IN NUMBER,
status OUT nocopy varchar2) IS
cursor getProductionPlans is
SELECT plans.plan_id
FROM msc_plans plans, msc_designators desig
WHERE plans.curr_plan_type in (1,2,3,5)
AND plans.organization_id = desig.organization_id
AND plans.sr_instance_id = desig.sr_instance_id
AND plans.compile_designator = desig.designator
AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
AND plans.organization_selection <> 1
and desig.PRODUCTION = 1
AND NVL(plans.copy_plan_id,-1) = -1
AND NVL(desig.copy_designator_id, -1) = -1;
UpdatePDS_PO(planId, transId, status);
UpdatePDS_SO(planId, transId, status);
UpdatePDS_PO(v_plan_id, transId, status);
UpdatePDS_SO(v_plan_id, transId, status);
end UpdatePDS_Order;
PROCEDURE UpdatePDS_PO( planId IN NUMBER,
transId IN NUMBER,
status OUT nocopy varchar2) IS
isPoShipment NUMBER :=0;
UpdateNewColumnAndFirmDate_PO(planId, transId, isPoShipment, status);
end UpdatePDS_PO;
PROCEDURE UpdatePDS_SO( planId IN NUMBER,
transId IN NUMBER,
status OUT nocopy varchar2) IS
begin
UpdateNewColumnAndFirmDate_SO(planId, transId, status);
end UpdatePDS_SO;
SELECT distinct s.transaction_id, tu.UPDATED_ARRIVAL_DATE, tu.EBS_SR_INSTANCE_ID, s.ORGANIZATION_ID, s.INVENTORY_ITEM_ID, s.supplier_id, s.supplier_site_id,
s.new_dock_date, s.order_number,
s.NEW_ORDER_QUANTITY, s.SOURCE_SR_INSTANCE_ID, s.SOURCE_ORGANIZATION_ID
INTO supp_Transaction_id, newArrivalDate, srInstanceId, v_org_id, v_inv_item_id, v_supplier_id, v_supplier_site_id, v_old_dock_date, v_order_number, v_q,
v_source_sr_inst_id, v_sr_org_id
FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
WHERE s.ORDER_TYPE = 11
AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
AND s.SUPPLIER_ID is not null
AND s.PLAN_ID = planId
AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND tu.TRANS_UPDATE_ID = transId;
SELECT distinct s.transaction_id, tu.UPDATED_ARRIVAL_DATE, tu.EBS_SR_INSTANCE_ID,s.ORGANIZATION_ID, s.INVENTORY_ITEM_ID, s.supplier_id,
s.supplier_site_id, s.new_dock_date, s.order_number,
s.NEW_ORDER_QUANTITY, s.SOURCE_SR_INSTANCE_ID,s.SOURCE_ORGANIZATION_ID
INTO supp_Transaction_id, newArrivalDate, srInstanceId, v_org_id, v_inv_item_id, v_supplier_id, v_supplier_site_id, v_old_dock_date, v_order_number, v_q,
v_source_sr_inst_id, v_sr_org_id
FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
WHERE s.ORDER_TYPE = 1
AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
AND s.PO_LINE_ID = tu.PO_LINE_ID
AND s.PLAN_ID =planId
AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND tu.TRANS_UPDATE_ID = transId;
select count(1)
into count_exc_this_order
from msc_exception_details
where exception_type = excType
and plan_id = planId
and organization_id =v_org_id
and inventory_item_id =v_inv_item_id
and resource_id =-1 and department_id = -1
and sr_Instance_id = srInstanceId
and supplier_id = v_supplier_id
and order_number =v_order_number;
update msc_exception_details
set date2=newArrivalDate
where exception_type = excType
and plan_id = planId
and organization_id =v_org_id
and inventory_item_id =v_inv_item_id
and resource_id =-1 and department_id = -1
and sr_Instance_id = srInstanceId
and supplier_id = v_supplier_id
and order_number =v_order_number;
status := 'Exception for this order already inserted. Updated new Arrival Date';
select count(1)
into countItemExc
from msc_item_exceptions
where plan_id = planId
and organization_id = v_org_id
and sr_Instance_id = srInstanceId
and inventory_item_id = v_inv_item_id
and exception_type = excType;
INSERT INTO msc_item_exceptions(plan_id, organization_id, sr_Instance_id, inventory_item_id, exception_type,
exception_group,
LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
supplier_id, supplier_site_id, exception_count)
VALUES(planId, v_org_id, srInstanceId,v_inv_item_id, excType,
21,
SYSDATE, userId, SYSDATE, userId,
v_supplier_id, v_supplier_site_id, 1);
select exception_count
into countItemExc
from msc_item_exceptions
where plan_id = planId
and organization_id = v_org_id
and sr_Instance_id = srInstanceId
and inventory_item_id = v_inv_item_id
and exception_type = excType;
update msc_item_exceptions
set exception_count = countItemExc,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
where plan_id = planId
and organization_id = v_org_id
and sr_Instance_id = srInstanceId
and inventory_item_id = v_inv_item_id
and exception_type = excType;
INSERT into msc_exception_details
(
exception_detail_id, exception_type, plan_id, organization_id, inventory_item_id, resource_id, -- -1
department_id, sr_Instance_id, LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
supplier_id, supplier_site_id, order_number, date2, date1, quantity, number1, number2,
transaction_id
)
VALUES (MSC_EXCEPTION_DETAILS_S.nextval, excType, planId, v_org_id, v_inv_item_id, -1,
-1, srInstanceId, SYSDATE, userId, SYSDATE, userId,
v_supplier_id, v_supplier_site_id, v_order_number, newArrivalDate, v_old_dock_date,v_q, v_sr_org_id, v_source_sr_inst_id,
supp_Transaction_id);
SELECT s2.transaction_id, s2.supplier_id, s2.supplier_site_id, s2.new_dock_date, s2.order_number, s2.INVENTORY_ITEM_ID,
s2.SOURCE_ORGANIZATION_ID , s2.ORGANIZATION_ID, s2.NEW_ORDER_QUANTITY, s2.SR_INSTANCE_ID
FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE s2.ORDER_TYPE = 11 -- IR Shipment
AND s2.PLAN_ID =planId
AND s2.SR_INSTANCE_ID = srIId
AND SO.SR_INSTANCE_ID = srIId
AND dd.SR_INSTANCE_ID = srIId
AND tu.EBS_SR_INSTANCE_ID = srIId
AND s2.REQ_LINE_ID = SO.ORIGINAL_SYSTEM_LINE_REFERENCE
AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND tu.Trans_update_id = transId;
select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
from msc_transportation_updates
where TRANS_UPDATE_ID = transId;
SELECT distinct d.DEMAND_ID
INTO ISOID1
FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND d.PLAN_ID = planId
AND d.ORIGINATION_TYPE = 30
AND tu.Trans_update_id = transId;
SELECT distinct s.transaction_id, s.supplier_id, s.supplier_site_id, s.new_dock_date, s.order_number, d.INVENTORY_ITEM_ID,
d.SOURCE_ORGANIZATION_ID , d.ORGANIZATION_ID, s.NEW_ORDER_QUANTITY, s.SR_INSTANCE_ID--IR
INTO supp_Transaction_id, v_supplier_id, v_supplier_site_id, v_old_dock_date, v_order_number, v_inv_item_id,
v_sr_org_id, v_org_id, v_q, v_source_sr_Inst_id
FROM MSC_SUPPLIES s, MSC_DEMANDS d
WHERE s.ORDER_TYPE = 2 -- IR
AND s.TRANSACTION_ID = d.DISPOSITION_ID
AND s.PLAN_ID =planId
AND s.SR_INSTANCE_ID = SrInstanceId
AND d.DEMAND_ID = ISOID1;
select count(1)
into count_exc_this_order
from msc_exception_details
where exception_type = excType
and plan_id = planId
and organization_id =v_org_id
and inventory_item_id =v_inv_item_id
and resource_id =-1 and department_id = -1
and sr_Instance_id = srInstanceId
and supplier_id = v_supplier_id
and order_number =v_order_number;
update msc_exception_details
set date2=newArrivalDate
where exception_type = excType
and plan_id = planId
and organization_id =v_org_id
and inventory_item_id =v_inv_item_id
and resource_id =-1 and department_id = -1
and sr_Instance_id = srInstanceId
and supplier_id = v_supplier_id
and order_number =v_order_number;
status := 'Exception for this order already inserted. Updated new Arrival Date';
select count(1)
into countItemExc
from msc_item_exceptions
where plan_id = planId
and organization_id = v_org_id
and sr_Instance_id = srInstanceId
and inventory_item_id = v_inv_item_id
and exception_type = excType;
INSERT INTO msc_item_exceptions(plan_id, organization_id, sr_Instance_id, inventory_item_id, exception_type,
exception_group,
LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
supplier_id, supplier_site_id, exception_count)
VALUES(planId, v_org_id, srInstanceId,v_inv_item_id, excType,
21,
SYSDATE, userId, SYSDATE, userId,
v_supplier_id, v_supplier_site_id, 1);
select exception_count
into countItemExc
from msc_item_exceptions
where plan_id = planId
and organization_id = v_org_id
and sr_Instance_id = srInstanceId
and inventory_item_id = v_inv_item_id
and exception_type = excType;
update msc_item_exceptions
set exception_count = countItemExc,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
where plan_id = planId
and organization_id = v_org_id
and sr_Instance_id = srInstanceId
and inventory_item_id = v_inv_item_id
and exception_type = excType;
INSERT into msc_exception_details
(
exception_detail_id, exception_type, plan_id, organization_id, inventory_item_id, resource_id, -- -1
department_id, sr_Instance_id, LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
supplier_id, supplier_site_id, order_number, date2, date1, quantity, number1, number2,
transaction_id
)
VALUES (MSC_EXCEPTION_DETAILS_S.nextval, excType, planId, v_org_id, v_inv_item_id, -1,
-1, srInstanceId, SYSDATE, userId, SYSDATE, userId,
v_supplier_id, v_supplier_site_id, v_order_number, newArrivalDate, v_old_dock_date, v_q,
v_sr_org_id, v_source_sr_Inst_id, supp_Transaction_id);
PROCEDURE UpdateNewColumnAndFirmDate_PO( planId IN NUMBER,
transId IN NUMBER,
isPoShipment out nocopy NUMBER,
status out nocopy varchar2) IS
cursor GetPOIds is
SELECT s.TRANSACTION_ID
FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
WHERE s.ORDER_TYPE = 1
AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
AND s.PO_LINE_ID = tu.PO_LINE_ID
AND s.PLAN_ID =planId
AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND tu.TRANS_UPDATE_ID = transId;
SELECT s.TRANSACTION_ID
FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
WHERE s.ORDER_TYPE = 11
AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
AND s.SUPPLIER_ID is not null
AND s.PLAN_ID = planId
AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND tu.TRANS_UPDATE_ID = transId;
select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
from msc_transportation_updates
where TRANS_UPDATE_ID = transId;
select distinct s.INVENTORY_ITEM_ID, s.ORGANIZATION_ID into invItemId, orgId
FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
WHERE s.ORDER_TYPE = 11
AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
AND s.PLAN_ID =planId
AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND tu.TRANS_UPDATE_ID = transId;
select distinct s.INVENTORY_ITEM_ID, s.ORGANIZATION_ID into invItemId, orgId
FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
WHERE s.ORDER_TYPE = 1
AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
AND s.PO_LINE_ID = tu.PO_LINE_ID
AND s.PLAN_ID =planId
AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND tu.TRANS_UPDATE_ID = transId;
status := 'NO_ORDERS_FOUND_TO_UPDATE';
UPDATE msc_transportation_updates
SET UPDATED_DUE_DATE = v_new_firm_Date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE TRANS_UPDATE_ID = transId;
UPDATE MSC_SUPPLIES
Set FIRM_DATE = v_new_firm_Date,
APPLIED = 2,
STATUS = 0,
FIRM_PLANNED_TYPE = 1,
OTM_ARRIVAL_DATE = newArrivalDate,
FIRM_QUANTITY = NEW_ORDER_QUANTITY,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE TRANSACTION_ID = PO_Ids(i)
AND SR_INSTANCE_ID = SrInstanceId
AND PLAN_ID = planId;
status := ' NO_ORDERS_FOUND_TO_UPDATE_IN_PDS';
end UpdateNewColumnAndFirmDate_PO;
PROCEDURE UpdateNewColumnAndFirmDate_SO( planId IN NUMBER,
transId IN NUMBER , status out nocopy varchar2) IS
/*cursor GetIR_Shipments( srIId IN NUMBER ) is
SELECT s2.TRANSACTION_ID
FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE s2.ORDER_TYPE = 11 -- IR Shipment
AND s2.PLAN_ID =planId
AND s2.SR_INSTANCE_ID = srIId
AND SO.SR_INSTANCE_ID = srIId
AND dd.SR_INSTANCE_ID = srIId
AND tu.EBS_SR_INSTANCE_ID = srIId
AND s2.REQ_LINE_ID = SO.ORIGINAL_SYSTEM_LINE_REFERENCE
AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND tu.Trans_update_id = transId;*/
select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
from msc_transportation_updates
where TRANS_UPDATE_ID = transId;
select distinct d.INVENTORY_ITEM_ID, d.SOURCE_ORGANIZATION_ID into invItemId, orgId
FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE d.SALES_ORDER_LINE_ID = to_char(dd.SOURCE_LINE_ID)
AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND d.PLAN_ID = planId
AND d.ORIGINATION_TYPE = 30
AND tu.TRANS_UPDATE_ID = transId;
SELECT distinct d.DEMAND_ID
into ISOID1
FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND d.PLAN_ID = planId
AND d.ORIGINATION_TYPE = 30
AND tu.Trans_update_id = transId;
UPDATE MSC_DEMANDS
Set OTM_ARRIVAL_DATE = newArrivalDate,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE PLAN_ID = planId
AND SR_INSTANCE_ID = SrInstanceId
AND DEMAND_ID = ISOID1;
SELECT distinct s.TRANSACTION_ID --IR_Ids
INTO IRID1
FROM MSC_SUPPLIES s, MSC_DEMANDS d
WHERE s.ORDER_TYPE = 2 -- IR
AND s.TRANSACTION_ID = d.DISPOSITION_ID
AND s.PLAN_ID =planId
AND d.PLAN_ID =planId
AND s.SR_INSTANCE_ID = srInstanceId
AND d.DEMAND_ID = ISOID1;
UPDATE msc_transportation_updates
SET UPDATED_DUE_DATE = v_new_firm_Date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE TRANS_UPDATE_ID = transId;
UPDATE MSC_SUPPLIES
Set FIRM_DATE = v_new_firm_Date,
APPLIED = 2,
STATUS = 0,
FIRM_PLANNED_TYPE = 1,
OTM_ARRIVAL_DATE = newArrivalDate,
FIRM_QUANTITY = NEW_ORDER_QUANTITY,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE
PLAN_ID =planId
AND SR_INSTANCE_ID = SrInstanceId
AND TRANSACTION_ID = IRID1;
end UpdateNewColumnAndFirmDate_SO;
PROCEDURE UpdateNewColumnAndFirmDate_SO_( planId IN NUMBER,
transId IN NUMBER , status out nocopy varchar2) IS
cursor GetISOs is
SELECT d.DEMAND_ID
FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND d.PLAN_ID = planId
AND d.ORIGINATION_TYPE = 30
AND tu.Trans_update_id = transId;
SELECT s.TRANSACTION_ID --IR_Ids
FROM MSC_SUPPLIES s, MSC_DEMANDS d
WHERE s.ORDER_TYPE = 2 -- IR
AND s.TRANSACTION_ID = d.DISPOSITION_ID
AND s.PLAN_ID =planId
AND s.SR_INSTANCE_ID = srIId
AND d.DEMAND_ID IN ( SELECT d.DEMAND_ID
FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND d.PLAN_ID = planId
AND d.ORIGINATION_TYPE = 30
AND tu.Trans_update_id = transId);
SELECT s2.TRANSACTION_ID
FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE s2.ORDER_TYPE = 11 -- IR Shipment
AND s2.PLAN_ID =planId
AND s2.SR_INSTANCE_ID = srIId
AND SO.SR_INSTANCE_ID = srIId
AND dd.SR_INSTANCE_ID = srIId
AND tu.EBS_SR_INSTANCE_ID = srIId
AND s2.REQ_LINE_ID = SO.ORIGINAL_SYSTEM_LINE_REFERENCE
AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID -- maybe use wsh_delive from MTU
AND tu.Trans_update_id = transId;
select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
from msc_transportation_updates
where TRANS_UPDATE_ID = transId;
select d.INVENTORY_ITEM_ID, d.SOURCE_ORGANIZATION_ID into invItemId, orgId
FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
AND d.PLAN_ID = planId
AND d.ORIGINATION_TYPE = 30
AND tu.TRANS_UPDATE_ID = transId;
UPDATE MSC_DEMANDS
Set OTM_ARRIVAL_DATE = newArrivalDate,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE PLAN_ID = planId
AND SR_INSTANCE_ID = SrInstanceId
AND DEMAND_ID = ISO_Ids(i);
UPDATE msc_transportation_updates
SET UPDATED_DUE_DATE = v_new_firm_Date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE TRANS_UPDATE_ID = transId;
UPDATE MSC_SUPPLIES
Set FIRM_DATE = v_new_firm_Date,
APPLIED = 2,
STATUS = 0,
FIRM_PLANNED_TYPE = 1,
OTM_ARRIVAL_DATE = newArrivalDate,
FIRM_QUANTITY = NEW_ORDER_QUANTITY,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
WHERE
PLAN_ID =planId
AND SR_INSTANCE_ID = SrInstanceId
AND TRANSACTION_ID = IR_Ids(i); --in (IR_Ids, IR_Shipment_ids);
end UpdateNewColumnAndFirmDate_SO_;
select PLANNER_CODE into v_plannerCode
from MSC_SYSTEM_ITEMS
where INVENTORY_ITEM_ID = inventoryItemId
and plan_id = -1
and ORGANIZATION_ID = orgId;
select USER_NAME into planner
from MSC_PLANNERS
where PLANNER_CODE = v_plannerCode
and ORGANIZATION_ID = orgId
and SR_INSTANCE_ID = srInstanceId;
select count(1) into nCount
from MSC_TRANSPORTATION_UPDATES
where OTM_RELEASE_LINE_GID = ReleaseLineGid;
select MSC_TRANSPORTATION_UPDATES_s.nextval into key from dual;
insert into MSC_TRANSPORTATION_UPDATES (TRANS_UPDATE_ID, ORDER_TYPE, PO_LINE_LOCATION_ID, PO_LINE_ID, UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID,
OTM_RELEASE_GID, OTM_RELEASE_LINE_GID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
VALUES (key, 1, locationLineId,poId, d1, srInstanceId,
ReleaseGid, ReleaseLineGid, SYSDATE, userId, SYSDATE, userId);
update MSC_TRANSPORTATION_UPDATES
set UPDATED_ARRIVAL_DATE = d1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
where OTM_RELEASE_LINE_GID = ReleaseLineGid;
select trans_update_id into tranzId
from msc_transportation_updates
where OTM_RELEASE_LINE_GID = ReleaseLineGid;
select count(1) into nCount
from MSC_TRANSPORTATION_UPDATES
where OTM_RELEASE_LINE_GID = ReleaseLineGid;
select MSC_TRANSPORTATION_UPDATES_s.nextval into key from dual;
insert into MSC_TRANSPORTATION_UPDATES (TRANS_UPDATE_ID, ORDER_TYPE, UPDATED_ARRIVAL_DATE,EBS_SR_INSTANCE_ID,
OTM_RELEASE_GID,OTM_RELEASE_LINE_GID,WSH_DELIVERY_DETAIL_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
VALUES (key, 2, d1, srInstanceId, ReleaseGid, ReleaseLineGid, ReleaseLineGid, SYSDATE, userId, SYSDATE, userId);
update MSC_TRANSPORTATION_UPDATES
set UPDATED_ARRIVAL_DATE = d1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userId
where OTM_RELEASE_LINE_GID = ReleaseLineGid;
select trans_update_id into tranzId
from msc_transportation_updates
where OTM_RELEASE_LINE_GID = ReleaseLineGid;
select order_type , EBS_SR_INSTANCE_ID, OTM_RELEASE_GID, updated_Arrival_Date
into v_order_type, v_srInstanceId, otmReleaseGid, v_arrival_Date
from msc_transportation_updates
where trans_update_id = tranzId;
SELECT po_line_location_id
INTO v_line_location_id
FROM MSC_TRANSPORTATION_UPDATES
WHERE trans_update_id = tranzId;
SELECT s2.order_number, s2.INVENTORY_ITEM_ID, s2.ORGANIZATION_ID
INTO v_orderNumber, v_itemId, v_orgId
FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
WHERE s2.PLAN_ID =-1
AND s2.SR_INSTANCE_ID = v_srInstanceId
AND SO.SR_INSTANCE_ID = v_srInstanceId
AND dd.SR_INSTANCE_ID = v_srInstanceId
AND tu.EBS_SR_INSTANCE_ID = v_srInstanceId
AND s2.TRANSACTION_ID = SO.SUPPLY_ID
AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
AND tu.Trans_update_id = tranzId
AND s2.order_type = 2;
select distinct order_number, inventory_item_id, ORGANIZATION_ID
from msc_supplies
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID=srInstanceId
AND ORDER_TYPE= 1
AND PO_line_LOCATION_ID = lineLocationId;
SELECT application_id INTO appId FROM fnd_responsibility WHERE responsibility_id = respId;
planId := fnd_profile.value('MSC_PROD_PLAN_ID_FOR_OTM_UPDATES');
procedure PurgeTransportationUpdates is
cursor c_getLine is
SELECT
TRANS_UPDATE_ID, updated_Due_Date
FROM
MSC_TRANSPORTATION_UPDATES;
delete from msc_transportation_updates where trans_update_id = v_trans_id;
end PurgeTransportationUpdates;