BASIC SQL STATEMENT

BASIC SQL

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database





Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns





SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.
SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)


SQL DEFAULT Constraint on ALTER TABLE

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'



The COUNT() function

SELECT COUNT(*) FROM TABLENAME IT RETURNS THE NUMBER OF ROWS OF THE TABLE

SELECT DISTINCT COUNT(*) FROM TABLENAME RETURNS THE NUMBER OF DISTINCT ROWS IN THE TABLE


THE MAX FUNCTION

IT RETURNS THE MAX VALUE OF THE GIVEN COLUMN.
EG
SELECT MAX(SALARY) FROM EMP;
RETURNS THE MAXIMUM VALUE OF THE SALARY COLUMN

THE MIN FUNCTION

IT RETURNS THE MIN VALUE OF THE GIVEN COLUMN
EG
SELECT MIN(SALARY) FROM EMP;
RETURNS THE MINIMUM VALUE OF THE SALARY COLUMN


THE SUM FUNCTION

IT RETURNS THE SUM OF THE TOTAL DATA PRESENT IN THE GIVEN COLUMN IN A TABLE

EG
SELECT SUM(SALARY) FROM EMP;
IT SUMS UP THE TOTAL SALARY AND RETURNS THE SUMMATION OF SALARY.



The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

use ful links

http://oracleappss.blogspot.com

creation of Purchase order in oracle payable R12

THese are the minimum columns required to creat purchase order in R12.
The three interface tables has to be populated in order to create purchase order in R12.
The tables are as follows
Po_HEaders_Interface
Po_lines_Interface
PO_DISTRIBUTIONS_INTERFACE
The insert into query along with minimum column required are given as follows.

INSERT INTO PO_HEADERS_INTERFACE (
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,DOCUMENT_TYPE_CODE
,VENDOR_ID
,VENDOR_SITE_ID
,AGENT_ID
,TERMS_ID
,ORG_ID
,CURRENCY_CODE
,SHIP_TO_LOCATION_ID
,BILL_TO_LOCATION_ID
,APPROVAL_STATUS
-- ,DOCUMENT_NUM
,COMMENTS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
)




INSERT INTO PO_LINES_INTERFACE (
INTERFACE_HEADER_ID
,INTERFACE_LINE_ID
,LINE_NUM
,ITEM_ID
,UOM_CODE --UNIT_OF_MEASURE
,QUANTITY
,UNIT_PRICE
,PROMISED_DATE
,NEED_BY_DATE
,TERMS_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,SHIP_TO_ORGANIZATION_ID
,SHIP_TO_LOCATION_ID
)


INSERT INTO PO_DISTRIBUTIONS_INTERFACE(
INTERFACE_HEADER_ID
,INTERFACE_LINE_ID
,INTERFACE_DISTRIBUTION_ID
,QUANTITY_ORDERED
,CHARGE_ACCOUNT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
)


go to payables and import the po information using po import program.

cheers!!

Inventory item Locator creation in oracle inventory R12

The following API Creates Inventory Locator For an item.
Please put the appropriate values for item required.

APPS.INV_LOC_WMS_PUB.CREATE_LOCATOR (
x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_inventory_location_id => v_inventory_location_id
,x_locator_exists => v_locator_exists
,p_organization_id => v_organization_id
,p_organization_code => NULL
,p_concatenated_segments => rec.locator_segment1||'.'||rec.locator_segment2||'.'||rec.locator_segment3
,p_description => 'desc'
,p_inventory_location_type => 1
,p_picking_order => NULL
,p_location_maximum_units => NULL
,p_SUBINVENTORY_CODE => V_SEC_INV_NAME -- 'Stag'
,p_LOCATION_WEIGHT_UOM_CODE => NULL
,p_mAX_WEIGHT => NULL
,p_vOLUME_UOM_CODE => NULL
,p_mAX_CUBIC_AREA => NULL
,p_x_COORDINATE => NULL
,p_Y_COORDINATE => NULL
,p_Z_COORDINATE => NULL
,p_PHYSICAL_LOCATION_ID => NULL
,p_PICK_UOM_CODE => NULL
,p_DIMENSION_UOM_CODE => NULL
,p_LENGTH => NULL
,p_WIDTH => NULL
,p_HEIGHT => NULL
,p_STATUS_ID => V_STATUS_ID -- 1
,p_dropping_order => NULL
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_alias => NULL
) ;


cheers!!

Oracle Inventory Item Category Creation In R12

The following api creates the new category for an item.
These are the minimum parameter required for item category upload
Note that the segment provided is flexfield segments and has to be predefined.
The API further creates the new category with the segment provided.


