APPS.MSC_WS_OTM_BPEL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 45

    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;

Line: 58

select calendar_code into calendarCode
from msc_trading_partners
where partner_type = 3
and sr_instance_id = srInstanceId
and sr_tp_id = orgId;

Line: 67

    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;

Line: 95

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;

Line: 113

                Update_CP(v_line_location_id,v_arrival_Date, status );

Line: 118

end UpdateKeyDateInCP;

Line: 121

procedure UpdateCP_1 ( tranzId IN NUMBER,
                       status OUT NOCOPY VARCHAR2) is
v_line_location_id NUMBER :=0;

Line: 128

   status := 'NO_RECORD_TO_UPDATE';

Line: 133

            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;

Line: 139

            Update_CP(v_line_location_id,v_arrival_Date, status );

Line: 141

end UpdateCP_1;

Line: 143

procedure Update_CP ( lineLocationId IN NUMBER, arrivalDate IN DATE, status OUT NOCOPY VARCHAR2) is
v_cnt NUMBER :=0;

Line: 159

 SELECT  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 = c_lineLocationId
        and plan_id = -1
        and msc_supplies.sr_instance_id = c_srInstanceId
         and msc_supplies.order_type in (1, 11);

Line: 180

    select decode(instr(v_order_Number,'('), 0, v_order_Number, substr(v_order_Number, 1, instr(v_order_Number,'(') - 1))
   into orderNumber
     from dual;

Line: 184

      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;

Line: 201

    select count(*) into v_cnt
    from msc_sup_dem_entries
    where msc_sup_dem_entries.order_number = to_char(orderNumber)
    and msc_sup_dem_entries.line_number = to_number(lineNumber)
--    and msc_sup_dem_entries.release_number = releaseNumber
    and msc_sup_dem_entries.publisher_order_type in (13, 15);

Line: 224

        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;

Line: 269

 /*   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);

Line: 288

end Update_CP;

Line: 297

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;

Line: 325

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;

Line: 339

procedure UpdatePDS( status OUT nocopy VARCHAR2) is

cursor c_getLineIds is
SELECT
   order_type, TRANS_UPDATE_ID
FROM
    MSC_TRANSPORTATION_UPDATES;

Line: 359

    status := 'No Plan to Update.';

Line: 368

                UpdatePDS_Order(v_id, v_order_type, status );

Line: 377

END UpdatePDS;

Line: 379

procedure UpdatePDS_1( tranzId IN NUMBER,
                       bpelOrderType IN NUMBER,
                       status OUT nocopy VARCHAR2) is
plan_id NUMBER :=0;

Line: 386

    status := 'NO_RECORD_TO_UPDATE';

Line: 396

    status := 'No Plan to Update.';

Line: 400

   UpdatePDS_Order(tranzId, bpelOrderType, status );

Line: 405

END UpdatePDS_1;

Line: 409

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;

Line: 435

                    UpdatePDS_PO(planId, transId, status);

Line: 437

                    UpdatePDS_SO(planId, transId, status);

Line: 447

                            UpdatePDS_PO(v_plan_id, transId, status);

Line: 449

                            UpdatePDS_SO(v_plan_id, transId, status);

Line: 456

end UpdatePDS_Order;

Line: 458

PROCEDURE UpdatePDS_PO( planId IN NUMBER,
                        transId IN NUMBER,
                        status OUT nocopy varchar2) IS
isPoShipment NUMBER :=0;

Line: 463

     UpdateNewColumnAndFirmDate_PO(planId, transId, isPoShipment, status);

Line: 467

end UpdatePDS_PO;

Line: 469

PROCEDURE UpdatePDS_SO( planId IN NUMBER,
                        transId IN NUMBER,
                        status OUT nocopy varchar2) IS
begin
       UpdateNewColumnAndFirmDate_SO(planId, transId, status);

Line: 477

end UpdatePDS_SO;

Line: 505

            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;

Line: 520

            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;

Line: 544

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;

Line: 557

                        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;

Line: 568

    status := 'Exception for this order already inserted. Updated new Arrival Date';

Line: 574

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;

Line: 586

        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);

Line: 596

        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;

Line: 608

        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;

Line: 620

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);

Line: 650

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;

Line: 686

            select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID  into newArrivalDate, SrInstanceId
            from msc_transportation_updates
            where TRANS_UPDATE_ID = transId;

Line: 690

            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;

Line: 700

            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;

Line: 733

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;

Line: 746

                        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;

Line: 757

    status := 'Exception for this order already inserted. Updated new Arrival Date';

Line: 761

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;

Line: 774

        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);

Line: 790

        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;

Line: 802

        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;

Line: 816

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);

Line: 839

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;

Line: 855

        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;

Line: 877

select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID  into newArrivalDate, SrInstanceId
from msc_transportation_updates
where TRANS_UPDATE_ID = transId;

Line: 895

   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;

Line: 905

   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;

Line: 931

    status := 'NO_ORDERS_FOUND_TO_UPDATE';

Line: 943

    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;

Line: 953

            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;

Line: 972

status := ' NO_ORDERS_FOUND_TO_UPDATE_IN_PDS';

Line: 977

end UpdateNewColumnAndFirmDate_PO;

Line: 980

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;*/

