, , , , , , , ,

Required and optional columns of Payables Open interface import of PO matched invoices (R12)

In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing PO Matched Invoices.

 Table:  AP_INVOICE_INTERFACE
  Required Columns

Column Name
Validation
INVOICE_ID                            
Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
INVOICE_NUM                           
Must be unique to the supplier
PO_NUMBER
An approved, not cancelled, not closed or final closed PO
VENDOR_ID or VENDOR_NUM or VENDOR_NAME
An active vendor. Validated against PO_VENDORS
VENDOR_SITE_ID or VENDOR_SITE_CODE
An active pay site. Validated against PO_VENDOR_SITES
INVOICE_AMOUNT                        
Positive amount for ‘STANDARD’ type, Negative amount for ‘CREDIT’ type
ORG_ID                                
Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
SOURCE                                
Must be in
SELECT lookup_code
  FROM ap_lookup_codes
 WHERE lookup_type = ‘SOURCE’;
    
 
Optional Columns
    Column Name
Validation
INVOICE_DATE
Defaulted to SYSDATE
INVOICE_TYPE_LOOKUP_CODE              
Defaulted to ‘STANDARD’. It can be ‘STANDARD’ or ‘CREDIT’
INVOICE_CURRENCY_CODE                 
Defaulted from PO_VENDOR_SITES.
INVOICE_CURRENCY_CODE
EXCHANGE_RATE_TYPE                    
Defaulted from AP_SYSTEM_PARAMETERS.
DEFAULT_EXCHANGE_RATE_TYPE
TERMS_ID or TERMS_NAME                
Defaulted from
 PO_VENDOR_SITES.TERMS_ID
DOC_CATEGORY_CODE                      
Only populated if using automatic voucher number
PAYMENT_METHOD_LOOKUP_CODE
Defaulted from PO_VENDOR_SITES
.PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
Defaulted from PO_VENDOR_SITES.
PAY_GROUP_LOOKUP_CODE
ACCTS_PAY_CODE_COMBINATION_ID
Defaulted from PO_VENDOR_SITES.
ACCTS_PAY_CODE_COMBINAITON_ID
GROUP_ID
Group identifier. Suggest to use it
STATUS
DO NOT POPULATE IT


Table:  AP_INVOICE_LINES_INTERFACE
 Required Columns 
Column Name
Validation
INVOICE_ID                            
Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID                       
Populated from AP_INVOICE_LINES_INTERFACE_S.
NEXTVAL
LINE_NUMBER        
A unique number to the invoice
TAX_CODE or TAX_CODE_ID
Validated against AP_TAX_CODES_ALL
LINE_TYPE_LOOKUP_CODE                 
‘ITEM’
AMOUNT                              
Should be QUANTITY_INVOICED * UNIT_PRICE
If MATCH_OPTION is ‘P’, then populate
RELEASE_NUM or PO_RELEASE_ID              
For Blanket Release only,
 validated against PO_RELEASES_ALL
PO_NUMBER or PO_HEADER_ID
Validated against PO_HEADER_ALL
PO_LINE_NUMBER or PO_LINE_ID
Validated against PO_LINES_ALL
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID    
Validated against PO_LINE_LOCATIONS_ALL
If MATCH_OPTION is ‘R’, then populate
RECEIPT_NUMBER                            
Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID
Validated against RCV_TRANSACTIONS
Optional Columns
Column Name
Validation
QUANTITY_INVOICED
Populated if different from PO shipment
UNIT_PRICE
Populated if different from PO shipment
MATCH_OPTION
‘P’ or ‘R’ or Defaulted from PO_VENDOR_SITES.MATCH_OPTION
ACCOUNTING_DATE
Defaulted from INVOICE_DATE or SYSDATE
FINAL_MATCH_FLAG
Populated ‘Y’ if it is final matching
INVENTORY_ITEM_ID
Validated against PO_LINES.INVENTORY_ITEM_ID
INVENTORY_DESCRIPTION
Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION
SHIP_TO_LOCATION_CODE
Populated if different from PO shipment
PRICE_CORRECTION_FLAG
Populated ‘Y’ if it is price correction
/by
, , , , , ,

