eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.RCV_GML_CONV_MIG SQL Statements

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

Line: 26


   Update_rcv_lot_transactions;

Line: 29


   Update_rcv_supply;

Line: 32


   Update_rcv_lots_supply;

Line: 53


PROCEDURE update_rcv_lot_transactions IS

Cursor CR_GET_TRX_LOTS IS
SELECT rlt.rowid,
       rlt.transaction_id transaction_id,
       rlt.source_transaction_id source_transaction_id,
       rt.SHIPMENT_HEADER_ID shipment_header_id,
       rlt.SHIPMENT_LINE_ID shipment_line_id,
       rlt.lot_num lot_num,
       rlt.sublot_num,
       rlt.item_id,
       rt.organization_id organization_id,
       rt.subinventory subinventory,
       rt.locator_id locator_id,
       rlt.correction_transaction_id
FROM   rcv_transactions rt ,
       rcv_lot_transactions rlt,
       mtl_parameters mp
WHERE  rlt.lot_transaction_type = 'TRANSACTION'
and    rlt.source_transaction_id = rt.transaction_id
and    (rlt.sublot_num <> '-1' or rlt.sublot_num is NULL)
and    rt.organization_id = mp.organization_id
and    mp.process_enabled_flag = 'Y'
and not exists
      (SELECT 'x'
       FROM   GML_RCV_LOTS_MIGRATION glm
       WHERE  table_name = 'RCV_LOT_TRANSACTIONS'
       AND    glm.source_transaction_id = rlt.source_transaction_id
       AND    glm.transaction_id = rlt.transaction_id
       AND    glm.correction_transaction_id = rlt.correction_transaction_id);

Line: 85


SELECT rlt.rowid,
       rlt.transaction_id transaction_id,
       rsl.SHIPMENT_HEADER_ID shipment_header_id,
       rsl.SHIPMENT_LINE_ID shipment_line_id,
       rlt.lot_num lot_num,
       rlt.sublot_num,
       rlt.item_id,
       rsl.to_organization_id organization_id,
       rsl.to_subinventory subinventory,
       rsl.locator_id locator_id,
       rlt.correction_transaction_id,
       rlt.source_transaction_id
FROM   rcv_lot_transactions rlt ,
       rcv_shipment_lines rsl,
       mtl_parameters mp
WHERE  rlt.lot_transaction_type = 'SHIPMENT'
and    rsl.shipment_line_id = rlt.shipment_line_id
and    (rlt.sublot_num <> '-1' or rlt.sublot_num IS NULL)
and    rsl.to_organization_id = mp.organization_id
and    mp.process_enabled_flag = 'Y'
and not exists
      (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
       WHERE  table_name = 'RCV_LOT_TRANSACTIONS'
         And glm.shipment_line_id = rlt.shipment_line_id);

Line: 138


     Update rcv_lot_transactions
     set    LOT_NUM = l_lot_num
     where  rowid = cr_rec.rowid;

Line: 144


     INSERT INTO GML_RCV_LOTS_MIGRATION
            ( TABLE_NAME,
              TRANSACTION_ID,
              SOURCE_TRANSACTION_ID,
              SHIPMENT_LINE_ID,
              CORRECTION_TRANSACTION_ID,
              CREATED_BY,
              CREATION_DATE,
              LAST_UPDATED_BY,
              LAST_UPDATE_DATE)
     VALUES ( 'RCV_LOT_TRANSACTIONS',
              cr_rec.transaction_id,
              cr_rec.source_transaction_id,
              cr_rec.shipment_line_id,
              cr_rec.correction_transaction_id,
              1,
              sysdate,
              1,
              sysdate);

Line: 167


               insert into gml_po_mig_errors
				(migration_type,po_header_id,po_line_id,line_location_id,
				 transaction_id, shipment_header_id,shipment_line_id,
				 column_name,table_name,error_message,
				 creation_date,last_update_date)
			values ('CONVERGENCE',NULL, NULL, NULL,
				cr_rec.transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
				'LOT_NUM','RCV_LOT_TRANSACTIONS',
				'ERROR DERIVING NEW LOT NUM-'||substr(l_errm,1,1970),sysdate,sysdate);

Line: 201


      UPDATE rcv_lot_transactions
      SET    LOT_NUM = l_lot_num
     where  rowid = cr_rec1.rowid;

