Serial Number Tables for Data fix Usage

select msib.*
from mtl_system_items_b msib
where msib.segment1='908-000008-002'
and msib.organization_id=121;  --121 = Master Org_id
select * from MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER in ('FB00000','FB00001','FB00002');
AND INVENTORY_ITEM_ID IN (200906);
--and current_organization_id=124;

select * from MTL_UNIT_TRANSACTIONS
WHERE SERIAL_NUMBER in ('FB00000','FB00001','FB00002');
AND INVENTORY_ITEM_ID IN (200906);

select * from WSH_SERIAL_NUMBERS
--SET FM_SERIAL_NUMBER =  '452979', TO_SERIAL_NUMBER = '452979'
WHERE FM_SERIAL_NUMBER in ('FB00000','FB00001','FB00002');
AND DELIVERY_DETAIL_ID = 20637412;

select * from RCV_SERIAL_TRANSACTIONS
WHERE SERIAL_NUM in ('FB00000','FB00001','FB00002');
AND  SHIPMENT_LINE_ID = 11490696;

select * from RCV_SERIALS_SUPPLY
WHERE SERIAL_NUM in ('FB00000','FB00001','FB00002');
AND  SHIPMENT_LINE_ID = 11490696;
                   

Approval List could not generated. Please contact your System Administrator to review AME rules setup.

Approval List could not generated. Please contact your System Administrator to review AME rules setup.

Cause:

Additional Requisitions (Cancelled, Incomplete, System Saved) getting inserted while creating PR.

Solution:

Execute the script to reset the Active_Shopping_Cart_Flag to null.

UPDATE po_requisition_headers_all h  

SET active_shopping_cart_flag = null  

WHERE 

h.authorization_status = :Status and 

active_shopping_cart_flag = 'Y' 

AND h.preparer_id in (select employee_id from fnd_user u where u.user_name = UPPER( :user_name ) )  

AND h.org_id =:org_id 

AND h.segment1 =:PR Number

How to Create Category and Category Set in Oracle Inventory?

Categories are the method by which the items in inventory can be separated logically and functionally for planning, purchasing and other activities.You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs. This article will describe how to create categories and category set in oracle inventory.

Suppose we need a category called ‘INV_COLORS’. We can define multiple colors in this category and then assign this category to an item.

Example:

1. Item1 —- Black
2. Item2 —- Red
3. Item3 —- Green
4. Item4 —- Orange

1] First we need to create a value set to hold these colors.

Navigation > Setup: Flexfields: Validation: Sets

Validation type Select: Independent

2] Next we need to enter our values in the INVENTORY_COLOR valueset

RED, GREEN, BLUE, BLACK, and ORANGE

Navigation -> Setup: Flexfields: Validation: Values

Save and close the Screen.

3] Now we need to create a KFF Structure

Navigation Setup: Flexfields: Key: Segments

Create the structure name: In the “Code” field enter INV_COLORS

4] Click on the “Segments” button.

• Enter the “Number” field: 10
• Enter the Name field: Color
• Enter the “Window Prompt”: Color (This value will appear on the screen)
• Enter the “Column” field: Segment1 (you can choose any column)

Save and exit the form.

5] Check the Freeze flex field Definition, the following warning will appear.

Click OK.

6] The “Compile” button is now available to be selected. Click on the compile button.

Click Ok

Close the form.

7] Go to View -> Request and Verify that the new Category flexfield compiled successfully.

8] The new structure is ready for use. Now let’s create a category.

Navigation : Setup: Items: Categories: Category Codes

• Enter the structure name: INV_COLORES
• Enter the category: BLACK
• (Note the form does not provide an LOV for the categories. You will need to use edit symbol at the top of the page or “ e “ to bring up the lov)
• Enter the description.

9] Next we create our category set.

Navigation Setup: Items: Categories: Category Sets

• Fill in the category set Name: INV_COLORS_SET
• The description: Inventory color set
• The Flex Structure: INV_COLORS
• The Controlled: Org Level
• Default Category: BLACK

10] After creating the category set, we can assign it to any items.

There are few category APIs that will be discussed in upcoming posts. Till then GOOD BYE………!! Have a nice day!

Reference: How to create a Category Set and Assign Items to Categories [MOS ID 423551.1]
 http://myoraclecafe.com/2011/07/29/how-to-create-category-and-category-set-in-oracle-inventory/

Receiving PO Using Receiving Open Interface (ROI)

The Receiving Transaction Processor processes pending or unprocessed receiving transactions. We can receive the Purchase Order either using the Expected Receipt form or by putting the record into the Receiving Open Interface (ROI). And then if we will submit the receiving transactions processor so the PO will be received.

Records needs to be inserted into rcv_transactions_interface with processing_status_code and transaction_status_code as 'PENDING' and transaction_type of 'RECEIVE'. and also inserted into rcv_shipment_headers which creates the shipment header.

