SQL Query to identify all personalizations on a Form

 SELECT 
       *       
 FROM
 (
SELECT   fpt.application_name, ff.form_name source_form_name,
         fft.user_form_name, fft.description form_description,
          ffcr.SEQUENCE personalize_rule_sequence,
         ffcr.description personalize_rule_description,
         --ffcr.last_update_date,
         DECODE (ffcr.rule_type,
                 'F', 'Form',
                 'A', 'Function'
                ) personalize_rule_level,
         ffcr.enabled personalize_rule_enabled,
         ffcr.last_update_date,
         ffcr.trigger_event personalize_rule_event, ffcr.trigger_object,
         ffcr.condition personalize_rule_condition,
         DECODE (ffcs.level_id,
                 10, 'Industry',
                 20, 'Site',
                 30, 'Responsibility',
                 40, 'User'
                ) context_level,
         DECODE (ffcs.level_id,
                 10, '',
                 20, '',
                 30, frt.responsibility_name,
                 40, fu.user_name
                ) context_level_value,
         ffca.SEQUENCE action_sequence,
         DECODE (ffca.action_type,
                 'P', 'Property',
                 'M', 'Message',
                 'B', 'Built in',
                 'S', 'Menu',
                 ''
                ) action_type,
         ffca.summary action_description, ffca.enabled action_enabled,
         DECODE (ffca.action_type,
                 'P', ffca.object_type,
                 NULL
                ) action_object_type,
         DECODE (ffca.action_type,
                 'P', ffca.target_object,
                 NULL
                ) action_target_object,
         DECODE (ffca.action_type,
                 'P', ffcpl.property_name,
                 NULL
                ) action_property_name,
         DECODE (ffca.action_type,
                 'P', DECODE (ffca.argument_type,
                              'B', DECODE (ffca.property_value,
                                           5, 'False',
                                           4, 'True'
                                          ),
                              ffca.property_value
                             ),
                 NULL
                ) action_property_value,
         DECODE (ffca.action_type,
                 'M', DECODE (ffca.MESSAGE_TYPE,
                              'S', 'Show',
                              'E', 'Error',
                              'W', 'Warning',
                              'H', 'Hint',
                              'D', 'Debug'
                             ),
                 NULL
                ) action_message_type,
         DECODE (ffca.action_type,
                 'M', ffca.MESSAGE_TEXT,
                 NULL
                ) action_message_text,
         DECODE (ffca.action_type,
                 'B', ffca.builtin_type,
                 NULL
                ) action_builtin_type,
         DECODE (ffca.action_type,
                 'B', ffca.builtin_arguments,
                 NULL
                ) action_builtin_arguments,
         DECODE (ffca.action_type,
                 'B', ffca.menu_argument_long,
                 NULL
                ) action_builtin_parameters,
         DECODE (ffca.action_type,
                 'B', ffca.menu_argument_short,
                 NULL
                ) action_builtin_code,
         DECODE (ffca.action_type,
                 'S', ffca.menu_entry,
                 NULL
                ) action_menu_entry,
         DECODE (ffca.action_type,
                 'S', ffca.menu_label,
                 NULL
                ) action_menu_label,
         DECODE (ffca.action_type,
                 'S', ffca.menu_seperator,
                 NULL
                ) action_menu_seperator,
         DECODE (ffca.action_type,
                 'S', ffca.menu_enabled_in,
                 NULL
                ) action_menu_blocks_enabled,
         DECODE (ffca.action_type,
                 'S', ffca.menu_argument_short,
                 NULL
                ) action_menu_icon_name
    FROM apps.fnd_application fp,
         apps.fnd_application_tl fpt,
         apps.fnd_form ff,
         apps.fnd_form_tl fft,
         apps.fnd_form_functions fff,
         apps.fnd_form_functions_tl ffft,
         apps.fnd_form_custom_rules ffcr,
         apps.fnd_form_custom_scopes ffcs,
         apps.fnd_responsibility_tl frt,
         apps.fnd_user fu,
         apps.fnd_form_custom_actions ffca,
         apps.fnd_form_custom_prop_list ffcpl
   WHERE fp.application_id = fpt.application_id
     AND fpt.LANGUAGE = 'US'
     AND fpt.application_id = ff.application_id
     AND ff.form_id = fft.form_id
     AND fft.LANGUAGE = 'US'
     AND ff.form_id = fff.form_id
     AND fff.function_id = ffft.function_id
     AND ffft.LANGUAGE = 'US'
     AND ff.form_name = ffcr.form_name
     AND ffcr.function_name = fff.function_name
     AND ffcr.ID = ffcs.rule_id
     AND ffcs.level_value = frt.responsibility_id(+)
     AND frt.LANGUAGE(+) = 'US'
     AND ffcs.level_value = fu.user_id(+)
     AND ffcr.ID = ffca.rule_id
     AND DECODE (ffca.action_type, 'P', ffca.property_name, 79) =
                                                             ffcpl.property_id
     AND DECODE (ffca.action_type, 'P', ffca.object_type, 'ITEM') =
                                                              ffcpl.field_type
     AND ffcr.trigger_event LIKE '%QUERY%'
     --and ff.form_name='OEXOEORD'
ORDER BY fft.application_id,
         ff.form_name,
         ffcr.SEQUENCE,
         ffcs.level_id,
         ffcs.level_value,
         ffca.SEQUENCE
)
WHERE 1 = 1
--AND action_type = 'Property'
--AND personalize_rule_enabled = 'Y'
--AND personalize_rule_event LIKE '%QUERY%'
--AND ACTION_TYPE = 'Builtin'
--AND personalize_rule_sequence IN ('55')
--AND personalize_rule_description LIKE '%CPQ%Punch%'
  AND personalize_rule_condition LIKE '%SUMMARY%';