Tables effected in the OM process

oe_order_headers_all,
oe_order_lines_all,
oe_order_holds_all,
oe_transaction_types_tl,
cs_customer_product_statuses,
cs_customer_products_all ,
wsh_delevery_details

For Deemed Products:-

MTL_UNIT_TRANSACTIONS,
MTL_MATERIAL_TRANSACTIONS

oe_order_headers_all,
oe_order_holds_all,
oe_order_sources,
oe_transaction_types_all,
oe_sales_credits,
oe_order_lines_all,
oe_order_lines_history,
oe_reservtns_iface_all,
wsh_delivey_details

/by
, , ,

Using Roles to Manage Database Access

When your databases has lots of tables, object privileges can become unwieldy and hard to manage. You can simplify the management of privileges with the use of a database object called a role. A role acts in two capacities in the database. First, the role can act as a focal point for grouping the privileges to execute certain tasks. Second, the role can act as a “virtual user” of a database, to which all the object privileges required to execute a certain job function can be granted, such as data entry, manager review, batch processing, and so on


Step 1:  Design and Crate roles is a process that can happen outside the database. A role mechanism can be used to provide authorization. A single person or a group of people can be granted a role or a group of roles. One role can be granted in turn to other roles. By defining different types of roles, administrators can manage access privileges much more easily. You simply sit down and think to yourself, how many different purposes will users be accessing this application for? Once this step is complete, you can create the different roles required to manage the privileges needed for each job function. Let’s say people using the EMP table have two different job roles: those who can query the table and those who can add records to the table. The next step is to create roles that correspond to each activity. This architecture of using roles as a middle layer for granting privileges greatly simplifies administration of user privileges. Let’s take a look at how to create the appropriate roles:
Create role role_name;
Step 2 :  Granting Privileges to Roles
The next step is to grant object privileges to roles. You can accomplish this task using the same command as you would use to grant privileges directly to users—the grant command. The following code block demonstrates how to grant privileges to both our roles:
Grant select on emp to rpt_writer;
Revoking privileges from roles works the same way as it does for users. System privileges are granted and revoked with roles in the exact same way they are with users as well.
Step 3: Granting Roles to Users
Once a role is created and privileges are granted to it, the role can then be granted to users. This step is accomplished with the grant command. Let’s see an example of how this is done:
Grant rpt_writer to turner;
If a role already granted to a user is later granted another privilege, that additional privilege is available to the user immediately. The same statement can be made for privileges revoked from roles already granted to users, too.
Step 4:  Revoking and Dropping Roles
Finally, a role can be revoked by the role’s owner or by a privileged administrative user using the revoke statement, much like revoking privileges:
revoke rpt_writer from turner;
Roles can be deleted from the database using the drop role statement. When a role is dropped, the associated privileges are revoked from the users granted the role. The following code block shows how to eliminate roles from Oracle:
drop role rpt_writer;
Step 5: Modifying Roles if necessary
Let’s say that after we create our roles, we realize that changing records in the EMP table is serious business. To create an added safeguard against someone making a change to the EMP table in error, the DATA_CHANGER role can be altered to require a password by using the alter role identified by statement. Anyone wanting to modify data in EMP with the privileges given via the DATA_CHANGER role must first supply a password. Code for altering the role is shown in the following example:
Alter role data_changer
Identified by highly#secure;
Step 6: Defining User Default Roles
Now user TURNER can execute all the privileges given to him via the RPT_WRITER role, and user FORD can do the same with the privileges given from DATA_CHANGER. Or can he? Recall that we specified that DATA_CHANGER requires a password in order for the grantee to utilize its privileges. Let’s make a little change to FORD’s user ID so that this status will take effect:
alter user ford default role none;
You can use the following keywords in the alter user default role command to define default roles for users: all, all except rolename, and none. Note that users usually cannot issue alter user default role themselves to change their default roles—only a privileged user such as the DBA can do it for them.
Step 7: Enabling the Current Role
FORD knows he is supposed to be able to accomplish this task because he has the DATA_CHANGER role. Then he remembers that this role has a password on it. FORD can use the set role command to enable the DATA_CHANGER role in the following way:
set role data_changer identified by highly#secure;
Now FORD can make the change he needs to make:
SQL> insert into scott.emp (empno, ename, job)
  2  values (1234, ‘SMITHERS’,’MANAGER’);