Interface Tables: -

• rcv_headers_interface
• rcv_transactions_interface
• mtl_transaction_lots_interface

Error Table: -

• po_interface_errors

Base Tables:

• rcv_shipment_headers
• rcv_shipment_lines
• rcv_transactions
• mtl_lot_numbers
• mtl_material_transactions
• rcv_lot_transactions

R12 - Sample Procedure to Receive PO by inserting records into ROI

DECLARE

x_user_id NUMBER;
x_resp_id NUMBER;
x_appl_id NUMBER;
x_po_header_id NUMBER;
x_vendor_id NUMBER;
x_segment1 VARCHAR2 (20);
x_org_id NUMBER;
x_line_num NUMBER;
l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);
v_count NUMBER;

BEGIN

DBMS_OUTPUT.put_line ('RCV Sample Insert Script Starts');
DBMS_OUTPUT.put_line ('**************************************');

SELECT po_header_id, vendor_id, segment1, org_id
INTO x_po_header_id, x_vendor_id, x_segment1, x_org_id
FROM po_headers_all
WHERE segment1 = '380087' -- Enter The Po Number which needs to be received
AND org_id = 308 -- Enter the org_id
AND approved_flag = 'Y'
AND nvl(cancel_flag, 'N') = 'N';

SELECT DISTINCT
u.user_id,
to_char(a.responsibility_id) responsibility_id,
b.application_id
INTO
x_user_id, x_resp_id, x_appl_id
from
apps.fnd_user_resp_groups_direct a,
apps.fnd_responsibility_vl b,
apps.fnd_user u,
apps.fnd_application fa
where
a.user_id = u.user_id
and a.responsibility_id = b.responsibility_id
and a.responsibility_application_id = b.application_id
and sysdate between a.start_date and nvl(a.end_date,sysdate+1)
and fa.application_id (+) = b.application_id
and upper(u.user_name) = 'A42485' -- Enter the User_name
and b.responsibility_name = 'Inventory'; -- Enter The Responsibility Name

DBMS_OUTPUT.put_line ('Inserting the Record into Rcv_headers_interface');
DBMS_OUTPUT.put_line ('*********************************************');

INSERT INTO rcv_headers_interface
(header_interface_id, GROUP_ID, processing_status_code,
receipt_source_code, transaction_type, last_update_date,
last_updated_by, last_update_login, creation_date, created_by,
vendor_id,expected_receipt_date, validation_flag)
SELECT rcv_headers_interface_s.NEXTVAL, rcv_interface_groups_s.NEXTVAL,
'PENDING', 'VENDOR', 'NEW', SYSDATE, x_user_id, 0,SYSDATE, x_user_id,
x_vendor_id, SYSDATE, 'Y'
FROM DUAL;

DECLARE

CURSOR po_line
IS
SELECT
pl.org_Id, pl.po_header_id, pl.item_id, pl.po_line_id, pl.line_num, pll.quantity,
pl.unit_meas_lookup_code, mp.organization_code,
pll.line_location_id, pll.closed_code, pll.quantity_received,
pll.cancel_flag, pll.shipment_num,
pda.destination_type_code,
pda.deliver_to_person_id,
pda.deliver_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM po_lines_all pl, po_line_locations_all pll,mtl_parameters mp, apps.po_distributions_all pda
WHERE pl.po_header_id = x_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id;

BEGIN

FOR rec_det IN po_line LOOP

IF rec_det.closed_code IN ('APPROVED', 'OPEN')
AND rec_det.quantity_received <>

THEN

DBMS_OUTPUT.put_line ('Inserting the Record into Rcv_Transactions_Interface');
DBMS_OUTPUT.put_line ('*********************************************');

INSERT INTO rcv_transactions_interface
(interface_transaction_id, GROUP_ID,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, transaction_type,
transaction_date, processing_status_code,
processing_mode_code, transaction_status_code,
po_header_id, po_line_id, item_id, quantity, unit_of_measure,
po_line_location_id, auto_transact_code,
receipt_source_code, to_organization_code,
source_document_code, document_num,
destination_type_code,deliver_to_person_id,
deliver_to_location_id,subinventory,
header_interface_id, validation_flag)
SELECT rcv_transactions_interface_s.NEXTVAL,
rcv_interface_groups_s.CURRVAL, SYSDATE, x_user_id,
SYSDATE, x_user_id, 0, 'RECEIVE', SYSDATE, 'PENDING',
'BATCH', 'PENDING', rec_det.po_header_id,rec_det.po_line_id,
rec_det.item_id, rec_det.quantity,
rec_det.unit_meas_lookup_code,
rec_det.line_location_id, 'DELIVER', 'VENDOR',
rec_det.organization_code, 'PO', x_segment1,
rec_det.destination_type_code, rec_det.deliver_to_person_id,
rec_det.deliver_to_location_id, rec_det.destination_subinventory,
rcv_headers_interface_s.CURRVAL, 'Y'
FROM DUAL;

