APPS.INV_CR_ASN_DETAILS SQL Statements

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

Line: 23

PROCEDURE insertrows(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
IS
 l_userid         NUMBER;

Line: 33

insert into WMS_ASN_DETAILS (
                GROUP_ID            ,
		SHIPMENT_NUM        ,
		ORGANIZATION_ID     ,
                DISCREPANCY_REPORTING_CONTEXT ,
		ITEM_ID                       ,
		QUANTITY_EXPECTED             ,
		QUANTITY_ACTUAL               ,
		UNIT_OF_MEASURE_EXPECTED      ,
		UNIT_OF_MEASURE_ACTUAL        ,
		LPN_EXPECTED                  ,
		LPN_ACTUAL                    ,
		ITEM_REVISION_EXPECTED        ,
		ITEM_REVISION_ACTUAL          ,
		LOT_NUMBER_EXPECTED           ,
		LOT_NUMBER_ACTUAL             ,
		SERIAL_NUMBER_EXPECTED        ,
		SERIAL_NUMBER_ACTUAL          ,
                VENDOR_ID                     ,
                VENDOR_SITE_ID                ,
                TRANSACTION_DATE              ,
                LAST_UPDATE_DATE              ,
                LAST_UPDATED_BY               ,
                CREATION_DATE                 ,
                CREATED_BY                    ,
                LAST_UPDATE_LOGIN
) values
(
                p_create_asn_details_rec.GROUP_ID            ,
		p_create_asn_details_rec.SHIPMENT_NUM        ,
		p_create_asn_details_rec.ORGANIZATION_ID     ,
                p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT ,
		p_create_asn_details_rec.ITEM_ID                       ,
		p_create_asn_details_rec.QUANTITY_EXPECTED             ,
		p_create_asn_details_rec.QUANTITY_ACTUAL               ,
		p_create_asn_details_rec.UNIT_OF_MEASURE_EXPECTED      ,
		p_create_asn_details_rec.UNIT_OF_MEASURE_ACTUAL        ,
		p_create_asn_details_rec.LPN_EXPECTED                  ,
		p_create_asn_details_rec.LPN_ACTUAL                    ,
		p_create_asn_details_rec.ITEM_REVISION_EXPECTED        ,
		p_create_asn_details_rec.ITEM_REVISION_ACTUAL          ,
		p_create_asn_details_rec.LOT_NUMBER_EXPECTED           ,
		p_create_asn_details_rec.LOT_NUMBER_ACTUAL             ,
		p_create_asn_details_rec.SERIAL_NUMBER_EXPECTED        ,
		p_create_asn_details_rec.SERIAL_NUMBER_ACTUAL          ,
		p_create_asn_details_rec.VENDOR_ID                     ,
		p_create_asn_details_rec.VENDOR_SITE_ID                ,
		p_create_asn_details_rec.TRANSACTION_DATE              ,
                sysdate,
                l_userid,
                sysdate,
                l_userid,
                l_loginid
);

Line: 88

END insertrows;

Line: 90

PROCEDURE insert_asn_item_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp
  		      )
IS

l_exists varchar2(10);

Line: 105

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
;

Line: 113

update WMS_ASN_DETAILS set quantity_actual = (nvl(quantity_actual,0) + p_create_asn_details_rec.QUANTITY_ACTUAL) ,
                       unit_of_measure_actual = p_create_asn_details_rec.unit_of_measure_actual,
                       item_revision_actual = p_create_asn_details_rec.item_revision_actual,
                       transaction_date = sysdate,
                       last_update_date = sysdate,
                       last_updated_by  = l_userid,
                       last_update_login = l_loginid
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
;

Line: 128

insertrows(p_create_asn_details_rec);

Line: 130

END insert_asn_item_details;

Line: 132

PROCEDURE insert_asn_item_details_intf(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp
                      )
IS

l_exists varchar2(10);

Line: 147

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
;

Line: 155

