FRM-40212: Invalid value for field SERIAL_NUMBER

SELECT group_mark_id,

  line_mark_id,

  lot_line_mark_id

FROM mtl_serial_numbers

WHERE inventory_item_id=2701

AND serial_number     IN ('12703','12717');

SELECT inventory_item_id "Item Id", serial_number "Serial Number",

       current_organization_id "Organization Id", lot_number "Lot Number",

       DECODE (current_status,

               1, 'Defined but not used',

               3, 'Resides in Stores',

               4, 'Out of Stores',

               5, 'Intransit',

               6, 'Invalid',

               NULL, 'Verify Serial Number',

               current_status

              ) "Status",

       current_subinventory_code "Subinv", current_locator_id "Locator",

       group_mark_id "Group Mark Id", line_mark_id "Line Mark Id",

       lot_line_mark_id "Lot Line Mark Id", last_update_date

  from mtl_serial_numbers

 where  1 = 1

 and inventory_item_id = 4325

 and current_subinventory_code='EIS001062C'

 and serial_number in ('TB1-M000996','TB1-M000997','TB1-M000998','TB1-M000999','TB1-M001000','TB1-M001001','TB1-M001007','TB1-M001008')

  --UPDATE mtl_serial_numbers

SET group_mark_id      =NULL,

  line_mark_id         =NULL,

  lot_line_mark_id     =NULL

WHERE inventory_item_id=4325

 and current_subinventory_code='EIS001062C'

AND serial_number     IN ('TB1-M000996','TB1-M000997','TB1-M000998','TB1-M000999','TB1-M001000','TB1-M001001','TB1-M001007','TB1-M001008');

commit

SQL Query to check Workflow Override Address

SQL Query to check Workflow Override Address 

select fscpv.parameter_value test_address
from   fnd_svc_comp_params_tl fscpt
,      fnd_svc_comp_param_vals fscpv
,      fnd_svc_components fsc
where  fscpt.parameter_id = fscpv.parameter_id
and    fscpv.component_id = fsc.component_id
and    fscpt.display_name = 'Test Address'
and    fsc.component_name = 'Workflow Notification Mailer';

API Script to Update Cust Acct Sites

DECLARE
   l_init_msg_list           VARCHAR2 (1000) := FND_API.G_TRUE;
   l_cust_acct_site_rec      HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
   l_return_status           VARCHAR2 (1000);
   l_object_version_number   NUMBER;
   l_msg_count               NUMBER;
   l_msg_data                VARCHAR2 (1000);
   L_SITE_USE_ID             NUMBER;
   p_cust_acct_site_id number;
   p_object_version_number number;
BEGIN
select cust_acct_site_id,
object_version_number
into p_cust_acct_site_id,p_object_version_number
from hz_cust_acct_sites_all where party_site_id=34190;

  l_cust_acct_site_rec.cust_acct_site_id := p_cust_acct_site_id;
  l_cust_acct_site_rec.attribute1:='N';
  l_object_version_number:=p_object_version_number;
  l_cust_acct_site_rec.attribute2:=1;
  l_cust_acct_site_rec.attribute3:=1;
  hz_cust_account_site_v2pub.update_cust_acct_site ('T',
                                                     l_cust_acct_site_rec,
                                                     l_object_version_number,
                                                     l_return_status,
                                                     l_msg_count,
                                                     l_msg_data);

   DBMS_OUTPUT.put_line (l_return_status ||':' ||l_msg_data);
   COMMIT;
END;

Script to Deletes the duplicate/dummy Inter-org shipment

/**************************************************************************
**                                                                       **
**  This datafix script deletes the duplicate/dummy Inter-org shipment/  **
**  Internal Sales Order shipment lines for which there is no ISSUE OUT  **
**  transaction happened in the the source/sending organization.         **
**                                                                       **
**                                                                       **
**   Records from the following tables are deleted:                      **
**     1) rcv_shipment_headers                                           **
**     2) rcv_shipment_lines                                             **
**     3) rcv_lots_supply                                                **
**     4) rcv_lot_transacions                                            **
**     5) rcv_serial_transactions                                        **
**     6) rcv_serials_supply                                             **
**     7) mtl_supply                                                     **
**     8) mtl_material_transactions_temp                                 **
**     9) rcv_transactions                                               **
**                                                                       **
**                                                                       **
**                                                                       **
**     Documentation detail for the updates need to be added here        **
**                                                                       **
**                                                                       **
**                                                                       **
**   Note:                                                               **
**   Please take a backup of the above tables before running script.     **
***************************************************************************/