DBMS_OUTPUT.put_line ('PO line:' rec_det.line_num ' Shipment: ' rec_det.shipment_num ' has been inserted into ROI.');

select count(*)
into v_count
from mtl_system_items
where inventory_item_id = rec_det.item_id
and lot_control_code = 2 -- 2 - full_control, 1 - no control
and organization_id = rec_det.destination_organization_id;

IF v_count > 0 then

DBMS_OUTPUT.put_line ('The Ordered Item is Lot Controlled');
DBMS_OUTPUT.put_line ('Generate the Lot Number for the Lot Controlled Item');

BEGIN

-- initialization required for R12
mo_global.set_policy_context ('S', rec_det.org_id);
mo_global.init ('INV');
-- Initialization for Organization_id
inv_globals.set_org_id (rec_det.destination_organization_id);
-- initialize environment
fnd_global.apps_initialize (user_id => x_user_id,
resp_id => x_resp_id,
resp_appl_id => x_appl_id);

DBMS_OUTPUT.put_line ('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
DBMS_OUTPUT.put_line ('*********************************************');

l_chr_lot_number :=
inv_lot_api_pub.auto_gen_lot
(p_org_id => rec_det.destination_organization_id,
p_inventory_item_id => rec_det.item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);

IF l_chr_return_status = 'S' THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

DBMS_OUTPUT.put_line ('Lot Number Created for the item is => ' l_chr_lot_number);

END;

DBMS_OUTPUT.put_line ('Inserting the Record into mtl_transaction_lots_interface ');
DBMS_OUTPUT.put_line ('*********************************************');

INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
lot_number,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id,
product_code,
product_transaction_id)
(select
mtl_material_transactions_s.nextval,--transaction_interface_id
sysdate, --last_update_date
x_user_id, --last_updated_by
sysdate, --creation_date
x_user_id, --created_by
-1, --last_update_login
l_chr_lot_number, --lot_number
rec_det.quantity, --transaction_quantity
rec_det.quantity, --primary_quantity
NULL, --serial_transaction_temp_id
'RCV', --product_code
rcv_transactions_interface_s.currval --product_transaction_id
from dual);

ELSE

DBMS_OUTPUT.put_line ('The Ordered Item is Not Lot Controlled');
DBMS_OUTPUT.put_line ('********************************************');

END IF;

ELSE
DBMS_OUTPUT.put_line ( 'PO line ' rec_det.line_num'-' rec_det.shipment_num ' is either closed, cancelled, received.');
DBMS_OUTPUT.put_line ('*********************************************');

END IF;

END LOOP;

DBMS_OUTPUT.put_line ('RCV Sample Insert Script Ends');
DBMS_OUTPUT.put_line ('*****************************************');

END;

COMMIT;

END;

-- Cross Check the Records in the Interface Table

select * from apps.rcv_headers_interface
where created_by = 2083
and group_id = ***

select *
from apps.rcv_transactions_interface
where created_by = 2083
and group_id = ***

select * from apps.mtl_transaction_lots_interface
where created_by = 2083
and lot_number = ***
and product_transaction_id in
(select interface_transaction_id from apps.rcv_transactions_interface
where created_by = 2083 and group_id = ***)

-- Check for the Error

select * from po_interface_errors
where batch_id = ***

-- Reprocessing the records from the interface if the same errored out there.

UPDATE rcv_headers_interface
SET processing_request_id = NULL,
validation_flag = 'Y',
processing_status_code = 'PENDING'
WHERE GROUP_ID = ***

UPDATE rcv_transactions_interface
SET request_id = NULL,
processing_request_id = NULL,
validation_flag = 'Y',
processing_status_code = 'PENDING',
transaction_status_code = 'PENDING',
processing_mode_code = 'BATCH'
WHERE interface_transaction_id = ***
AND batch_id = ***


-- Verification of the base tables Once the Receiving Transactions Processor is Completed

select * from apps.rcv_shipment_headers
where created_by = 2083

select * from apps.rcv_shipment_lines
where created_by = 2083
and po_header_id = 619
select * from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083

select * from apps.mtl_lot_numbers
where lot_number in ('A6631684', 'A6631685', 'A6631686')
select * from apps.rcv_lot_transactions
where lot_num in ('A6631684', 'A6631685', 'A6631686')
select * from apps.mtl_material_transactions
where created_by = 2083
and rcv_transaction_id in (select transaction_id from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083)