--AND action_message_text LIKE '%You%allowed%'
--AND action_message_type = 'Show'
--AND personalize_rule_sequence IN ('55','54') 
ORDER BY last_update_date DESC;
--action_property_name LIKE '%DEFAULT_WHERE%';

Useful Links

 

How to know if my computer is 32-bit or 64-bit Windows?

https://support.lenovo.com/us/en/solutions/ht117173-how-to-know-if-my-computer-is-32-bit-or-64-bit-windows

How to format code in blogger?

https://www.youtube.com/watch?v=APNWCe56cQA

Custom.pll and Form Personalization precedence

1.What would be the precedence if both custom library and Forms Personalization have the same set of events?

Oracle Forms Libraries first invoke the Forms Personalization, and then call goes to the CUSTOM library for the same set of events. Forms personalization will override if the same set of events are passed as that of CUSTOM library.
The first call goes to Form personalization from Oracle Form libraries so the customization done using form personalization will be effective even if same set of events are available in custom library. So in this case custom library would not effective.

2.What is difference between Form personalization and custom library?

Form Personalization is an alternative way to do the same task as done in the custom library. Form Personalization has all the common used activities of CUSTOM library.
However form personalization has its own limitation, which does not allow achieving complex business logic's.
Limitations of Form Personalization:
Form Personalization allows you to respond to a Zoom event - it does not allow you to enable Zoom on a form. If you want to do that you must use CUSTOM library - custom.pll

Barcode Font formatting in Oracle XML Publisher / BI Publisher

Before going to the barcode implementation in XML Publisher, let's understand what is a Barcode?

A barcode consists of bars and spaces of varying width that can be read with an optical barcode scanner. When we go to a supermarket or a convenient store, to bill a product we need to scan the barcode of the product using a barcode scanner and the billing systems identify the product based on the barcode. What if there are no barcodes? e need to manually enter the product name or product number in the system to identify the exact product and its price and if you are buying many items, it takes very long time to enter each product number or name. Hence the barcodes and barcode readers came to solve this problem.

What are the components of a barcode?

As shown in the barcode components link, in general, there are 4 components to a barcode.

1. Quiet Zone - Blank Margin located on either sides of barcode
2. Start / Stop Characters - Represents the start and end of data
3. Data - The actual data for which the barcode is generated
4. Check Digit - Digit for checking if the encoded barcode data is correct

Now that we have seen the components of a barcode, the next thing is how do we convert the data into barcode format?