L_CATEGORY_REC := NULL;
L_CATEGORY_REC.STRUCTURE_ID := 99
L_CATEGORY_REC.STRUCTURE_CODE := 'ITEM_CATEGORIES';
L_CATEGORY_REC.SUMMARY_FLAG := 'N';
L_CATEGORY_REC.ENABLED_FLAG := 'Y';
L_CATEGORY_REC.SEGMENT1 := I.SEGMENT1;
L_CATEGORY_REC.SEGMENT2 := I.SEGMENT2;
INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_TRUE,
X_RETURN_STATUS => O_RETURN_STATUS,
X_ERRORCODE => O_ERRORCODE,
X_MSG_COUNT => O_MSG_COUNT,
X_MSG_DATA => O_MSG_DATA,
P_CATEGORY_REC => L_CATEGORY_REC,
X_CATEGORY_ID => X_CATEGORY_ID --returns category id
);

Item category assign ment and onhand quantity for inventory item in R12

THese are the minimum column required for importing item category for an item.
The interface assigns the category for a given item.
The category and item should be predefined for this to work.
If category is not defined then you should create category and find the category id for the created category along with inventory item id.


INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
(
LAST_UPDATE_DATE --SYSADTE
,LAST_UPDATED_BY --USERNAME
,CREATION_DATE --SYSDATE
,CREATED_BY
,CATEGORY_SET_ID -- category set id in MTL_CATEGORY_SETS_TL
,ORGANIZATION_ID
,INVENTORY_ITEM_ID--INVENTORY ITEM ID
,CATEGORY_ID--SEARCH FROM MTL_ITEM_CATEGORIES
,OLD_CATEGORY_ID--IN CASE OF UPDATE
,PROCESS_FLAG --sud be 1
,TRANSACTION_TYPEp--,'UPDATE'/'create'
,SET_PROCESS_ID --NOTE THIS COLUMN MUST BE ENTERED LIKE BATCH NUMBER
)




ON HAND QUANTITY



insert into mtl_transactions_interface
(
transaction_type_id SOURCE mtl_transaction_types
,transaction_uom
,transaction_date
,organization_id
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_mode SHOULD BE 3
process_flag SHOULD BE 1
,source_header_id
,source_line_id
,source_code
,lock_flag SHOULD BE2
,flow_schedule SHOULD BE Y
,scheduled_flag SHOULD BE 2
,transaction_header_id
,inventory_item_id
,transaction_interface_id SOURCE mtl_material_transactions_s.nextval
,subinventory_code
,distribution_account_id
,transaction_reference SHOULD BE 'Migration'
)

cheers!!

Inventory item creation in R12

The following insert statement inserts the data into the item import interface table which further
creates inventory item.
Please fill in the values appropriately.

INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
LAST_UPDATE_DATE --SYSADTE
,LAST_UPDATED_BY --USERNAME
,CREATION_DATE --SYSDATE
,CREATED_BY --USERNAME
,SEGMENT1 --item code
,DESCRIPTION
,PRIMARY_UOM_CODE
,INVENTORY_ITEM_STATUS_CODE
,ORGANIZATION_CODE
,TEMPLATE_ID
,ORGANIZATION_ID
,LONG_DESCRIPTION
,PROCESS_FLAG -- should be 1
,TRANSACTION_TYPE--TRANSACTION_TYPE MAY BE CREATE OR UPDATE

)
now import item with item import program in inventory resposibility.
cheers

Oracle Receivable receipt application on account in R12

The receipt should be previously created in order to apply receipt on account.Following is the code to apply receipt in oracle receivable.
This is the working minimum required parameters to be passed.Put the values as per requirement.

AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT (
P_API_VERSION => 1.0,
P_INIT_MSG_LIST =>FND_APL_G_FALSE,
P_COMMIT =>FND_APL_G_FALSE,
P_VALIDATION_LEVEL =>FND_APL_G_VALID_LEVEL_FULL,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_COUNT,
X_MSG_DATA => L_MSG_DATA,
P_CASH_RECEIPT_ID => L_RECEIPT_ID,
P_RECEIPT_NUMBER => NULL,
P_AMOUNT_APPLIED => L_AMOUNT,
P_APPLY_DATE => l_APPLY_DATE ,
P_APPLY_GL_DATE => l_APPLY_TO_GL_DATE ,
P_USSGL_TRANSACTION_CODE => NULL,
P_ATTRIBUTE_REC => AR_RECEIPT_API_PUB.ATTRIBUTE_REC_CONST,
P_GLOBAL_ATTRIBUTE_REC => AR_RECEIPT_API_PUB.GLOBAL_ATTRIBUTE_REC_CONST,
P_COMMENTS => L_COMMENTS,
P_APPLICATION_REF_NUM => NULL,
P_SECONDARY_APPLICATION_REF_ID => NULL,
P_CUSTOMER_REFERENCE => NULL,
P_CALLED_FROM => NULL,
P_CUSTOMER_REASON => NULL,
P_SECONDARY_APP_REF_TYPE => NULL,
P_SECONDARY_APP_REF_NUM => NULL,
P_ORG_ID => L_ORG_ID
);