You must already have been granted the roles that you name in the SET ROLE statement. Also, you can disable all roles with the SET ROLE NONE statement.
/by
, , ,

System/Object Privileges

Privileges are the right to execute particular SQL statements. Two types of privileges exist in Oracle: object privileges and system privileges. Object privileges regulate access to database objects in Oracle, such as querying or changing data in tables and views, creating foreign key constraints and indexes on tables, executing PL/SQL programs, and a handful of other activities. System privileges govern every other type of activity in Oracle, such as connecting to the database, creating tables, creating sequences, creating views, and much, much more.
Privileges are given to users with the grant command, and they are taken away with the revoke command. The ability to grant privileges to other users in the database rests on users who can administer the privileges. The owner of a database object can administer object privileges related to that object, whereas the DBA administers system privileges

Object privileges (Grant Privileges on Tables)
You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

The syntax for granting privileges on a table is:
    grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
    grant select, insert, update, delete on suppliers to smithj;
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
    grant all on suppliers to smithj;
The keyword all can be use as a consolidated method for granting object privileges related to a table. Note that all in this context is not a privilege; it is merely a specification for all object privileges for a database object
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
    grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
    revoke privileges on object from user;
//  Grant this on this to this
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
    revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
    revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
    revoke all on suppliers from public;

System Privileges

Several categories of system privileges relate to each object. Those categories determine the scope of ability that the privilege grantee will have. The classes or categories of system privileges are listed in this section. Note that in the following subtopics, the privilege itself gives the ability to perform the action against your own database objects, and the any keyword refers to the ability to perform the action against any database object of that type in Oracle.
Database Access These privileges control who accesses the database, when he or she can access it, and what he or she can do regarding management of his or her own session. Privileges include create session, alter session, and restricted session. Users These privileges are used to manage users on the Oracle database. Typically, these privileges are reserved for DBAs or security administrators. Privileges include create user, become user, alter user, and drop user.
Tables You already know that tables store data in the Oracle database. These privileges govern which users can create and maintain tables. The privileges include create table, create any table, alter any table, backup any table, drop any table, lock any table, comment any table, select any table, insert any table, update any table, and delete any table. The create table or create any table privilege also enables you to drop the table. The create table privilege also bestows the ability to create indexes on the table and to run the analyze command on the table. To be able to truncate a table, you must have the drop any table privilege granted to you.
Indexes You already know that indexes are used to improve SQL statement performance on tables containing lots of row data. The privileges include create any index, alter any index, and drop any index. You should note that no create index system privilege exists. The create table privilege also enables you to alter and drop indexes that you own and that are associated with the table.
Synonyms A synonym is a database object that enables you to reference another object by a different name. A public synonym means that the synonym is available to every user in the database for the same purpose. The privileges include create synonym, create any synonym, drop any synonym, create public synonym, and drop public synonym. The create synonym privilege also enables you to alter and drop synonyms that you own.
Views You already know that a view is an object containing a SQL statement that behaves like a table in Oracle, except that it stores no data. The privileges include create view, create any view, and drop any view. The create view privilege also enables you to alter and drop views that you own.
Sequences You already know that a sequence is an object in Oracle that generates numbers according to rules you can define. Privileges include create sequence, create any sequence, alter any sequence, drop any sequence, and select any sequence. The create sequence privilege also enables you to drop sequences that you own.
Roles Roles are objects that can be used for simplified privilege management. You create a role, grant privileges to it, and then grant the role to users. Privileges include create role, drop any role, grant any role, and alter any role.