Line: 205


      INSERT INTO GML_RCV_LOTS_MIGRATION
         ( TABLE_NAME,
           TRANSACTION_ID,
           SOURCE_TRANSACTION_ID,
           SHIPMENT_LINE_ID,
           CORRECTION_TRANSACTION_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_DATE)
      VALUES ( 'RCV_LOT_TRANSACTIONS',
           cr_rec1.transaction_id,
           cr_rec1.source_transaction_id,
           cr_rec1.shipment_line_id,
           cr_rec1.correction_transaction_id,
           1,
           sysdate,
           1,
           sysdate);

Line: 228


            insert into gml_po_mig_errors
				(migration_type,po_header_id,po_line_id,line_location_id,
				 transaction_id, shipment_header_id,shipment_line_id,
				 column_name,table_name,error_message,
				 creation_date,last_update_date)
			values ('CONVERGENCE',NULL, NULL, NULL,
				cr_rec1.transaction_id, cr_rec1.shipment_header_id, cr_rec1.shipment_line_id,
				'LOT_NUM','RCV_LOT_TRANSACTIONS',
				'ERROR DERIVING NEW LOT NUM-'||substr(l_errm,1,1970),sysdate,sysdate);

Line: 241


END Update_rcv_lot_transactions;

Line: 261


PROCEDURE update_rcv_supply IS

CURSOR cr_get_supply IS
SELECT rs.rowid,
       rs.quantity,
       rs.Unit_of_measure,
       rs.Secondary_quantity,
       rs.Secondary_unit_of_measure,
       msi.secondary_uom_code,
       rs.To_organization_id,
       rs.To_subinventory,
       rs.To_locator_id,
       rs.Item_id,
       rs.po_header_id    ,
       rs.po_line_id      ,
       rs.po_line_location_id   ,
       rs.shipment_header_id    ,
       rs.shipment_line_id      ,
       rs.rcv_transaction_id
FROM   rcv_supply rs ,
       mtl_system_items_b msi,
       mtl_parameters mp
WHERE  nvl(rs.quantity,0) <> 0
AND    rs.secondary_quantity is null
AND    rs.item_id = msi.INVENTORY_ITEM_ID
AND    msi.ORGANIZATION_ID = to_organization_id
AND    msi.tracking_quantity_ind = 'PS'
AND    rs.to_organization_id = mp.organization_id
AND    mp.process_enabled_flag = 'Y';

Line: 304


      SELECT UNIT_OF_MEASURE
      INTO   l_secondary_unit_of_measure
      FROM   MTL_UNITS_OF_MEASURE
      WHERE  UOM_CODE = cr_rec.secondary_uom_code;

Line: 322


        UPDATE rcv_supply
        SET    secondary_quantity = l_secondary_quantity,
               secondary_unit_of_measure = l_secondary_unit_of_measure
        WHERE  rowid = cr_rec.rowid;

Line: 329


            insert into gml_po_mig_errors
				(migration_type,po_header_id,po_line_id,line_location_id,
				 transaction_id, shipment_header_id,shipment_line_id,
				 column_name,table_name,error_message,
				 creation_date,last_update_date)
			values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.po_line_location_id,
				cr_rec.rcv_transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
				'SECONDARY_QUANTITY','RCV_SUPPLY',
				'ERROR DERIVING SECONDARY_QUANTITY FROM QUANTITY',sysdate,sysdate);

Line: 340


            insert into gml_po_mig_errors
				(migration_type,po_header_id,po_line_id,line_location_id,
				 transaction_id, shipment_header_id,shipment_line_id,
				 column_name,table_name,error_message,
				 creation_date,last_update_date)
			values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.po_line_location_id,
				cr_rec.rcv_transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
				'SECONDARY_QUANTITY','RCV_SUPPLY',
				'WHEN OTHERS IN DERIVING SECONDARY_QUANTITY FROM QUANTITY-'||substr(l_errm,1,1925),sysdate,sysdate);

Line: 353


END Update_rcv_supply;

Line: 371


PROCEDURE update_rcv_lots_supply IS

CURSOR CR_GET_LOT_SUPPLY IS
SELECT rls.rowid,
       rls.transaction_id transaction_id,
       rt.SHIPMENT_HEADER_ID shipment_header_id,
       rls.SHIPMENT_LINE_ID shipment_line_id,
       rls.lot_num lot_num,
       rls.sublot_num,
       rt.organization_id organization_id,
       rt.subinventory subinventory,
       rt.locator_id locator_id,
       rls.reason_code,
       rsl.item_id
FROM   rcv_transactions rt ,
       rcv_lots_supply rls,
       rcv_shipment_lines rsl,
       mtl_parameters mp