SET SERVEROUTPUT OFF;

DECLARE

             l_is_r12_instance NUMBER := 0;
            l_shipment_line_id NUMBER;
          l_shipment_header_id NUMBER;
     l_prev_shipment_header_id NUMBER := -999;
                l_shipment_num rcv_shipment_headers.shipment_num%TYPE;  
                p_patch_level  VARCHAR2(30);            

     l_requisition_line_id NUMBER;      --for Req Line Update.
     l_quantity_received NUMBER := 0;   --for Req Line Update
     l_item_id        NUMBER;           --from rsl
     tmp_rt_quantity  NUMBER := 0;
     tmp_con_quantity NUMBER := 0;
     l_req_uom    VARCHAR2(30);         --requisition line UOM
     l_rt_uom     VARCHAR2(30);         --RT UOM


CURSOR c_shipment IS
    SELECT rsl.shipment_header_id,
           rsl.shipment_line_id,
           rsl.requisition_line_id,
           rsl.item_id,
           rsh.shipment_num
    FROM   rcv_shipment_lines rsl,
           rcv_shipment_headers rsh
    WHERE  rsl.shipment_header_id = rsh.shipment_header_id
  --  AND    rsh.creation_date  >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
   -- AND    rsh.creation_date  <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
    and  rsh.SHIPMENT_HEADER_ID=1104033
    AND    rsl.mmt_transaction_id IS NOT null
    AND    rsh.receipt_source_code = 'INVENTORY'
    AND EXISTS
       (
        SELECT 1
        FROM mtl_parameters mp
        WHERE mp.organization_id = rsh.organization_id
       )
    AND NOT EXISTS--To check ISSUE OUT txn happened in source orgn
       (
        SELECT 1
        FROM mtl_material_transactions mmt
        WHERE mmt.transaction_id = rsl.mmt_transaction_id
        AND   mmt.transaction_source_type_id = 13
        AND   mmt.transaction_action_id in (3,21)   
       )
    AND NOT EXISTS
       (
        SELECT 1
        FROM  mtl_material_transactions mmt,
              rcv_transactions rt
        WHERE mmt.rcv_transaction_id = rt.transaction_id
        AND   rt.shipment_line_id = rsl.shipment_line_id
        AND   rt.transaction_type = 'DELIVER'
       )         
    UNION
    SELECT rsl.shipment_header_id,
           rsl.shipment_line_id,
           rsl.requisition_line_id,
           rsl.item_id,
           rsh.shipment_num
    FROM   rcv_shipment_lines rsl,
           rcv_shipment_headers rsh
    WHERE  rsl.shipment_header_id = rsh.shipment_header_id
  --  AND    rsh.creation_date  >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
  --  AND    rsh.creation_date  <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
     and  rsh.SHIPMENT_HEADER_ID=1104033
    AND    rsl.mmt_transaction_id IS NOT null
    AND    rsh.receipt_source_code = 'INTERNAL ORDER'
    AND EXISTS
       (
        SELECT 1
        FROM mtl_parameters mp
        WHERE mp.organization_id = rsh.organization_id
       )
    AND NOT EXISTS--To check ISSUE OUT txn happened in source orgn
       (
        SELECT 1
        FROM mtl_material_transactions mmt
        WHERE mmt.transaction_id = rsl.mmt_transaction_id
        AND   mmt.transaction_source_type_id = 8
        AND   mmt.transaction_action_id in (1,3,21, 2)
       )   
    AND NOT EXISTS
       (
        SELECT 1
        FROM  mtl_material_transactions mmt,
              rcv_transactions rt
        WHERE mmt.rcv_transaction_id = rt.transaction_id
        AND   rt.shipment_line_id = rsl.shipment_line_id
        AND   rt.transaction_type = 'DELIVER'
       )       
    ORDER BY 1;
   
    CURSOR rt_remain(t_requisition_line_id number) is
    SELECT rt.quantity,
           rt.unit_of_measure
    FROM rcv_transactions rt
    WHERE rt.requisition_line_id = t_requisition_line_id
        AND ( ( rt.TRANSACTION_TYPE = 'RECEIVE' AND rt.INSPECTION_STATUS_CODE = 'NOT INSPECTED')
              OR
              ( rt.TRANSACTION_TYPE = 'CORRECT' AND rt.INSPECTION_STATUS_CODE = 'NOT INSPECTED')
            );