SELECT (SELECT segment1
FROM po_headers_all
WHERE po_header_id = pl.po_header_id
AND org_id = pl.org_id) po_number, pl.po_header_id,
pl.item_id, pl.po_line_id, pl.line_num, pll.shipment_num,
pll.quantity, pl.unit_meas_lookup_code, mp.organization_code, pll.line_location_id,
pll.closed_code, pll.quantity_received, pll.cancel_flag,
pll.shipment_num, pda.destination_type_code, pda.deliver_to_person_id,
pda.deliver_to_location_id, pda.destination_subinventory
FROM
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.mtl_parameters mp,
apps.po_distributions_all pda
WHERE 1 = 1
AND pl.po_header_id = 619
AND pl.org_id = 308
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id
order by 1, 5, 6

 References:
http://jyotioraapps.blogspot.com/2009/08/receiving-po-using-receiving-open.html
 

Po Receipt Interface

PO receipt interface is to interface the po receiving transactions.

Interface Tables 

RCV_HEADERS_INTERFACE

RCV_TRANSACTION_INTERFACE.

MTL_SERIAL_NUMBERS_INTERFACE.

MTL_TRANSACTIONS_LOT_INTERFACE.

Interface Errors tables.

PO_INTERFACE_ERRORS.

Base tables.

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

RCV_TRANSACTIONS

These are the steps to be followed .

1. Insert  header records  into RCV_HEADERS_INTERFACE

2. insert transaction records  information into RCV_TRANSACTION_INTERFACE.

3. Based on the control of the item 

if item is serial controlled insert serial information in to                              MTL_SERIAL_NUMBERS_INTERFACE table

If the item is lot controlled item then insert records into lot interface table  MTL_TRANSACTIONS_LOT_INTERFACE table.

4.Run Receiving Transaction Processor concurrent request.

5. For Receving errors run the report Receiving Interface Errors Report

Here is the sample for po receipt interface for  loctor controled on default and serial control on receipt 

begin

INSERT INTO rcv_headers_interface

            (header_interface_id,

             GROUP_ID, 

             processing_status_code,

             receipt_source_code,

             asn_type, 

             transaction_type,

             last_update_date,

             last_updated_by,

             last_update_login,

             creation_date, 

             created_by, 

             shipment_num,

             shipped_date,

             vendor_id, 

             employee_id,

             validation_flag,-- freight_amount,

             --freight_carrier_code, num_of_containers,

             ship_to_organization_code,

              expected_receipt_date)

            SELECT rcv_headers_interface_s.NEXTVAL, 

             rcv_interface_groups_s.NEXTVAL,

            'PENDING',

            'VENDOR', 

            'ASN',

            'NEW',-- 'CANCEL',

             SYSDATE,

             1003,

             1,

             SYSDATE,

             1003,

             'SHIP-1234',

              SYSDATE, 

              1, 

              245,

              'Y',

             'MAD',

              SYSDATE + 5

     FROM DUAL;

INSERT INTO rcv_transactions_interface

            (interface_transaction_id,

             header_interface_id,

             GROUP_ID,

             last_update_date,

             last_updated_by,

             creation_date,

             created_by,

             last_update_login,

             transaction_type,

             transaction_date,

             processing_status_code,

             processing_mode_code,

             transaction_status_code,

             quantity,

             unit_of_measure,

             auto_transact_code,

             receipt_source_code,

             source_document_code,

             document_num, 

             release_num,

             document_line_num,

             document_shipment_line_num,

             VENDOR_id            , 

             notice_unit_price, 

             validation_flag,  

             subinventory,

             to_organization_code,

             item_id ,

             LOCATOR_id,

             uom_code)

   SELECT rcv_transactions_interface_s.NEXTVAL,

          rcv_headers_interface_s.CURRVAL, 

          rcv_interface_groups_s.CURRVAL,

          SYSDATE,

           1003, 

           SYSDATE,

            1003,

             1, 

             'RECEIVE',     --'SHIP', --'06-JAN-1998',

          SYSDATE,

           'PENDING', 

           'BATCH',

           'PENDING',

            2,

             'EACH', 

            'DELIVER',                 --'SHIP',

         'VENDOR',

          'PO', 

          '39338', 

          '', 

          '1',

           '',      

           1,

           972,

            'Y', 

          '01',

           'MAD',

           8,

           1241,

           'EA'

          

     FROM DUAL;

     

       INSERT INTO mtl_serial_numbers_interface

                           (transaction_interface_id,

                            last_update_date, last_updated_by,

                            creation_date, created_by,

                            last_update_login, fm_serial_number,

                            to_serial_number, product_code,

                            product_transaction_id

                           )

                    VALUES (mtl_material_transactions_s.NEXTVAL,

                            sysdate,

                             1003,                          

                            sysdate,        1003,1,

                            365879989,

                            365879990, 

                            'RCV',

                           rcv_transactions_interface_s.CURRVAL

                           );

     

     

COMMIT ;

end;

References:

http://erpappsoracle.blogspot.com/2009/05/po-receipt-interface.html