WHERE  rls.supply_type_code = 'RECEIVING'
and    rls.transaction_id = rt.transaction_id
and    (rls.sublot_num <> '-1' or rls.sublot_num IS NULL)
AND    rt.organization_id = mp.organization_id
AND    rt.shipment_header_id = rsl.shipment_header_id
AND    rt.shipment_line_id = rsl.shipment_line_id
AND    mp.process_enabled_flag = 'Y'
and not exists
       (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
        WHERE  table_name = 'RCV_LOTS_SUPPLY'
        and    glm.transaction_id = rls.transaction_id
        and    glm.shipment_line_id = rls.shipment_line_id)
ORDER BY rls.transaction_id;

Line: 404


SELECT rlt.rowid,
       rsl.SHIPMENT_HEADER_ID shipment_header_id,
       rsl.SHIPMENT_LINE_ID shipment_line_id,
       rlt.lot_num lot_num,
       rlt.sublot_num,
       rsl.item_id,
       rsl.to_organization_id organization_id,
       rsl.to_subinventory subinventory,
       rsl.locator_id locator_id,
       rlt.reason_code reason_code,
       rlt.transaction_id
from   rcv_lots_supply rlt ,
       rcv_shipment_lines rsl,
       mtl_parameters mp
WHERE  rlt.supply_type_code = 'SHIPMENT'
and    rsl.shipment_line_id = rlt.shipment_line_id
and    (rlt.sublot_num <> '-1' or rlt.sublot_num is NULL)
AND    mp.organization_id = rsl.to_organization_id
AND    mp.process_enabled_flag = 'Y'
/*and not exists
      (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
       WHERE  table_name = 'RCV_LOTS_SUPPLY'
         And glm.shipment_line_id = rls.shipment_line_id)*/
ORDER BY rsl.shipment_line_id
FOR UPDATE OF LOT_NUM;

Line: 459


         Select reason_id
         into l_reason_id
         from mtl_transaction_reasons
         where reason_name = cr_rec.reason_code;

Line: 469


      UPDATE rcv_lots_supply
      SET    LOT_NUM = l_lot_num,
             REASON_ID = l_reason_id
      WHERE  rowid = cr_rec.rowid;

Line: 474


      INSERT INTO GML_RCV_LOTS_MIGRATION
         ( TABLE_NAME,
           TRANSACTION_ID,
           SOURCE_TRANSACTION_ID,
           SHIPMENT_LINE_ID,
           CORRECTION_TRANSACTION_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_DATE)
      VALUES ( 'RCV_LOTS_SUPPLY',
           cr_rec.transaction_id,
           NULL,
           cr_rec.shipment_line_id,
           NULL,
           1,
           sysdate,
           1,
           sysdate);

Line: 497


            insert into gml_po_mig_errors
				(migration_type,po_header_id,po_line_id,line_location_id,
				 transaction_id, shipment_header_id,shipment_line_id,
				 column_name,table_name,error_message,
				 creation_date,last_update_date)
			values ('CONVERGENCE',NULL, NULL, NULL,
				cr_rec.transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
				'LOT_NUM','RCV_LOT_SUPPLY',
				'ERROR DERIVING LOT_NUM-'||substr(l_errm,1,1975),sysdate,sysdate);

Line: 527


         Select reason_id
         into l_reason_id
         from mtl_transaction_reasons
         where reason_name = cr_rec1.reason_code;

Line: 537


      UPDATE rcv_lots_supply
      SET    LOT_NUM = l_lot_num,
             REASON_ID = l_reason_id
      WHERE  rowid = cr_rec1.rowid;

Line: 542


      INSERT INTO GML_RCV_LOTS_MIGRATION
         ( TABLE_NAME,
           TRANSACTION_ID,
           SOURCE_TRANSACTION_ID,
           SHIPMENT_LINE_ID,
           CORRECTION_TRANSACTION_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_DATE)
      VALUES ( 'RCV_LOTS_SUPPLY',
           cr_rec1.transaction_id,
           NULL,
           cr_rec1.shipment_line_id,
           NULL,
           1,
           sysdate,
           1,
           sysdate);

Line: 566


            insert into gml_po_mig_errors
				(migration_type,po_header_id,po_line_id,line_location_id,
				 transaction_id, shipment_header_id,shipment_line_id,
				 column_name,table_name,error_message,
				 creation_date,last_update_date)
			values ('CONVERGENCE',NULL, NULL, NULL,
				NULL, cr_rec1.shipment_header_id, cr_rec1.shipment_line_id,
				'LOT_NUM','RCV_LOT_SUPPLY',
				'ERROR DERIVING LOT_NUM-'||substr(l_errm,1,1975),sysdate,sysdate);

Line: 578


END Update_rcv_lots_supply;