BEGIN
          OPEN c_shipment;

          LOOP

            FETCH c_shipment into l_shipment_header_id,l_shipment_line_id,l_requisition_line_id, l_item_id, l_shipment_num;

            EXIT WHEN c_shipment%NOTFOUND;

            IF l_prev_shipment_header_id <> l_shipment_header_id THEN
              dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
              dbms_output.put_line('Datafix for the shipment:'||l_shipment_num);
            END IF;
            dbms_output.put_line('shipment_line_id : '||l_shipment_line_id);
           
            DELETE FROM rcv_shipment_lines
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_shipment_lines.');

            DELETE FROM rcv_lots_supply
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_lots_supply.');

            DELETE FROM rcv_lot_transactions
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_lot_transactions.');

            DELETE FROM rcv_serials_supply
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_serials_supply.');

            DELETE FROM rcv_serial_transactions
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_serial_transactions.');

            DELETE FROM mtl_supply
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from mtl_supply.');

            DELETE FROM rcv_supply
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_supply.');
           
            DELETE FROM mtl_material_transactions_temp
            WHERE rcv_transaction_id IN (SELECT transaction_id
                                         FROM   rcv_transactions
                                         WHERE  transaction_type = 'DELIVER'
                                         AND    shipment_line_id = l_shipment_line_id);
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from mtl_material_transactions_temp.');
           

            DELETE FROM rcv_transactions
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_transactions.');

/*update requisition line */
            IF SQL%ROWCOUNT > 0 AND nvl(l_requisition_line_id, -99) <> -99 THEN
               BEGIN
                   l_quantity_received := 0;
                  
                   SELECT unit_meas_lookup_code into l_req_uom
                   FROM po_requisition_lines_all
                   WHERE requisition_line_id = l_requisition_line_id;
                  
                   OPEN rt_remain(l_requisition_line_id);
                   LOOP
                       FETCH rt_remain into tmp_rt_quantity, l_rt_uom;
                       EXIT WHEN rt_remain%NOTFOUND;
                      
                       if ( l_rt_uom = l_req_uom ) then
                           tmp_con_quantity := tmp_rt_quantity;
                       else
                           tmp_con_quantity := 0;
                           po_uom_s.uom_convert(tmp_rt_quantity, l_rt_uom, l_item_id, l_req_uom, tmp_con_quantity);
                       end if;
                      
                       l_quantity_received := l_quantity_received + tmp_con_quantity;
                   END LOOP;
                  
                   close rt_remain;
                  
                   UPDATE po_requisition_lines_all
                   SET quantity_received = l_quantity_received
                   WHERE requisition_line_id = l_requisition_line_id;
                  
                   dbms_output.put_line('Updating requisition_line_id ' || l_requisition_line_id || ' quantity_received to ' || l_quantity_received);

           
               EXCEPTION
                   WHEN OTHERS THEN
                       dbms_output.put_line('Error happens during updating requisition_line_id ' || l_requisition_line_id ); 
               END;
            END IF;
/*update requisition line */


            DELETE FROM rcv_transactions_interface
            WHERE shipment_line_id = l_shipment_line_id;
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_transactions_interface.');
           
            DELETE FROM rcv_shipment_headers
            WHERE shipment_header_id = l_shipment_header_id
            AND NOT EXISTS
                   (
                    SELECT 1
                    FROM rcv_shipment_lines rsl
                    WHERE rsl.shipment_header_id = l_shipment_header_id
                   );
            dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_shipment_headers.');


            l_prev_shipment_header_id := l_shipment_header_id;

          END LOOP;

          CLOSE c_shipment;

  dbms_output.put_line('Please Commit the transaction manually...');
EXCEPTION
  WHEN OTHERS THEN
    IF ( c_shipment%ISOPEN ) THEN
       CLOSE c_shipment;
    END IF;
    dbms_output.put_line('Exception Occurred for shipment'||l_shipment_num);
    dbms_output.put_line('Exception Occurred for shipment'||l_shipment_line_id);
    dbms_output.put_line('Exception Occurred'||SQLERRM);
    ROLLBACK;
    dbms_output.put_line('Rolled back the transaction...');
 END;
/