update WMS_ASN_DETAILS set quantity_expected = ( quantity_expected + p_create_asn_details_rec.QUANTITY_EXPECTED) ,
                        transaction_date = sysdate,
                        last_update_date = sysdate,
                        last_updated_by  = l_userid,
                        last_update_login = l_loginid
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
;

Line: 168

insertrows(p_create_asn_details_rec);

Line: 170

END insert_asn_item_details_intf;

Line: 202

PROCEDURE insert_asn_lot_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
IS

l_exists varchar2(10);

Line: 211

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and lpn_expected = p_create_asn_details_rec.lpn_expected
 and lot_number_expected =  p_create_asn_details_rec.lot_number_expected
 and rownum < 2
;

Line: 226

insertrows(p_create_asn_details_rec);

Line: 228

END insert_asn_lot_details;

Line: 232

PROCEDURE insert_asn_lpn_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )

IS

l_exists varchar2(10);

Line: 242

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and lpn_expected = p_create_asn_details_rec.lpn_expected
 and rownum < 2
;

Line: 256

insertrows(p_create_asn_details_rec);

Line: 258

END insert_asn_lpn_details;

Line: 261

PROCEDURE update_asn_lpn_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )

IS

l_exists varchar2(10);

Line: 273

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and ( lpn_expected = p_create_asn_details_rec.lpn_actual
      or lpn_actual = p_create_asn_details_rec.lpn_actual )
 and rownum < 2
;

Line: 285

update WMS_ASN_DETAILS set
  quantity_actual = nvl(quantity_actual, 0) + p_create_asn_details_rec.QUANTITY_ACTUAL,
  lpn_actual      = p_create_asn_details_rec.LPN_ACTUAL ,
  last_update_date = sysdate,
  last_updated_by  = l_userid,
  last_update_login = l_loginid
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and ( lpn_expected = p_create_asn_details_rec.lpn_actual
      or lpn_actual = p_create_asn_details_rec.lpn_actual )
;

Line: 301

insertrows(p_create_asn_details_rec);

Line: 303

END update_asn_lpn_details;

Line: 306

PROCEDURE update_asn_lot_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
IS

l_exists varchar2(10);

Line: 322

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and ( (lot_number_expected = p_create_asn_details_rec.lot_number_actual and lpn_expected = p_create_asn_details_rec.lpn_actual)
       or
       (lot_number_actual = p_create_asn_details_rec.lot_number_actual and lpn_actual = p_create_asn_details_rec.lpn_actual)
     )
 and rownum < 2
;

Line: 335

update WMS_ASN_DETAILS set
  quantity_actual = nvl(quantity_actual,0) + p_create_asn_details_rec.QUANTITY_ACTUAL ,
  lot_number_actual = p_create_asn_details_rec.LOT_NUMBER_ACTUAL,
  lpn_actual        = p_create_asn_details_rec.LPN_ACTUAL,
  last_update_date = sysdate,
  last_updated_by  = l_userid,
  last_update_login = l_loginid
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and ( (lot_number_expected = p_create_asn_details_rec.lot_number_actual and lpn_expected = p_create_asn_details_rec.lpn_actual)
       or
       (lot_number_actual = p_create_asn_details_rec.lot_number_actual and lpn_actual = p_create_asn_details_rec.lpn_actual)
     )
;

Line: 354

insertrows(p_create_asn_details_rec);

Line: 356

END update_asn_lot_details;

Line: 359