Transactions These privileges are for resolving in-doubt distributed transactions being processed on the Oracle database. Privileges include force transaction and force any transaction.
PL/SQL There are many different types of PL/SQL blocks in Oracle. These privileges enable you to create, run, and manage those different types of blocks. Privileges include create procedure, create any procedure, alter any procedure, drop any procedure, and execute any procedure. The create procedure privilege also enables you to alter and drop PL/SQL blocks that you own.
Triggers A trigger is a PL/SQL block in Oracle that executes when a specified DML activity occurs on the table to which the trigger is associated. Privileges include create trigger, create any trigger, alter any trigger, and drop any trigger. The create trigger privilege also enables you to alter and drop triggers that you own.
Examples
grant create session to turner;
// Grant this to this
revoke create session from turner;
// Revoke this from this
Open to the Public
Another aspect of privileges and access to the database involves a special user on the database. This user is called PUBLIC. If a system privilege or object privilege is granted to the PUBLIC user, then every user in the database has that privilege. Typically, it is not advised that the DBA should grant many privileges or roles to PUBLIC, because if a privilege or role ever needs to be revoked, then every stored package, procedure, or function will need to be recompiled. Let’s take a look:

GRANT select, update, insert ON emp TO public;
Tip    
Roles can be granted to the PUBLIC user as well. We’ll talk more about roles in the next discussion.
Granting Object Privileges All at Once
The keyword all can be use as a consolidated method for granting object privileges related to a table. Note that all in this context is not a privilege; it is merely a specification for all object privileges for a database object. The following code block shows how all is used:
GRANT ALL ON emp TO turner;

Giving Administrative Ability along with Privileges
When another user grants you a privilege, you then have the ability to perform whatever task the privilege enables you to do. However, you usually can’t grant the privilege to others, nor can you relinquish the privilege without help of the user who granted the privilege to you. If you want some additional power to administer the privilege granted to you, the user who gave you the privilege must also give you administrative control over that privilege. For example, let’s say KING now completely trusts TURNER to manage the creation of tables (a system privilege) and wants to give him access to the EMP table (an object privilege). Therefore, KING tells SCOTT to give TURNER administrative control over both these privileges, as follows:
GRANT CREATE TABLE TO turner WITH ADMIN OPTION;
GRANT SELECT, UPDATE ON turner TO SPANKY WITH GRANT OPTION;
Tip    
The GRANT OPTION is not valid when granting an object privilege to a role.
A system privilege or role can be granted with the ADMIN OPTION. A grantee with this option has several expanded capabilities. The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. However, a user cannot revoke a role from himself. The grantee can further grant the system privilege or role with the ADMIN OPTION. The grantee of a role can alter or drop the role.
Finally, if a role is granted using the with admin option clause, the grantee can alter the role or even remove it. You’ll learn more about roles in the next discussion.
You can grant INSERT, UPDATE, or REFERENCES privileges on individual columns in a table.

Cascading Effects

No cascading effects of revoking system privileges from users occur.
when an object privilege is revoked from a grantor of that privilege, all grantees receiving the privilege from the grantor also lose the privilege. However, in cases where the object privilege involves update, insert, or delete, if subsequent grantees have made changes to data using the privilege, the rows already changed don’t get magically transformed back the way they were before

/by
, , ,

User Access Control

In this chapter, you will learn about and demonstrate knowledge in the following areas of user access and privileges in the Oracle database:

  • Creating users
  • Granting and revoking object privileges
  • Using roles to manage database access

The basic Oracle database security model consists of two parts. The first part consists of password authentication for all users of the Oracle database. Password authentication is available either directly from the Oracle server or from the operating system supporting the Oracle database. When Oracle’s own authentication system is used, password information is stored in Oracle in an encrypted format. The second part of the Oracle security model consists of controlling which database objects a user may access, the level of access a user may have to these objects, and whether a user has the authority to place new objects into the Oracle database. At a high level, these controls are referred to as privileges. We’ll talk about privileges and database access later in this section.
Create Users
The most basic version of the command for creating users defines only the user we want to create, along with a password, as seen in the following example:
Create User USERNAME identified by PASSWORD;
create user turner identified by ike;
Tip :
The user does not have privileges at this point. The DBA can then grant privileges to the user. The privileges determine the actions that the user can do with the objects in the database. Also, usernames can be up to 30 characters in length and can contain alphanumeric characters as well as the $, #, and _ characters.


/by