The input variable starting from L_ is given by developer.

The receipt applied can be viewed from
receivable >receipts >receipts

Minimum column required for receipt creation (oracle receivable) r12

This is the minimum requirement for recipt creation.The code can be used for both identified recceipt and unidentified recipt.
For identified put customer id and customer site use id
and for unidentified receipt creation put null for these two column.

AR_RECEIPT_API_PUB.CREATE_CASH(
P_API_VERSION => 1.0
,P_INIT_MSG_LIST => FND_API.G_FALSE
,P_COMMIT => FND_API.G_FALSE
,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
,X_RETURN_STATUS => L_RETURN_STATUS
,X_MSG_COUNT => L_MSG_COUNT
,X_MSG_DATA => L_MSG_DATA
,P_USR_CURRENCY_CODE => NULL
,P_CURRENCY_CODE => 'USD'
,P_USR_EXCHANGE_RATE_TYPE => NULL
,P_EXCHANGE_RATE_TYPE => NULL
,P_EXCHANGE_RATE => NULL
,P_EXCHANGE_RATE_DATE => NULL
,P_AMOUNT => L_AMOUNT
,P_FACTOR_DISCOUNT_AMOUNT => NULL
,P_RECEIPT_NUMBER => L_RECEIPT_NUMBER
,P_RECEIPT_DATE => L_RECEIPT_DATE
,P_GL_DATE => L_GL_DATE
,P_MATURITY_DATE => NULL
,P_POSTMARK_DATE => NULL
,P_CUSTOMER_ID => l_customer_id-------null if unidentified
,P_CUSTOMER_NAME => NULL
,P_CUSTOMER_NUMBER => NULL
,P_CUSTOMER_BANK_ACCOUNT_ID => NULL
,P_CUSTOMER_BANK_ACCOUNT_NUM => NULL
,P_CUSTOMER_BANK_ACCOUNT_NAME => NULL
,P_PAYMENT_TRXN_EXTENSION_ID => NULL
,P_LOCATION => NULL
,P_CUSTOMER_SITE_USE_ID => l_customer_site_use_id----------null for unidenfied
,P_DEFAULT_SITE_USE =>'Y'
,P_CUSTOMER_RECEIPT_REFERENCE => NULL
,P_OVERRIDE_REMIT_ACCOUNT_FLAG => NULL
,P_REMITTANCE_BANK_ACCOUNT_ID => NULL
,P_REMITTANCE_BANK_ACCOUNT_NUM => NULL
,P_REMITTANCE_BANK_ACCOUNT_NAME => NULL
,P_DEPOSIT_DATE => NULL
,P_RECEIPT_METHOD_ID => NULL
,P_RECEIPT_METHOD_NAME => L_RECEIPT_METHOD_NAME
,P_DOC_SEQUENCE_VALUE => NULL
,P_USSGL_TRANSACTION_CODE => NULL
,P_ANTICIPATED_CLEARING_DATE => NULL
,P_CALLED_FROM => NULL
,P_COMMENTS => L_COMMENTS
,P_ISSUER_NAME => NULL
,P_ISSUE_DATE => NULL
,P_ISSUER_BANK_BRANCH_ID => NULL
,P_ORG_ID => l_ORG_ID
,P_INSTALLMENT => NULL
,P_CR_ID =>L_RECEIPT_ID
);

You can go to receivable>receipts>receipts in order to view the receipt created.
you can query the receipt by its receipt number.Here the inputcolumn starting from l_ are
the inputs given from staging table.


cheers!!

creation of fnd_user via api

The following API provided adds the user and its password in fnd_table.The following code is working version of procedure to create new user.
leave the quotation '' as it is unless u have to sepcify some values of your own.


p_session_id INTEGER := userenv('sessionid');

FND_USER_PKG.CREATEUSER( x_user_name => rec.customer_user_id
,x_owner => ''
,x_unencrypted_password => rec.customer_password
,x_session_number => p_session_id
,x_start_date => rec.customer_creation_date
,x_end_date => NULL
,x_last_logon_date => SYSDATE - 10
,x_description => NULL
,x_password_date => SYSDATE - 10
,x_password_accesses_left => NULL
,x_password_lifespan_accesses => NULL
,x_password_lifespan_days => NULL
,x_employee_id => NULL
,x_email_address => NULL
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => ''
);

after addition find the user name in fnd_user.