PROCEDURE insert_asn_ser_details_exp(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
IS
l_exists varchar2(10);

Line: 366

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and lpn_expected = p_create_asn_details_rec.lpn_expected
 and serial_number_expected =  p_create_asn_details_rec.serial_number_expected
 and rownum < 2
;

Line: 381

insertrows(p_create_asn_details_rec);

Line: 383

END insert_asn_ser_details_exp;

Line: 387

PROCEDURE insert_asn_ser_details_act(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
IS
l_exists varchar2(10);

Line: 394

select 1 into l_exists
  from WMS_ASN_DETAILS
 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
 and lpn_actual = p_create_asn_details_rec.lpn_actual
 and serial_number_actual =  p_create_asn_details_rec.serial_number_actual
 and rownum < 2
;

Line: 409

insertrows(p_create_asn_details_rec);

Line: 411

END insert_asn_ser_details_act;

Line: 423

     For c_item_details in ( select * from WMS_ASN_DETAILS
                              where shipment_num = p_shipment_num
                                and organization_id = p_organization_id
                              order by discrepancy_reporting_context
                           )
     Loop

          -- Check Item
          if c_item_details.discrepancy_reporting_context = 'I'
          then
             if (
                    (c_item_details.QUANTITY_EXPECTED <> c_item_details.QUANTITY_ACTUAL)
                or
                    (c_item_details.UNIT_OF_MEASURE_EXPECTED <> c_item_details.UNIT_OF_MEASURE_ACTUAL)
                or
                    (c_item_details.ITEM_REVISION_EXPECTED <> c_item_details.ITEM_REVISION_ACTUAL)
                )
             then
               return FALSE;

Line: 480

                   select 1
                     into l_exists
                     from WMS_ASN_DETAILS
                    where shipment_num=p_shipment_num
                      and organization_id = p_organization_id
                      and serial_number_actual = c_item_details.serial_number_expected
                      and lpn_actual           = c_item_details.lpn_expected
                    ;

Line: 496

                   select 1
                     into l_exists
                     from WMS_ASN_DETAILS
                    where shipment_num=p_shipment_num
                      and organization_id = p_organization_id
                      and serial_number_expected = c_item_details.serial_number_actual
                      and lpn_expected           = c_item_details.lpn_actual
                    ;

Line: 572

   select quantity_received,
          quantity_shipped
     into l_received_quantity ,
          l_shipped_quantity
     FROM RCV_SHIPMENT_LINES RSL
    WHERE RSL.SHIPMENT_LINE_ID = p_rcv_rcpt_rec.rcv_shipment_line_id
   ;

Line: 619

insert_asn_item_details(l_create_asn_details_rec); */

Line: 648

select wlpn.license_plate_number,
       wlpn.lpn_id
  from wms_license_plate_numbers wlpn
 where wlpn.lpn_id = p_rcv_transaction_rec.lpn_id
)
Loop

initialize_details(l_create_asn_details_rec);

Line: 663

 update_asn_lpn_details(l_create_asn_details_rec);

Line: 709

       select quantity
         into p_lot_quantity
         from mtl_txn_request_lines
        where organization_id = l_create_asn_details_rec.organization_id
          and inventory_item_id = l_create_asn_details_rec.ITEM_ID
          and lot_number = l_create_asn_details_rec.LOT_NUMBER_ACTUAL
          and reference = 'SHIPMENT_LINE_ID'
          and reference_type_code = 8
          and reference_id = p_rcv_rcpt_rec.rcv_shipment_line_id
          and txn_source_id = p_interface_transaction_id
          and lpn_id = c_lpn_actual.lpn_id
        ;

Line: 741

      update_asn_lot_details(l_create_asn_details_rec);

Line: 752

   (select msn.serial_number serial_number
      from mtl_serial_numbers msn
     where msn.inventory_item_id = p_rcv_rcpt_rec.item_id
       and ( l_create_asn_details_rec.lot_number_actual is not null
       and msn.lot_number = l_create_asn_details_rec.lot_number_actual)
       and msn.lpn_id = c_lpn_actual.lpn_id
   )
   loop

   initialize_details(l_create_asn_details_rec);

Line: 766

   insert_asn_ser_details_act(l_create_asn_details_rec);

Line: 779

   (select msn.serial_number serial_number
      from mtl_serial_numbers msn
     where msn.inventory_item_id = p_rcv_rcpt_rec.item_id
       and msn.lpn_id = c_lpn_actual.lpn_id
   )
   loop
   initialize_details(l_create_asn_details_rec);

Line: 790

   insert_asn_ser_details_act(l_create_asn_details_rec);

Line: 822

update WMS_ASN_DETAILS set discrepancy_status = 'S'
 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = l_create_asn_details_rec.organization_id
   and discrepancy_reporting_context = 'I'
   and item_id = l_create_asn_details_rec.item_id
;

Line: 829

update WMS_ASN_DETAILS set discrepancy_status = 'F'
 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = l_create_asn_details_rec.organization_id
   and discrepancy_reporting_context = 'I'
   and item_id = l_create_asn_details_rec.item_id
;

Line: 921

   select shipment_num,
          vendor_id,
          vendor_site_id
     into l_shipment_num,
          l_vendor_id,
          l_vendor_site_id
     FROM RCV_SHIPMENT_HEADERS RSH
    WHERE RSH.shipment_header_id = p_interface_transaction_rec.shipment_header_id ;

Line: 958

insert_asn_item_details_intf(l_create_asn_details_rec);

Line: 978

(select wlpn.license_plate_number,
       wlpc.quantity,
       wlpc.lot_number,
       wlpn.lpn_id
  from wms_lpn_contents wlpc,
       wms_license_plate_numbers wlpn
 where wlpc.parent_lpn_id = wlpn.lpn_id
   and wlpn.source_header_id  = p_interface_transaction_rec.shipment_header_id
   and wlpc.inventory_item_id = p_interface_transaction_rec.item_id
   order by wlpn.license_plate_number, wlpc.lot_number
)
Loop

-- Find The Expected LPN Quantity and populate the rows.
initialize_details(l_create_asn_details_rec);

Line: 997

insert_asn_lpn_details(l_create_asn_details_rec);

Line: 1015

    insert_asn_lot_details(l_create_asn_details_rec);

Line: 1027

(select serial_number,
        lot_number
   from mtl_serial_numbers
  where inventory_item_id = p_interface_transaction_rec.item_id
    and lpn_id = c_lpn.lpn_id
)
loop

initialize_details(l_create_asn_details_rec);

Line: 1040

insert_asn_ser_details_exp(l_create_asn_details_rec);

Line: 1072

update WMS_ASN_DETAILS set discrepancy_status = 'E'
 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = l_create_asn_details_rec.organization_id
   and discrepancy_reporting_context = 'I'
   and item_id = l_create_asn_details_rec.item_id
;

Line: 1120

PROCEDURE update_asn_item_details
( p_group_id IN NUMBER )
IS

CURSOR  rti_record
IS
SELECT * FROM rcv_transactions_interface
WHERE group_id = p_group_id ;

Line: 1140

print_debug('In the procedure update_asn_item_details', 1);

Line: 1152

SELECT shipment_num
INTO l_shipment_num
FROM rcv_shipment_headers
WHERE shipment_header_id= l_rti_record.shipment_header_id ;

Line: 1157

SELECT quantity_shipped,
       item_id,
       unit_of_measure,
       item_revision
INTO l_shipped_quantity, l_item_id, l_unit_of_measure, l_item_revision
FROM RCV_SHIPMENT_LINES RSL
WHERE RSL.SHIPMENT_LINE_ID = l_rti_record.shipment_line_id ;

Line: 1193

insert_asn_item_details(l_create_asn_details_rec);

Line: 1199

update WMS_ASN_DETAILS set discrepancy_status = 'S'
 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = l_create_asn_details_rec.organization_id
   and discrepancy_reporting_context = 'I'
   and item_id = l_create_asn_details_rec.item_id
;

Line: 1207

update WMS_ASN_DETAILS set discrepancy_status = 'F'
 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
   and organization_id = l_create_asn_details_rec.organization_id
   and discrepancy_reporting_context = 'I'
   and item_id = l_create_asn_details_rec.item_id
;

Line: 1218

END update_asn_item_details;