Line: 1015

select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID  into newArrivalDate, SrInstanceId
from msc_transportation_updates
where TRANS_UPDATE_ID = transId;

Line: 1020

    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;

Line: 1030

        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;

Line: 1047

            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;

Line: 1058

            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;

Line: 1090

    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;

Line: 1098

            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;

Line: 1122

end UpdateNewColumnAndFirmDate_SO;

Line: 1124

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;

Line: 1138

            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);

Line: 1155

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;

Line: 1188

select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID  into newArrivalDate, SrInstanceId
from msc_transportation_updates
where TRANS_UPDATE_ID = transId;

Line: 1195

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;

Line: 1226

            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);

Line: 1273

    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;

Line: 1282

            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);

Line: 1306

end UpdateNewColumnAndFirmDate_SO_;

Line: 1319

select PLANNER_CODE into v_plannerCode
from MSC_SYSTEM_ITEMS
where INVENTORY_ITEM_ID = inventoryItemId
        and plan_id = -1
        and ORGANIZATION_ID = orgId;

Line: 1325

select USER_NAME into planner
from MSC_PLANNERS
where PLANNER_CODE = v_plannerCode
        and ORGANIZATION_ID = orgId
        and SR_INSTANCE_ID = srInstanceId;

Line: 1383

    select count(1) into nCount
    from MSC_TRANSPORTATION_UPDATES
    where OTM_RELEASE_LINE_GID = ReleaseLineGid;

Line: 1389

             select MSC_TRANSPORTATION_UPDATES_s.nextval into key from dual;

Line: 1391

             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);

Line: 1400

             update MSC_TRANSPORTATION_UPDATES
             set UPDATED_ARRIVAL_DATE = d1,
                 LAST_UPDATE_DATE = SYSDATE,
                 LAST_UPDATED_BY = userId
             where OTM_RELEASE_LINE_GID = ReleaseLineGid;

Line: 1406

             select trans_update_id into tranzId
             from msc_transportation_updates
             where OTM_RELEASE_LINE_GID = ReleaseLineGid;

Line: 1448

    select count(1) into nCount
    from MSC_TRANSPORTATION_UPDATES
    where OTM_RELEASE_LINE_GID = ReleaseLineGid;

Line: 1458

                select MSC_TRANSPORTATION_UPDATES_s.nextval into key from dual;

Line: 1460

                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);

Line: 1468

             update MSC_TRANSPORTATION_UPDATES
             set UPDATED_ARRIVAL_DATE = d1,
                 LAST_UPDATE_DATE = SYSDATE,
                 LAST_UPDATED_BY = userId
             where OTM_RELEASE_LINE_GID = ReleaseLineGid;

Line: 1474

              select trans_update_id into tranzId
             from msc_transportation_updates
             where OTM_RELEASE_LINE_GID = ReleaseLineGid;

Line: 1531

    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;

Line: 1541

                SELECT po_line_location_id
                INTO v_line_location_id
                FROM MSC_TRANSPORTATION_UPDATES
                WHERE trans_update_id = tranzId;

Line: 1549

            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;

Line: 1594

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;

Line: 1625

    SELECT application_id INTO appId FROM fnd_responsibility WHERE responsibility_id = respId;

Line: 1636

 planId := fnd_profile.value('MSC_PROD_PLAN_ID_FOR_OTM_UPDATES');

Line: 1686

procedure PurgeTransportationUpdates is
cursor c_getLine is
SELECT
   TRANS_UPDATE_ID, updated_Due_Date
FROM
    MSC_TRANSPORTATION_UPDATES;

Line: 1707

                    delete from msc_transportation_updates where trans_update_id = v_trans_id;

Line: 1713

end PurgeTransportationUpdates;