Identifying Duplicate Shipments SQL Query

 SELECT rsl.shipment_header_id,
        rsl.shipment_line_id,
        rsh.shipment_num
 FROM   rcv_shipment_lines rsl,
        rcv_shipment_headers rsh
 WHERE  rsl.shipment_header_id = rsh.shipment_header_id
 AND    rsh.creation_date  >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
 AND    rsh.creation_date  <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
 AND    rsl.mmt_transaction_id IS NOT null
 AND    rsh.receipt_source_code = 'INVENTORY'
 AND EXISTS
    (
     SELECT 1
     FROM mtl_parameters mp
     WHERE mp.organization_id = rsh.organization_id
    )
 AND NOT EXISTS
    (
     SELECT 1
     FROM mtl_material_transactions mmt
     WHERE mmt.transaction_id = rsl.mmt_transaction_id
     AND   mmt.transaction_source_type_id = 13
     AND   mmt.transaction_action_id in (3,21)
    )
 AND NOT EXISTS
    (
     SELECT 1
     FROM  mtl_material_transactions mmt,
           rcv_transactions rt
     WHERE mmt.rcv_transaction_id = rt.transaction_id
     AND   rt.shipment_line_id = rsl.shipment_line_id
     AND   rt.transaction_type = 'DELIVER'
    )
UNION
 SELECT rsl.shipment_header_id,
        rsl.shipment_line_id,
        rsh.shipment_num
 FROM   rcv_shipment_lines rsl,
        rcv_shipment_headers rsh
 WHERE  rsl.shipment_header_id = rsh.shipment_header_id
 AND    rsh.creation_date  >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
 AND    rsh.creation_date  <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
 AND    rsl.mmt_transaction_id IS NOT null
 AND    rsh.receipt_source_code = 'INTERNAL ORDER'
 AND EXISTS
    (
     SELECT 1
     FROM mtl_parameters mp
     WHERE mp.organization_id = rsh.organization_id
    )
 AND NOT EXISTS
    (
     SELECT 1
     FROM mtl_material_transactions mmt
     WHERE mmt.transaction_id = rsl.mmt_transaction_id
     AND   mmt.transaction_source_type_id = 8
     AND   mmt.transaction_action_id in (1,3,21,2)
    )
 AND NOT EXISTS
    (
     SELECT 1
     FROM  mtl_material_transactions mmt,
           rcv_transactions rt
     WHERE mmt.rcv_transaction_id = rt.transaction_id
     AND   rt.shipment_line_id = rsl.shipment_line_id
     AND   rt.transaction_type = 'DELIVER'
    )
 ORDER BY 1;

Oracle Pending Transaction Queries

Oracle Pending Transaction Queries

Unprocessed Material:

SELECT *

  FROM mtl_material_transactions_temp

 WHERE     organization_id = :p_org_id

       AND transaction_date < TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND NVL (transaction_status, 0) <> 2 -- 2 indicates a save-only status;

Uncosted Material/WSM:

SELECT  /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */     *

  FROM mtl_material_transactions MMT

 WHERE     organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND costed_flag IS NOT NULL;

Pending WIP Costing:

SELECT *

  FROM wip_cost_txn_interface

 WHERE organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending WSM Interface:

SELECT *

  FROM wsm_split_merge_txn_interface

 WHERE     organization_id = :p_org_id

       AND process_status <> wip_constants.completed

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending LCM Interface:

/* Support for Landed Cost Management: Pending landed cost adjustment transactions */

SELECT *

  FROM cst_lc_adj_interface

 WHERE organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending Receiving:

SELECT *

  FROM rcv_transactions_interface

 WHERE     to_organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND destination_type_code IN ('INVENTORY', 'SHOP FLOOR');

Pending Material:

SELECT *

  FROM mtl_transactions_interface

 WHERE     organization_id = 102

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND process_flag <> 9;

Pending Shop Floor Move:

SELECT *

  FROM wip_move_txn_interface

 WHERE organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr');

Incomplete Work Orders : If the maintenance is enabled in mtl parameters.eam_enabled_flag = ‘Y’

SELECT *

  FROM wip_discrete_jobs WDJ, wip_entities WE

 WHERE WDJ.organization_id = :p_org_id

       AND WDJ.scheduled_completion_date <=TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND WDJ.status_type = 3                                     -- Released

       AND WDJ.wip_entity_id = WE.wip_entity_id

       AND WDJ.organization_id = WE.organization_id

       AND WE.entity_type = 6                       -- Maintenance Work Order;