There are font encoders that reads the data and converts them into barcode format. Font encoder is the code (for simplicity, let's assume it as a java class) that reads the data and encodes it i.e. add start/stop characters and check digit. Usually the barcode font encoders i.e. java classes are provided by the barcode vendors.

Since barcodes are such an import functionality, XML Publisher also offers the ability to display data in barcode fonts in the report output.

Now let's see how to implement this barcode font formatting in XML Publisher.

The implementation requires 4 steps:

1. Define Barcode Font Mapping (In this post, I am showing the steps for IDAutomationC128M Barcode font family which is of code128a barcode type.
2. Place the encoding class file in the application server that can be used by XML Publisher at runtime to carry out the encoding at runtime
3. In the form field of a template 
1. register barcode encoding class - <?register-barcode-vendor:'java_class_name';'barcode_vendor_id'?>
2. encode data - <?format-barcode:data;'barcodetype';'barcode_vendor_id'?> 
4. Provide the font mapping defined in step1 under template configuration.

Define Barcode Font:

Navigation: XML Publisher Administration > Home > Administration

Click on Font Mappings > Create Font Mapping set

Place the encoding java class file in Application Server.

Tags to be added in the form field of template

<?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.BarcodeUtil';'XMLPBarVendor'?><?format-barcode:ORDER_NUMBER;'code128a';'XMLPBarVendor'?>

Once we complete the above steps, whenever XML publisher see the format-barcode tag, it will encode the data by using the encoding method (code128a in this case) in the class file that we mentioned in the register-barcode tag and generates the barcode format.

Note: As shown in the below screenshot, the language and territory of the template file should be same as that of the font mapping set. If they don't match, then XML Publisher will not encode the data into barcode format.

Mirror Items / Synchronized Items in Oracle Forms

In Oracle Forms, Mirror items or Synchronized items are achieved through the property called "Synchronize with Item property Description" and below are the property details

Synchronize with Item property Description

Specifies the name of the item from which the current item should derive its value. Setting this property synchronizes the values of the two items, so that they effectively mirror each other. When the end user or the application changes the value of either item, the value of the other item also changes.

Applies to all items except OLE containers Set Form Builder

Required/Optional: Optional 

Default: NULL

Usage Notes:

• In earlier releases, this property was called the Mirror Item property
• You can set Synchronize with Item for base table or control blocks. When Synchronize with Item is specified, the current item’s Base Table Item property is ignored, and the item derives its value from the mirror item specified, rather than from a column in the database.
• If you use the GET_ITEM_PROPERTY built-in to obtain a Base Table Item property, it will obtain the value from the mirror item specified.
• You can use mirror item to create more than one item in a block that display the same database column value. Synchronize with Item restrictions
• The maximum number of items in a form that can point to the same mirror item is 100.

UNIX

Oracle Support Notes

FNDLOAD Commands

Download Concurrent Program LDT file

====================================

FNDLOAD apps/<apps_pwd> O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct <filename>.ldt PROGRAM APPLICATION_SHORT_NAME="<conc_prog_appl_short_name" CONCURRENT_PROGRAM_NAME="<conc_prog_short_name>"

Download Lookup LDT file

========================

FNDLOAD apps/<apps_pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct <filename>.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME = "lookup_appl_short_name" LOOKUP_TYPE="<lookup_type"

Download LDT file to attach concurrent program to request group

===============================================================

FNDLOAD apps/<apps_pwd> O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct <filename>.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request_group_code" APPLICATION_SHORT_NAME="request_group_appl_short_name" REQUEST_GROUP_UNIT UNIT_NAME="conc_prog_short_name"

Download Value set LDT file

===========================

$FND_TOP/bin/FNDLOAD apps/<apps_pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <filename>.ldt VALUE_SET FLEX_VALUE_SET_NAME="<value_set_name>"

Download Request Set and its Links files

========================================

FNDLOAD apps/<apps_pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <filename>.ldt REQ_SET REQUEST_SET_NAME = "<request_set_code>"

FNDLOAD apps/<apps_pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <file_name>.ldt REQ_SET_LINKS REQUEST_SET_NAME = "request_set_code"

Download WebADI LDT file

========================

$FND_TOP/bin/FNDLOAD apps/<apps_pwd> 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct <file_name>.ldt BNE_INTEGRATORS INTEGRATOR_ASN="<integrator_appl_short_name" INTEGRATOR_CODE="integrator_short_code"

Download Alerts LDT file

========================

FNDLOAD apps/<apps_pwd 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct <file_name>.ldt ALR_ALERTS APPLICATION_SHORT_NAME="alert_appl_short_name" ALERT_NAME="<alert_name>"

SQL Queries

How to know the version of a Form in Oracle Apps?

 We can know the version of a Form in 2 methods.

Method1: 

Login to application and open the form for which we want to know the version.

Go to Help > About Oracle Applications as shown below

Scroll down to the Current Form section to get the current form version.

Method 2:

Login to UNIX server and go to forms top i.e. $AU_TOP/forms/US and execute the below command

adident Header formname.fmb

Ex: adident Header OEXOEORD.fmb

How to know/check 802.11 radio types supported by your laptop?

  Press Windows R to open the Run prompt.

  Enter cmd as shown below and hit Ok button.

  Now, enter the command netsh wlan show drivers and hit enter.

How to know current Oracle EBS login password?

 CREATE OR REPLACE FUNCTION XXFND_USER_PASS_DECRYPT(KEY   IN VARCHAR2,

                                                   VALUE IN VARCHAR2)

  RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

After creating the above function, we can use the below query to get the current password.

SELECT USR.USER_NAME,

       XXFND_USER_PASS_DECRYPT((SELECT (SELECT XXFND_USER_PASS_DECRYPT(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,                                                                     USERTABLE.ENCRYPTED_FOUNDATION_PASSWORD)

                                         FROM DUAL) AS APPS_PASSWORD

                                 FROM FND_USER USERTABLE

                                WHERE USERTABLE.USER_NAME =

                                      (SELECT SUBSTR(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,

                                                     1,

                                                     INSTR(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,

                                                           '/') - 1)

                                         FROM DUAL)),

                               USR.ENCRYPTED_USER_PASSWORD) PASSWORD

  FROM FND_USER USR

 WHERE USR.USER_NAME = 'USERNAME'; 

Role Based Access Control (RBAC)

 

Microsoft Excel

CNTL+SHIFT+ DOWN ARROW > Highlight all the cells in the column from current cell till the first empty cell

CNTL+SHIFT+ LEFT ARROW > Highlight all the cells in the row from current cell till the first empty cell

To use absolute referencing for any column in a formula, prefix the cell number of that column with a $ sign.

Concurrent Programs Queries

--concurrent program details 

SELECT CPTL.USER_CONCURRENT_PROGRAM_NAME,
       CPA.APPLICATION_SHORT_NAME CP_APPL,      
       CPTL.CREATION_DATE,
       CP.CONCURRENT_PROGRAM_NAME,
       CP.LAST_UPDATED_BY,
       CP.LAST_UPDATE_DATE,
       CP.CONCURRENT_PROGRAM_ID,
       CP.ENABLED_FLAG,
       CP.EXECUTION_METHOD_CODE,
       EC.LOOKUP_TYPE,
       EC.MEANING,
       E.EXECUTABLE_NAME,
       E.EXECUTION_FILE_NAME,
       CP.OUTPUT_FILE_TYPE,
       E.EXECUTION_FILE_PATH,
       EA.APPLICATION_SHORT_NAME EXE_APPL
  FROM APPS.FND_CONCURRENT_PROGRAMS_TL CPTL,
       APPS.FND_CONCURRENT_PROGRAMS    CP,
       APPS.FND_EXECUTABLES            E,
       APPS.FND_LOOKUP_VALUES          EC,
       APPS.FND_APPLICATION            CPA,
       APPS.FND_APPLICATION            EA
 WHERE EA.APPLICATION_ID = E.APPLICATION_ID
   AND CPA.APPLICATION_ID = CP.APPLICATION_ID
   AND CP.CONCURRENT_PROGRAM_ID = CPTL.CONCURRENT_PROGRAM_ID
   AND E.EXECUTABLE_ID = CP.EXECUTABLE_ID
   AND EC.LOOKUP_CODE = E.EXECUTION_METHOD_CODE
   AND EC.LANGUAGE = CPTL.LANGUAGE
   AND ('&&conc_prog_name' IS NULL OR UPPER(CPTL.USER_CONCURRENT_PROGRAM_NAME) LIKE
       UPPER('&conc_prog_name') || '%')
   AND ('&&conc_prog_short_name' IS NULL OR
       UPPER(CP.CONCURRENT_PROGRAM_NAME) LIKE
       '%' || ('&conc_prog_short_name') || '%')
   AND CPTL.LANGUAGE = 'US'
   AND EC.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
   AND ('&&exec_file_name' IS NULL OR
       UPPER(E.EXECUTION_FILE_NAME) LIKE
       '%' || UPPER('&exec_file_name') || '%')
 ORDER BY CPTL.USER_CONCURRENT_PROGRAM_NAME;

SELECT CPTL.USER_CONCURRENT_PROGRAM_NAME,
       CR.DESCRIPTION,
       CR.REQUEST_DATE,
       CR.REQUESTED_BY,
       (SELECT USER_NAME 
          FROM APPS.FND_USER 
         WHERE USER_ID = CR.REQUESTED_BY) REQUEST_BY_USER,
       (SELECT RESPONSIBILITY_NAME
          FROM FND_RESPONSIBILITY_TL
         WHERE RESPONSIBILITY_ID = CR.RESPONSIBILITY_ID
           AND LANGUAGE = 'US'
       ) RESPONSIBILITY,
       CR.RESUBMIT_INTERVAL,
       CR.PHASE_CODE,
       CR.STATUS_CODE,
       CR.HOLD_FLAG,
       CR.REQUESTED_START_DATE,
       CR.REQUEST_ID,
       CR.ARGUMENT_TEXT
  FROM FND_CONCURRENT_REQUESTS CR, 
       FND_CONCURRENT_PROGRAMS_TL CPTL
 WHERE CR.STATUS_CODE IN ('Q', 'I') AND 
       CR.REQUESTED_START_DATE > SYSDATE AND
       CPTL.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID AND
       CPTL.LANGUAGE = 'US'
 ORDER BY CPTL.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_START_DATE DESC;

Query to find valid categories in a Category Set:

SELECT mc.concatenated_segments,mc.description
  FROM apps.mtl_category_set_valid_cats mcsvc
      ,apps.mtl_category_sets mcs
      ,apps.mtl_categories_kfv mc
 WHERE mcsvc.category_set_id = mcs.category_set_id
   AND mc.category_id = mcsvc.category_id
   AND mcs.category_set_name = '<category_set_name>';

Query to find manufacturer part number for an item:

SELECT mm.manufacturer_name,
       mp.mfg_part_num,
       mp.description,
       msi.segment1 inv_item,
       msi.description item_desc,
       mp.approval_status
  FROM apps.mtl_system_items msi,
       apps.mtl_mfg_part_numbers mp,
       apps.mtl_manufacturers mm,
       apps.org_organization_definitions o
WHERE mm.manufacturer_id = mp.manufacturer_id
  AND mp.inventory_item_id = msi.inventory_item_id
  AND mp.organization_id   = msi.organization_id
  AND msi.segment1 = '<item_number>'
  AND msi.organization_id = o.organization_id
  AND o.organization_code = '<org_code>';

_TL, _VL, _ALL, _B, _F, _S tables in Oracle Apps

_ALL: These are Multi-org tables where we can see data for all operating units.
Ex: oe_order_headers_all, oe_order_lines_all, po_headers_all ap_supplier_sites_all

_TL:
These are translated tables which have the translated value for each installed language. These are tables corresponding to another table with the same name minus the TL. 
These tables provide multiple language support. 
For each item in the table without TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.
Ex: fnd_responsibility_tl, oe_transaction_types_tl

_F
These are date tracked tables which occur in HR and Payroll. In these tables there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap.
Ex: per_all_people_f, per_all_assignments_f

_B
These are the BASE tables. 
They are very important and the data is stored in the these table with all validations.
It is supposed that these table will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.
Ex: mtl_system_items_b

_V are views.

_VL
These are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG'). 

_S are sequences, used for generating new values for the primary key of a table.

Advanced Supply Chain Planning (ASCP)

MRP:ATP Database Link is the profile option in Source instance which determines the database link to be used to connect to Planning Server.

In a distributed installation where EBS Source instance is release 12.1, we must have the responsibility APS COLLECTIONS on the source instance to run collections and APS RELEASE on the source instance to release planned orders.

Item Validation Organization:

Navigation: Order Management > Setup > System Parameters > Values

•  Item Validation Organization system parameter is the key parameter which should be defined with out which items cannot be placed on Sales orders.
• While entering Sales Orders, Order Management validates if the item is orderable in the organization specified as Item Validation Organization. It does not validate against the organization / warehouse specified on Sales Order.
• Only items that have "Customer Orders Enabled" item attribute set to Yes in Item Validation Organization will appear in the item LOV on sales order line. 

Oracle Purchasing

Pay On Code:

Payment on Receipt also called Evaluated Receipt Settlement (ERS) enables the user to automatically create standard, unapproved invoices for payment of goods based on receipt transactions. 

Invoices are created using a combination of receipt and purchase order information, that eliminates duplicate manual data entry and ensures accurate and timely data processing. 

Prerequisite:

Supplier Site should be an active Pay Site and Purchasing Site.

Supplier Site should be setup as Pay on Receipt.

Supplier Invoice creation process:

 While creating receipt for a Purchase Order (PO), if the Pay on Code for PO supplier site is setup as "Receipt", Receiving Transaction Processor (RTP) will populate the INVOICE_STATUS_CODE as "PENDING" in RCV_TRANSACTIONS table otherwise it inserts NULL.

Then the concurrent program "Pay on Receipt Auto Invoice" picks up the receiving transactions with INVOICE_STATUS_CODE as PENDING and does the following actions: 

•      Update the INVOICE_STATUS_CODE to INVOICED in RCV_TRANSACTIONS table for RECEIVE transaction type
•      Insert records into AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE table
•      Launch "Payables Open Interface Import(PII)" program to create unapproved Payables Invoice

If PII error out during interface data validation, then the records stay in the interface with REJECTED status and the corresponding error message will be available in AP_INTERFACE_REJECTIONS table

        Irrespective of whether AP invoice is created or not, the INVOICE_STATUS_CODE will be updated to INVOICED in RCV_TRANSACTIONS table for RECEIVE transaction type.

Tables involved in Sourcing

1. PO_APPROVED_SUPPLIER_LIST

PO_APPROVED_SUPPLIER_LIST stores the relationship between an item or commodity; a supplier, distributor, or manufacturer; ship-to organizations; and approval/certification status.

2. PO_ASL_ATTRIBUTES

PO_ASL_ATTRIBUTES stores all information for the supplier/item/organization relationship defined in PO_APPROVED_SUPPLIER_LIST. This information is maintained separately to allow each organization to define its own attributes, even if using a Global ASL entry for the supplier/item relationship and approval status.

3. PO_ASL_DOCUMENTS

PO_ASL_DOCUMENTS stores sourcing references to supply agreements, blanket agreements, and catalog quotations associated with particular suppliers and items in PO_APPROVED_SUPPLIER_LIST.

4. PO_ASL_STATUSES

PO_ASL_STATUSES stores the user-defined approval/certification statuses for the ASLs.

5. PO_ASL_STATUS_RULES

PO_ASL_STATUS_RULES stores the business rules associated with each defined ASL status.

6. MRP_SOURCING_RULES

MRP_SOURCING_RULES stores sourcing rule names and descriptions. The Define Sourcing Rule form populates this table.

7. MRP_ASSIGNMENT_SETS

MRP_ASSIGNMENT_SETS stores information about Sourcing Assignment Sets. The Define Assignment Sets form populates this table.

8. MRP_SR_ASSIGNMENTS

MRP_SR_ASSIGNMENTS stores the assignment of sourcing rules or bills of distribution to items, organizations, category, or

at the global level. The Assign Sourcing Rules Form populates this table

.9. PO_ASL_ATTRIBUTES_VAL_V (view)

PO_ASL_ATTRIBUTES_VAL_V is a view used by the Create Documents workflow that is created from table PO_ASL_ATTRIBUTES and table PO_APPROVED_SUPPLIER_LIST

 

Oracle Inventory Queries

Query to find valid categories in a Category Set:

SELECT mc.concatenated_segments,mc.description
  FROM apps.mtl_category_set_valid_cats mcsvc
      ,apps.mtl_category_sets mcs
      ,apps.mtl_categories_kfv mc
 WHERE mcsvc.category_set_id = mcs.category_set_id
   AND mc.category_id = mcsvc.category_id
   AND mcs.category_set_name = '<category_set_name>';

Query to find manufacturer part number for an item:

SELECT mm.manufacturer_name,
       mp.mfg_part_num,
       mp.description,
       msi.segment1 inv_item,
       msi.description item_desc,
       mp.approval_status
  FROM apps.mtl_system_items msi,
       apps.mtl_mfg_part_numbers mp,
       apps.mtl_manufacturers mm,
       apps.org_organization_definitions o
WHERE mm.manufacturer_id = mp.manufacturer_id
  AND mp.inventory_item_id = msi.inventory_item_id
  AND mp.organization_id   = msi.organization_id
  AND msi.segment1 = '<item_number>'
  AND msi.organization_id = o.organization_id
  AND o.organization_code = '<org_code>';