Sales Order Return Invoice No Query in Oracle apps

Sales Order Return Invoice No Query in Oracle apps

SELECToeh.order_number, oel.return_attribute1, rat.customer_trx_id,
rat.trx_number , inv.trx_number parent_inv_no, oel.line_id
FROM ra_customer_trx_all rat,

     oe_order_headers_all oe,
oe_order_lines_all oel ,

ra_customer_trx_all inv

WHERE rat.trx_number = '13901123'

AND rat.interface_header_attribute1 = oeh.order_number

AND oeh.header_id = oel.header_id

AND TO_NUMBER (oel.return_attribute1) = inv.customer_trx_id

Delete Item Classification Query in Oracle Apps

Excise Rezime Code

---********* To Delete Attributes ***********---

DELETE FROM jai_rgm_itm_tmpl_attrs att
      WHERE EXISTS (
               SELECT 1
                 FROM jai_rgm_itm_regns rgm
                WHERE rgm.rgm_item_regns_id = att.rgm_item_regns_id
                  AND rgm.regime_code = 'EXCISE'
                  AND EXISTS (
                         SELECT 1
                           FROM mtl_system_items_b msii
                          WHERE msii.inventory_item_id = rgm.inventory_item_id
                            AND msii.organization_id = :P_ORGN_ID))
                      
---******* Delete Item Classification for EXCISE Rezime Item ********
delete
FROM jai_rgm_itm_regns rgm
           WHERE  rgm.regime_code = 'EXCISE'
             AND EXISTS (
                    SELECT 1
                      FROM mtl_system_items_items_b msii
                     WHERE msii.inventory_item_id = rgm.inventory_item_id
                       AND msii.organization_id = :P_ORGN_ID)


VAT Rezime Code

---********* To Delete Attributes ***********---

DELETE FROM jai_rgm_itm_tmpl_attrs att
      WHERE EXISTS (
               SELECT 1
                 FROM jai_rgm_itm_regns rgm
                WHERE rgm.rgm_item_regns_id = att.rgm_item_regns_id
                  AND rgm.regime_code = 'VAT'
                  AND EXISTS (
                         SELECT 1
                           FROM mtl_system_items_items_b msii
                          WHERE msii.inventory_item_id = rgm.inventory_item_id
                            AND msii.organization_id = :P_ORGN_ID))
                      
---******* Delete Item Classification for VAT Rezime Item ********
delete
FROM jai_rgm_itm_regns rgm
           WHERE  rgm.regime_code = 'VAT'
             AND EXISTS (
                    SELECT 1
                      FROM mtl_system_items_items_b msii
                     WHERE msii.inventory_item_id = rgm.inventory_item_id
                       AND msii.organization_id = :P_ORGN_ID)

Import Item Standard API in Oracle Apps

Import Item in Oracle Apps


Mandatory Values in  mtl_system_items_interface table


Column : process_flag, set_process_id, transaction_type, organization_id,segment1, description,template_id


Insert script for Interface table



INSERT INTO mtl_system_items_interface
             (process_flag, set_process_id, transaction_type, organization_id,
            segment1, description, template_id
            )
     VALUES (1, 1, 'CREATE', 2,
             ' XXITEM ', ' XX Import Item ', 2
            );

Once the data has been loaded into interface table then run standard concurrent program Import Items from 

INV SUPER USER Responsibility to import data into base tables.

 

1] All Organizations:

• Yes: Run the interface for all organization codes in the item interface table.
• No: Run the interface only for the organization you are currently in. Item interface rows for organizations other than your current organization are ignored.

2] Validate Items:

• Yes: Validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they will not be processed into Oracle Inventory.
• No:  Do not validate items in the interface table.

3] Process Items:

• Yes: All qualifying items in the interface table are inserted into Oracle Inventory.
• No: Do not insert items into Oracle Inventory.

4] Delete Processed Rows:

• Yes: Delete successfully processed items from the item interface tables.
• No: Leave all rows in the item interface tables.

5] Process Set:

Enter a number for the set id for the set of rows you want to process. The program picks up the rows marked with that id in the SET_PROCESS_ID column. If you leave this field blank, all rows are picked up for processing regardless of the SET_PROCESS_ID column value.

Working with failed interface rows:

If a row fails validation, the Item Interface sets the PROCESS_FLAG to 3 (Assign/validation failed) and inserts a row in the interface errors table, MTL_INTERFACE_ERRORS. To identify the error message for the failed row, the program automatically populates the TRANSACTION_ID column in this table with the TRANSACTION_ID value from the corresponding item interface table.

The UNIQUE_ID column in MTL_INTERFACE_ERRORS is populated from the sequence MTL_SYSTEM_ITEMS_INTERFACE_S. Thus, for a given row, the sequence of errors can be determined by examining UNIQUE_ID for a given TRANSACTION_ID.

You should resolve errors in the sequence that they were found by the interface, that is, in increasing order of UNIQUE_ID for any TRANSACTION_ID.

Resubmitting an Errored Row:

During Item Interface processing, rows can error out either due to validation (indicated by PROCESS_FLAG = 3 in MTL_SYSTEM_ITEMS_INTERFACE and the corresponding error in MTL_INTERFACE_ERRORS) or due to an Oracle Error.

When an Oracle Error is encountered, the processing is stopped and everything is rolled back to the previous save point. This could be at PROCESS_FLAG = 1, 2, 3, or 4.

When you encounter rows errored out due to validations, you must first fix the row corresponding to the error with the appropriate value. Then reset PROCESS_FLAG = 1, INVENTORY_ITEM_ID = null, and TRANSACTION_ID = null. Then resubmit the row for reprocessing.

To check the errors in interface table.

Select

ORGANIZATION_ID,

UNIQUE_ID,

REQUEST_ID ,

TABLE_NAME ,

COLUMN_NAME,

ERROR_MESSAGE

CREATION_DATE,

MESSAGE_TYPE

from

MTL_INTERFACE_ERRORS where request_id= :p_req_id

order by CREATION_DATE;

 

Once the items has been successfully imported then process_flag value will be changed 7 in interface table.

You can also able to see the error in interface table MTL_INTERFACE_ERRORS

PROCESS_FLAG Values after running the Import Items concurrent program

1= Pending, 

2= Assign Complete,

3= Assign/Validation Failed, 

4= Validation succeeded; Import failed, 

5 = Import in Process,

7 = Import succeeded