1. Transferring material between subinventories.
2. Performing miscellaneous transactions (miscellaneous issues and receipts).
3. Transferring material between Organizations.
4. Tracking lots and serial numbers for an item (Assigning Lot Numbers, Assigning Serial Numbers).
5. Generating material shortage alerts and material shortage notifications.
6. Entering and maintaining movement statistics information.
7. Viewing Material Transactions.
8. Viewing material transaction accounting distributions.
9. Viewing Transaction Summaries for a range of dates.
10. Viewing Pending Transactions.
11. Viewing and Updating pending Transaction Interface Activity.
12. Purging Transaction History.
13. Viewing Serial Genealogy.
Transferring Material Between Subinventories:
1. You can transfer material within your current organization between subinventories, or between two locators within the same subinventory.
2. You can transfer from asset to expense subinventories, as well as from tracked to non–tracked subinventories. (Here Asset, Expense, Tracked and Non-Tracked are the types of subinventories)
3. If an item has a restricted list of subinventories (Item is sub inventory controlled.), you can only transfer material from and to subinventories in that list.
4. Oracle Inventory allows you to use user–defined transaction types when performing a subinventory transfer.
Steps for entering a subinventory transfer:
1. Navigate to the Subinventory Transfer window.
2. Enter the date and time of entry for the transaction.
3. Enter a transaction type for the subinventory transfer. This can either be a predefined system type or one you defined.
4. Optionally, enter the source of the transaction type ex: Inventory
5. Optionally, indicate if inventory information should be defaulted from the serial number.
6. Choose Transaction Lines in the Subinventory Transfer window. The Transaction Lines Detail folder window appears.
7. Enter an inventory item to transfer. If you choose to default inventory information from the serial number (step5), enter a serial number.
8. Optionally, enter the revision for the item. You must enter a value here if the item is under revision control.
9. Enter the subinventories from and to which to transfer material. Enter the same subinventory in the Sub and To Sub fields to transfer material between locators.
10. Optionally, enter the locators from and to which to transfer the item. You must enter a value here if you established locator control. You can enter a new value in the To Locator field only if you defined locator control as dynamic entry.
11. Optionally, enter a lot number for the item. If you want to enter multiple lot numbers, complete the remaining steps then choose the Lot/Serial button to display the Lot Entry window.
12. Enter a unit of measure. This can be the primary unit of measure (the default) or any valid alternate unit of measure. If you enter an alternate unit of measure, Oracle Inventory issues the quantity you specify in this unit of measure. Oracle Inventory also converts the quantity to the primary unit of measure so that it can correctly update the on–hand quantity.
13. Enter the quantity of the inventory item to transfer, based on the unit of measure you specified.
14. Optionally, enter a reason code for the transaction. For example, you can use reason codes to allow you to mark exceptional charges to support a quality data collection and reporting system.
15. Optionally, enter up to 240 characters of free text that describes the transaction. (Reference field).
To view quantity available and quantity on hand values:
Look at the following fields in the form.
Available: Displays the quantity available to transfer, based on the unit of measure you specified. The available quantity is the quantity on hand less all reservations for the item. This amount could include the amount you have reserved if you enter a transaction source that has reservations against it. The available quantity includes reservations against current transaction source. The available quantity is specific to the revision level, lot number, From subinventory, and From locator you specify for the transfer.
Reservations will always be against a Transaction Source (Inventory, Purchase Order etc.)
On hand: Displays the current on–hand quantity for the item, based on the unit of measure you specified. The on–hand quantity is specific to the revision, lot number, From subinventory, and From locator you specify for the transfer. On–hand includes quantities for pending transactions in the MTL–MATERIAL–TRANSACTIONS table.
Performing Miscellaneous Transactions
With a miscellaneous transaction you can issue material to or receive material from general ledger accounts in your current organization.
Thursday, October 23, 2008
Thursday, October 16, 2008
Chr(13) appended to column values while performing sqlload
Usually when we do the sqlload the '|' appends to the end of the string. This '|' is knows as Chr(13) and can be removed once the sqlload is done using the following update statement:
Update [table_name]
set [column_name] = trim(chr(13) from [column_name])
Update [table_name]
set [column_name] = trim(chr(13) from [column_name])
Deadlocks and how to break them?
What is a deadlock?
Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work:
ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.
Deadlocks in Oracle result in this error:
ORA-00060: deadlock detected while waiting for resource
If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.
How to Break them?
SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id
SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'
ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'
Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work:
ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.
Deadlocks in Oracle result in this error:
ORA-00060: deadlock detected while waiting for resource
If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.
How to Break them?
SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id
SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'
ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'
Wednesday, October 15, 2008
All about MRP Sourcing Rules API and Single record insertion script
Standard Validation
Oracle Master Scheduling/MRP validates all required columns in the Sourcing Rule/Bill of Distribution API. For specific information on the data implied by these columns, see your Oracle Master Scheduling/MRP Technical Reference Manual for details.
If you do not want to update a particular column in:
❏ MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
Do not enter NULL for its corresponding interface parameter unless the default in the PL/SQL specification is NULL. Either use one of the missing parameter constants defined in the FND_API package (G_MISS_...), or do not pass any value at all.
For all flag parameters, pass in a Boolean constant defined in FND_API (G_TRUE or G_FALSE). Each time the API is called, it will check the allocation percent for each receiving organization that belongs to the sourcing rule or bill of distribution. If the total allocation percent is 100, the plannning_active attribute is set to a value of 1. Otherwise the attribute is set to 2.
Creating Sourcing Rule API Entries
When you create a new sourcing rule the following item level validations:
■ sourcing_rule_name: must be defined in the MRP_SOURCING _RULES table.
■ sourcing_rule_type: must be a either (1) Sourcing Rule or (2) Bill of
Distribution.If the sourcing rule does not already exist in the system - the Status attribute is set to 1.
When you create a new sourcing rule, the following record level validations occur:
■ organization_id: must be a valid organization defined in ORG_ORGANIZATION_DEFINITIONS.
■ The organization_id attribute is associated with a valid organization, unless it is null.
When you create a new sourcing rule, the following object level validations occur:
■ At least one receiving organization record is created.
■ At least one shipping organization record is created.
If validation is successful, a record is inserted into the MRP_SOURCING_RULES table.
Procedure Parameter Descriptions for MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
The following content describes all parameters used by the public API MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE procedure. Additional information on these parameters follows.
1. p_api_version_number
Used to compare the incoming API call's version number with the current version number. An error is returned if the version numbers are incompatible.
2. p_init_msg_list
Requests that the API initialize the message list on your behalf. If the x_msg_count is greater than 1, then the list of messages must be retrieved using the call FND_MSG_PUB.GET. The values are:
■ p_msg_index => I
■ p_encoded => F
■ p_data => 1_message
■ p_msg_index_out => 1_msg_index_out
where 1_message and 1_msg_index_out are local variables of types Varchar2(2000 and Number respectively.
Default Value: FND_API.G_FALSE
3. p_return_values
Requests that the API send back the values on your behalf.
Default Value: FND_API.G_FALSE
4. p_commit
Requests that the API update information for you after it completes its function.
Default Value: FND_API.G_FALSE
5. x_return_status
Requests that the API return the status of the data for you after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
6. x_msg_count
Indicates number of error messages API has encountered.
7. x_msg_data
Displays error message text. If the x_msg_count is equal to 1, then this contains the actual message.
8. p_sourcing_rule_rec
The sourcing rule or bill of distribution record referenced by the API.
Default Value: G_MISS_SOURCING_RULE_REC
9. p_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
Default Value: G_MISS_SOURCING_RULE_VAL_REC
10. p_receiving_org_tbl
The receiving organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_RECEIVING_ORG_TBL
11. p_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule.
Default Value: G_MISS_RECEIVING_ORG_VAL_TBL
12. p_shipping_org_tbl
The shipping organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_SHIPPING_ORG_TBL
13. p_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule.
Default Value: G_MISS_SHIPPING_ORG_VAL_TBL
14. x_sourcing_rule_rec
Result of the API data after it completes its function for the sourcing rule/bill of distribution record.
15. x_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
16. x_receiving_org_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule/bill of distribution record.
17. x_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the rule.
18. x_shipping_org_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule/bill of distribution record.
19. x_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the rule.
Record Parameter Descriptions
SOURCING_RULE_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SOURCING_RULE_REC_TYPE record with some additional information.
1. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. description
Text describing the sourcing rule record type.
Default Value: FND_API.G_MISS_CHAR
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. organization_id
The identification number for the organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
11. planning_active
Rule is active when the sum of the allocation percentages equals 100.
Default Value: FND_API.G_MISS_NUM
12. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
14. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_name
Valid name of rule defined in the MRP_SOURCING _RULES table.
Default Value: FND_API.G_MISS_CHAR
17. sourcing_rule_type
Valid types must be one of the following values:
■ (1) Sourcing Rule
■ (2) Bill of Distribution
Default Value: FND_API.G_MISS_NUM
18. status
If any validation fails, the API will return error status to the calling module. The Sourcing Rule API processes the rows and reports the following values for every record. If the sourcing rule does not already exist in the system - the Status attribute is set to 1. Processing status of the sourcing rule, valid values are:
■ (1)
■ (2)
Default Value: FND_API.G_MISS_NUM
19. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
20. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
21. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
RECEIVING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the RECEIVING_ORG_REC_TYPE
record with additional information:
1. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. disable_date
Date the receipt organization is no longer effective.
Default Value: FND_API.G_MISS_DATE
7. effective_date
Beginning date the receipt organization becomes effective.
Default Value: FND_API.G_MISS_DATE
8. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
9. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
10. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
11. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
14. receipt_organization_id
Identifier of the organization that serves as the destination for the sourcing rule or bill of distribution.
Default Value: FND_API.G_MISS_NUM
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
17. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
18. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
19. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
SHIPPING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SHIPPING_ORG_REC_TYPE record with additional informatioin.
1. sr_source_id
Primary key in the sourcing rule or bill of distribution table.
Default Value: FND_API.G_MISS_NUM
2. allocation_percent
Percentage allocated to each source organization/supplier site destination.
Default Value: FND_API.G_MISS_NUM
3. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
4. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
5. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
6. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
11. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
13. rank
Rank of the sources, valid values are non-zero integers.
Default Value: FND_API.G_MISS_NUM
14. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
15. secondary_inventory
Currently not used.
16. ship_method
Method used when transporting material between source and destination.
Default Value: FND_API.G_MISS_CHAR
17. source_organization_id
Identifier of the source organization.
Default Value: FND_API.G_MISS_NUM
18. source_type
Indicator of the type of source. Valid values are:
■ Make
■ Transfer
■ Buy
Default Value: FND_API.G_MISS_NUM
19. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
20. vendor_id
Identifier of the vendor suppling the materials.
Default Value: FND_API.G_MISS_NUM
21. vendor_site_id
Identifier where the vendor’s materials are located.
Default Value: FND_API.G_MISS_NUM
22. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
23. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
24. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
25. receiving_org_index
Foreign key to the receipt organization PL/SQL table.
Default Value: FND_API.G_MISS_NUM
Single Record insertion script for MRP Sourcing Rules
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_err_count NUMBER := 0;
l_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
l_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
l_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
l_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
l_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
l_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
o_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
o_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
o_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
o_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
o_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
o_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
BEGIN
fnd_message.clear;
l_sourcing_rule_rec := MRP_SOURCING_RULE_PUB.G_MISS_SOURCING_RULE_REC;
l_sourcing_rule_rec.sourcing_rule_name := '1234'; --SR Name
l_sourcing_rule_rec.Organization_Id:=207;
l_sourcing_rule_rec.planning_active := 1; -- Active?
l_sourcing_rule_rec.status := 1; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := 1; -- 1:Sourcing Rule
l_sourcing_rule_rec.operation := 'UPDATE';
l_sourcing_rule_rec.OPERATION=>'CREATE';
l_receiving_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_RECEIVING_ORG_TBL;
l_shipping_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_SHIPPING_ORG_TBL;
--l_receiving_org_tbl(1).Sr_Receipt_Id:=207;
l_receiving_org_tbl(1).effective_date := trunc(sysdate)+1;
l_receiving_org_tbl(1).disable_date := trunc(sysdate)+7;
l_receiving_org_tbl(1).receipt_organization_id:=207;
l_receiving_org_tbl(1).operation := 'CREATE'; -- Create or Update
--l_shipping_org_tbl(1).Sr_Source_Id:=228;
l_shipping_org_tbl(1).rank := 1;
l_shipping_org_tbl(1).allocation_percent := 100; -- Allocation 100
l_shipping_org_tbl(1).source_type := 3; -- BUY FROM
l_shipping_org_tbl(1).vendor_id := 21;
l_shipping_org_tbl(1).receiving_org_index := 1;
l_shipping_org_tbl(1).operation := 'CREATE';
dbms_output.put_line('before call');
dbms_output.put_line('Operation before call
'||l_sourcing_rule_rec.operation);
MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE(
p_api_version_number =>1.0
,p_init_msg_list =>fnd_api.g_true
,p_commit => fnd_api.g_true
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data
,p_sourcing_rule_rec => l_sourcing_rule_rec
,p_sourcing_rule_val_rec => l_sourcing_rule_val_rec
,p_receiving_org_tbl => l_receiving_org_tbl
,p_receiving_org_val_tbl => l_receiving_org_val_tbl
,p_shipping_org_tbl => l_shipping_org_tbl
,p_shipping_org_val_tbl => l_shipping_org_val_tbl
,x_sourcing_rule_rec => o_sourcing_rule_rec
,x_sourcing_rule_val_rec => o_sourcing_rule_val_rec
,x_receiving_org_tbl => o_receiving_org_tbl
,x_receiving_org_val_tbl => o_receiving_org_val_tbl
,x_shipping_org_tbl => o_shipping_org_tbl
,x_shipping_org_val_tbl => o_shipping_org_val_tbl
);
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Success!');
else
dbms_output.put_line('count:'||l_msg_count);
IF l_msg_count > 0 THEN
FOR l_index IN 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(
p_msg_index => l_index,
p_encoded => FND_API.G_FALSE);
dbms_output.put_line(substr(l_msg_data,1,250));
END LOOP;
dbms_output.put_line('MSG:'||o_sourcing_rule_rec.return_status);
END IF;
dbms_output.put_line('Failure!');
end if;
END;
Single Record insertion script for MRP Sourcing Rule Assignments
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_org_cnt NUMBER;
l_vendor_cnt NUMBER;
l_org_class VARCHAR2 (3);
l_org_num NUMBER;
l_line_num NUMBER := 0;
l_err_count NUMBER := 0;
p NUMBER;
l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
BEGIN
fnd_message.CLEAR;
-- l_assignment_set_rec.assignment_set_id := 3530;
-- l_assignment_set_rec.assignment_set_name := 'Test_src1_set';
-- l_assignment_set_rec.operation := 'CREATE';
l_assignment_tbl (1).assignment_set_id := 1;
l_assignment_tbl (1).assignment_type := 6;
l_assignment_tbl (1).operation := 'CREATE';
l_assignment_tbl (1).organization_id := 107;
l_assignment_tbl (1).inventory_item_id := 37002;
l_assignment_tbl (1).sourcing_rule_id := 1014;
l_assignment_tbl (1).sourcing_rule_type := 1;
mrp_src_assignment_pub.process_assignment
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_assignment_set_rec => l_assignment_set_rec,
p_assignment_set_val_rec => l_assignment_set_val_rec,
p_assignment_tbl => l_assignment_tbl,
p_assignment_val_tbl => l_assignment_val_tbl,
x_assignment_set_rec => o_assignment_set_rec,
x_assignment_set_val_rec => o_assignment_set_val_rec,
x_assignment_tbl => o_assignment_tbl,
x_assignment_val_tbl => o_assignment_val_tbl
);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Success!');
ELSE
DBMS_OUTPUT.put_line ('count:' || l_msg_count);
IF l_msg_count > 0
THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
DBMS_OUTPUT.put_line (SUBSTR (l_msg_data, 1, 250));
END LOOP;
DBMS_OUTPUT.put_line ('MSG:' || o_assignment_set_rec.return_status);
END IF;
DBMS_OUTPUT.put_line ('Failure!');
END IF;
END;
/
Oracle Master Scheduling/MRP validates all required columns in the Sourcing Rule/Bill of Distribution API. For specific information on the data implied by these columns, see your Oracle Master Scheduling/MRP Technical Reference Manual for details.
If you do not want to update a particular column in:
❏ MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
Do not enter NULL for its corresponding interface parameter unless the default in the PL/SQL specification is NULL. Either use one of the missing parameter constants defined in the FND_API package (G_MISS_...), or do not pass any value at all.
For all flag parameters, pass in a Boolean constant defined in FND_API (G_TRUE or G_FALSE). Each time the API is called, it will check the allocation percent for each receiving organization that belongs to the sourcing rule or bill of distribution. If the total allocation percent is 100, the plannning_active attribute is set to a value of 1. Otherwise the attribute is set to 2.
Creating Sourcing Rule API Entries
When you create a new sourcing rule the following item level validations:
■ sourcing_rule_name: must be defined in the MRP_SOURCING _RULES table.
■ sourcing_rule_type: must be a either (1) Sourcing Rule or (2) Bill of
Distribution.If the sourcing rule does not already exist in the system - the Status attribute is set to 1.
When you create a new sourcing rule, the following record level validations occur:
■ organization_id: must be a valid organization defined in ORG_ORGANIZATION_DEFINITIONS.
■ The organization_id attribute is associated with a valid organization, unless it is null.
When you create a new sourcing rule, the following object level validations occur:
■ At least one receiving organization record is created.
■ At least one shipping organization record is created.
If validation is successful, a record is inserted into the MRP_SOURCING_RULES table.
Procedure Parameter Descriptions for MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
The following content describes all parameters used by the public API MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE procedure. Additional information on these parameters follows.
1. p_api_version_number
Used to compare the incoming API call's version number with the current version number. An error is returned if the version numbers are incompatible.
2. p_init_msg_list
Requests that the API initialize the message list on your behalf. If the x_msg_count is greater than 1, then the list of messages must be retrieved using the call FND_MSG_PUB.GET. The values are:
■ p_msg_index => I
■ p_encoded => F
■ p_data => 1_message
■ p_msg_index_out => 1_msg_index_out
where 1_message and 1_msg_index_out are local variables of types Varchar2(2000 and Number respectively.
Default Value: FND_API.G_FALSE
3. p_return_values
Requests that the API send back the values on your behalf.
Default Value: FND_API.G_FALSE
4. p_commit
Requests that the API update information for you after it completes its function.
Default Value: FND_API.G_FALSE
5. x_return_status
Requests that the API return the status of the data for you after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
6. x_msg_count
Indicates number of error messages API has encountered.
7. x_msg_data
Displays error message text. If the x_msg_count is equal to 1, then this contains the actual message.
8. p_sourcing_rule_rec
The sourcing rule or bill of distribution record referenced by the API.
Default Value: G_MISS_SOURCING_RULE_REC
9. p_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
Default Value: G_MISS_SOURCING_RULE_VAL_REC
10. p_receiving_org_tbl
The receiving organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_RECEIVING_ORG_TBL
11. p_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule.
Default Value: G_MISS_RECEIVING_ORG_VAL_TBL
12. p_shipping_org_tbl
The shipping organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_SHIPPING_ORG_TBL
13. p_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule.
Default Value: G_MISS_SHIPPING_ORG_VAL_TBL
14. x_sourcing_rule_rec
Result of the API data after it completes its function for the sourcing rule/bill of distribution record.
15. x_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
16. x_receiving_org_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule/bill of distribution record.
17. x_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the rule.
18. x_shipping_org_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule/bill of distribution record.
19. x_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the rule.
Record Parameter Descriptions
SOURCING_RULE_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SOURCING_RULE_REC_TYPE record with some additional information.
1. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. description
Text describing the sourcing rule record type.
Default Value: FND_API.G_MISS_CHAR
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. organization_id
The identification number for the organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
11. planning_active
Rule is active when the sum of the allocation percentages equals 100.
Default Value: FND_API.G_MISS_NUM
12. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
14. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_name
Valid name of rule defined in the MRP_SOURCING _RULES table.
Default Value: FND_API.G_MISS_CHAR
17. sourcing_rule_type
Valid types must be one of the following values:
■ (1) Sourcing Rule
■ (2) Bill of Distribution
Default Value: FND_API.G_MISS_NUM
18. status
If any validation fails, the API will return error status to the calling module. The Sourcing Rule API processes the rows and reports the following values for every record. If the sourcing rule does not already exist in the system - the Status attribute is set to 1. Processing status of the sourcing rule, valid values are:
■ (1)
■ (2)
Default Value: FND_API.G_MISS_NUM
19. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
20. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
21. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
RECEIVING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the RECEIVING_ORG_REC_TYPE
record with additional information:
1. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
6. disable_date
Date the receipt organization is no longer effective.
Default Value: FND_API.G_MISS_DATE
7. effective_date
Beginning date the receipt organization becomes effective.
Default Value: FND_API.G_MISS_DATE
8. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
9. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
10. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
11. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
13. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
14. receipt_organization_id
Identifier of the organization that serves as the destination for the sourcing rule or bill of distribution.
Default Value: FND_API.G_MISS_NUM
15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
16. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM
17. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
18. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
19. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
SHIPPING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SHIPPING_ORG_REC_TYPE record with additional informatioin.
1. sr_source_id
Primary key in the sourcing rule or bill of distribution table.
Default Value: FND_API.G_MISS_NUM
2. allocation_percent
Percentage allocated to each source organization/supplier site destination.
Default Value: FND_API.G_MISS_NUM
3. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR
4. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR
5. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM
6. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE
7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM
8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE
9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM
10. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
11. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM
12. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE
13. rank
Rank of the sources, valid values are non-zero integers.
Default Value: FND_API.G_MISS_NUM
14. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM
15. secondary_inventory
Currently not used.
16. ship_method
Method used when transporting material between source and destination.
Default Value: FND_API.G_MISS_CHAR
17. source_organization_id
Identifier of the source organization.
Default Value: FND_API.G_MISS_NUM
18. source_type
Indicator of the type of source. Valid values are:
■ Make
■ Transfer
■ Buy
Default Value: FND_API.G_MISS_NUM
19. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM
20. vendor_id
Identifier of the vendor suppling the materials.
Default Value: FND_API.G_MISS_NUM
21. vendor_site_id
Identifier where the vendor’s materials are located.
Default Value: FND_API.G_MISS_NUM
22. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR
23. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR
24. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR
25. receiving_org_index
Foreign key to the receipt organization PL/SQL table.
Default Value: FND_API.G_MISS_NUM
Single Record insertion script for MRP Sourcing Rules
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_err_count NUMBER := 0;
l_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
l_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
l_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
l_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
l_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
l_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
o_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
o_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
o_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
o_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
o_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
o_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
BEGIN
fnd_message.clear;
l_sourcing_rule_rec := MRP_SOURCING_RULE_PUB.G_MISS_SOURCING_RULE_REC;
l_sourcing_rule_rec.sourcing_rule_name := '1234'; --SR Name
l_sourcing_rule_rec.Organization_Id:=207;
l_sourcing_rule_rec.planning_active := 1; -- Active?
l_sourcing_rule_rec.status := 1; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := 1; -- 1:Sourcing Rule
l_sourcing_rule_rec.operation := 'UPDATE';
l_sourcing_rule_rec.OPERATION=>'CREATE';
l_receiving_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_RECEIVING_ORG_TBL;
l_shipping_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_SHIPPING_ORG_TBL;
--l_receiving_org_tbl(1).Sr_Receipt_Id:=207;
l_receiving_org_tbl(1).effective_date := trunc(sysdate)+1;
l_receiving_org_tbl(1).disable_date := trunc(sysdate)+7;
l_receiving_org_tbl(1).receipt_organization_id:=207;
l_receiving_org_tbl(1).operation := 'CREATE'; -- Create or Update
--l_shipping_org_tbl(1).Sr_Source_Id:=228;
l_shipping_org_tbl(1).rank := 1;
l_shipping_org_tbl(1).allocation_percent := 100; -- Allocation 100
l_shipping_org_tbl(1).source_type := 3; -- BUY FROM
l_shipping_org_tbl(1).vendor_id := 21;
l_shipping_org_tbl(1).receiving_org_index := 1;
l_shipping_org_tbl(1).operation := 'CREATE';
dbms_output.put_line('before call');
dbms_output.put_line('Operation before call
'||l_sourcing_rule_rec.operation);
MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE(
p_api_version_number =>1.0
,p_init_msg_list =>fnd_api.g_true
,p_commit => fnd_api.g_true
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data
,p_sourcing_rule_rec => l_sourcing_rule_rec
,p_sourcing_rule_val_rec => l_sourcing_rule_val_rec
,p_receiving_org_tbl => l_receiving_org_tbl
,p_receiving_org_val_tbl => l_receiving_org_val_tbl
,p_shipping_org_tbl => l_shipping_org_tbl
,p_shipping_org_val_tbl => l_shipping_org_val_tbl
,x_sourcing_rule_rec => o_sourcing_rule_rec
,x_sourcing_rule_val_rec => o_sourcing_rule_val_rec
,x_receiving_org_tbl => o_receiving_org_tbl
,x_receiving_org_val_tbl => o_receiving_org_val_tbl
,x_shipping_org_tbl => o_shipping_org_tbl
,x_shipping_org_val_tbl => o_shipping_org_val_tbl
);
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Success!');
else
dbms_output.put_line('count:'||l_msg_count);
IF l_msg_count > 0 THEN
FOR l_index IN 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(
p_msg_index => l_index,
p_encoded => FND_API.G_FALSE);
dbms_output.put_line(substr(l_msg_data,1,250));
END LOOP;
dbms_output.put_line('MSG:'||o_sourcing_rule_rec.return_status);
END IF;
dbms_output.put_line('Failure!');
end if;
END;
Single Record insertion script for MRP Sourcing Rule Assignments
DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_org_cnt NUMBER;
l_vendor_cnt NUMBER;
l_org_class VARCHAR2 (3);
l_org_num NUMBER;
l_line_num NUMBER := 0;
l_err_count NUMBER := 0;
p NUMBER;
l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
BEGIN
fnd_message.CLEAR;
-- l_assignment_set_rec.assignment_set_id := 3530;
-- l_assignment_set_rec.assignment_set_name := 'Test_src1_set';
-- l_assignment_set_rec.operation := 'CREATE';
l_assignment_tbl (1).assignment_set_id := 1;
l_assignment_tbl (1).assignment_type := 6;
l_assignment_tbl (1).operation := 'CREATE';
l_assignment_tbl (1).organization_id := 107;
l_assignment_tbl (1).inventory_item_id := 37002;
l_assignment_tbl (1).sourcing_rule_id := 1014;
l_assignment_tbl (1).sourcing_rule_type := 1;
mrp_src_assignment_pub.process_assignment
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_assignment_set_rec => l_assignment_set_rec,
p_assignment_set_val_rec => l_assignment_set_val_rec,
p_assignment_tbl => l_assignment_tbl,
p_assignment_val_tbl => l_assignment_val_tbl,
x_assignment_set_rec => o_assignment_set_rec,
x_assignment_set_val_rec => o_assignment_set_val_rec,
x_assignment_tbl => o_assignment_tbl,
x_assignment_val_tbl => o_assignment_val_tbl
);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Success!');
ELSE
DBMS_OUTPUT.put_line ('count:' || l_msg_count);
IF l_msg_count > 0
THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
DBMS_OUTPUT.put_line (SUBSTR (l_msg_data, 1, 250));
END LOOP;
DBMS_OUTPUT.put_line ('MSG:' || o_assignment_set_rec.return_status);
END IF;
DBMS_OUTPUT.put_line ('Failure!');
END IF;
END;
/
Script to reset OM Records, while performing the conversions using interface approach
UNDEFINE NEW_SOURCE_ID
UNDEFINE OLD_SOURCE_ID
select s.order_source_id, substrb(s.name,1,20)
from oe_order_sources s;
select s.order_source_id, substrb(s.name,1,20) source, count(distinct h.header_id) headers
from oe_order_headers_all h,
oe_order_sources s
where h.order_source_id = s.order_source_id
group by s.order_source_id, s.name;
update oe_order_headers_all
set order_source_id = &&NEW_SOURCE_ID
where order_source_id = &&OLD_SOURCE_ID;
update oe_order_lines_all
set order_source_id = &&NEW_SOURCE_ID
where order_source_id = &&OLD_SOURCE_ID;
select s.order_source_id, substrb(s.name,1,20) source, count(distinct h.header_id) headers
from oe_order_headers_all h,
oe_order_sources s
where h.order_source_id = s.order_source_id
group by s.order_source_id, s.name;
delete oe_headers_iface_all
where order_source_id = &&OLD_SOURCE_ID;
delete oe_lines_iface_all
where order_source_id = &&OLD_SOURCE_ID;
delete oe_actions_iface_all
where order_source_id = &&OLD_SOURCE_ID;
UNDEFINE OLD_SOURCE_ID
select s.order_source_id, substrb(s.name,1,20)
from oe_order_sources s;
select s.order_source_id, substrb(s.name,1,20) source, count(distinct h.header_id) headers
from oe_order_headers_all h,
oe_order_sources s
where h.order_source_id = s.order_source_id
group by s.order_source_id, s.name;
update oe_order_headers_all
set order_source_id = &&NEW_SOURCE_ID
where order_source_id = &&OLD_SOURCE_ID;
update oe_order_lines_all
set order_source_id = &&NEW_SOURCE_ID
where order_source_id = &&OLD_SOURCE_ID;
select s.order_source_id, substrb(s.name,1,20) source, count(distinct h.header_id) headers
from oe_order_headers_all h,
oe_order_sources s
where h.order_source_id = s.order_source_id
group by s.order_source_id, s.name;
delete oe_headers_iface_all
where order_source_id = &&OLD_SOURCE_ID;
delete oe_lines_iface_all
where order_source_id = &&OLD_SOURCE_ID;
delete oe_actions_iface_all
where order_source_id = &&OLD_SOURCE_ID;
Monday, October 13, 2008
Import errors while loading Item Costs
There is a good documentation for the import issues on the item costs in the metalink. Please refer to the doc id: 213583.1
Oracle Inventory, Units of Measure
First start with defining the Unit of Measure.
For example, one class might be "Volume". How does your business measure "Volume" ? For example, the concrete business measures their product in cubic yards. Utilities such as a water department measures their product in gallons. These are aspects of the class known as "Volume". Another class might be "Distance". How does your business measure "Distance" ?
For example, the concrete business measures distance in miles (number of miles dispatch makes a delivery to the customer). A water department may measure distance in feet or even inches (amount of pipe laid to each customer. These are all aspects of defining the Unit of Measure Classes, which is understanding the needs of the business. Most businesses need many difference classes set up, for measures such as distance, volume, time, weight, etc. Some may not be applicable.
The base unit of measure is normally the smallest possible unit of measure. If the concrete company rounds off their miles, regardless and never is concerned about 1/10th of a mile, then 'mile' would be their base unit for distance.
Second define your Units of Measure. A primary unit of measure must be selected as the stocking unit of measure for an item in the organization.
Third define your Unit of Measure Conversions. Oracle does not know that 24 cans of soda make up a case. This is an intra-class conversion - like items. Suppose a can is the smallest unit of measure in this situation. 24 case equal a case. 12 cases equal a box. 8 boxes equal a pallet. 32 pallets equal a truck load. These are the types measures that need the to be set up for intra-class conversions for all of your items. If all of your items are cans, then this needs to be set up once. If you have many different dissimiliar items, then intra-class conversions need to be set up for each different type of item.
Interclass conversions. Lets suppose that you measure your soda cans in ounces, 12 ounces to a can. But your business also ships globally where the measure is performed in milliliters. Therefore a interclass conversion needs to be set up that denotes that one fluid ounce equals 29.583 milliliters of product.
If you are in the concrete business, an you normally measure your product in cubic yards. However many of your customers need to know how many cubic yards of concrete are necessary to cover 500 square feet (with an industry standard of 4 inch thicknesss). This is an Intraclass conversion - you are converting cubic yards into square feet of product.
For example, one class might be "Volume". How does your business measure "Volume" ? For example, the concrete business measures their product in cubic yards. Utilities such as a water department measures their product in gallons. These are aspects of the class known as "Volume". Another class might be "Distance". How does your business measure "Distance" ?
For example, the concrete business measures distance in miles (number of miles dispatch makes a delivery to the customer). A water department may measure distance in feet or even inches (amount of pipe laid to each customer. These are all aspects of defining the Unit of Measure Classes, which is understanding the needs of the business. Most businesses need many difference classes set up, for measures such as distance, volume, time, weight, etc. Some may not be applicable.
The base unit of measure is normally the smallest possible unit of measure. If the concrete company rounds off their miles, regardless and never is concerned about 1/10th of a mile, then 'mile' would be their base unit for distance.
Second define your Units of Measure. A primary unit of measure must be selected as the stocking unit of measure for an item in the organization.
Third define your Unit of Measure Conversions. Oracle does not know that 24 cans of soda make up a case. This is an intra-class conversion - like items. Suppose a can is the smallest unit of measure in this situation. 24 case equal a case. 12 cases equal a box. 8 boxes equal a pallet. 32 pallets equal a truck load. These are the types measures that need the to be set up for intra-class conversions for all of your items. If all of your items are cans, then this needs to be set up once. If you have many different dissimiliar items, then intra-class conversions need to be set up for each different type of item.
Interclass conversions. Lets suppose that you measure your soda cans in ounces, 12 ounces to a can. But your business also ships globally where the measure is performed in milliliters. Therefore a interclass conversion needs to be set up that denotes that one fluid ounce equals 29.583 milliliters of product.
If you are in the concrete business, an you normally measure your product in cubic yards. However many of your customers need to know how many cubic yards of concrete are necessary to cover 500 square feet (with an industry standard of 4 inch thicknesss). This is an Intraclass conversion - you are converting cubic yards into square feet of product.
Oracle Support and Why upgrading to R12?
Oracle Support
Premier Support:
- Includes certification with new third party products/versions and Oracle products
Extended Support:
- Costs more than Premium Support
- Certification is with most existing instead of new third party products/versions and Oracle products
- Extended Support may not include certification with some new third party products/versions
- So if you find a new problem with an existing certified configuration, Oracle Support will help you.
- If you're still running Oracle 9i you're on Extended Support(which ends July 2010)
Sustaining Support:
- Does not include new updates, fixes, security alerts, data fixes and critical patch updates; new tax, legal and regulatory updates; new upgrade scripts; certification with new third-party products/versions; certification with new Oracle Products
- If you have a question and there's already an answer in Metalink, Oracle Support will point you to it
- If you're still running Release 11.0.3, you're on Sustaining Support(which end Jan 2009)
What's the risk in not upgrading to R12?
- The biggest risk - Security/Performance
- Critical Patch Updates(CPUs)
- CPUs address security vulnerabilities
- Oracle release a new CPU every quarter
- R12 Apps CPUs are cumulative
- Relase 12.0.4 includes the Jan 2008 CPU
- Oracle follows strict compilance with ATG_PF RUP policy(N-1)
* January 2008 CPU will only support ATG RUP 5 and RUP 6
* To stay current on security, you must stay relatively current on ATG RUPs, which means that you must stay relatively current on which releases of the applications you are running
- If Sarbanes-Oxley compilance is important to your company, can you afford to fall behind on security updates?
- If you know there's a Critical Patch Update, and you know what vulnerabilities it fixes, so do hackers
Premier Support:
- Includes certification with new third party products/versions and Oracle products
Extended Support:
- Costs more than Premium Support
- Certification is with most existing instead of new third party products/versions and Oracle products
- Extended Support may not include certification with some new third party products/versions
- So if you find a new problem with an existing certified configuration, Oracle Support will help you.
- If you're still running Oracle 9i you're on Extended Support(which ends July 2010)
Sustaining Support:
- Does not include new updates, fixes, security alerts, data fixes and critical patch updates; new tax, legal and regulatory updates; new upgrade scripts; certification with new third-party products/versions; certification with new Oracle Products
- If you have a question and there's already an answer in Metalink, Oracle Support will point you to it
- If you're still running Release 11.0.3, you're on Sustaining Support(which end Jan 2009)
What's the risk in not upgrading to R12?
- The biggest risk - Security/Performance
- Critical Patch Updates(CPUs)
- CPUs address security vulnerabilities
- Oracle release a new CPU every quarter
- R12 Apps CPUs are cumulative
- Relase 12.0.4 includes the Jan 2008 CPU
- Oracle follows strict compilance with ATG_PF RUP policy(N-1)
* January 2008 CPU will only support ATG RUP 5 and RUP 6
* To stay current on security, you must stay relatively current on ATG RUPs, which means that you must stay relatively current on which releases of the applications you are running
- If Sarbanes-Oxley compilance is important to your company, can you afford to fall behind on security updates?
- If you know there's a Critical Patch Update, and you know what vulnerabilities it fixes, so do hackers
Wednesday, October 8, 2008
SQL*Loader
This section will provide a basic understanding of SQL*Loader. Most of the information contained in this section is DIRECTLY extracted from 'ORACLE Server Utilities Users Guide' and all credit should be given to ORACLE. If you require more detailed information than provided in this section, consult the 'ORACLE Server Utilities Users Guide'.
SQL*Loader is a product for moving data from external files into tables in an ORACLE database. SQL*Loader loads data in a variety of formats, performs filtering (selectively loading records based upon the data values), and loads multiple tables simultaneously. During execution SQL*Loader produces a detailed log file with statistics about the load, and may also produce a bad file (records rejected because of incorrect data) and a discard file (records that did not meet your selection criteria). You have control over several loading options.
You must provide two types of input to SQL*Loader to load data from external files into an ORACLE database: the data itself, and control information describing how to perform the load. You must provide a file called the control file as an input to SQL*Loader. The control file tells SQL*Loader how to interpret the data file. For example, it describes the following:
1. The names of the data files
2. The format of the data files
3. The character sets used in the data files
4. The datatypes of the fields in those files
5. How to identify the start and end of data fields
6. Which tables and columns to load
The control file's datatype specifications tell SQL*Loader how to interpret the fields in the data files. SQL*Loader uses this information when working with the fields, and uses it to describe the data that is being passed to ORACLE. ORACLE then converts the data into the datatype specified by the table definition. Some information is mandatory (such as where to find the data and how it corresponds to the database tables). However, many options are also available to describe and manipulate the file data. For example, the instructions can include directions on how to format or filter the data, or to generate unique ID numbers.
You may load data in various formats. It is usually read from one or more data files, but the data may also be placed in the control file after the control file information. Data records may be in fixed or variable format. In fixed format, the data is contained in records which all have the same (fixed) format. That is, the records have a fixed length, and the data fields in those records have fixed length, type, and position.
In variable format (sometimes called stream format), each record is only as long as necessary to contain the data. With character data, if the first item is shorter than the second one, the first record is shorter. Also, the type of data in each record may vary. One record may contain a character string, the next may contain seven integers, the third may contain three decimals and a float, and so on. Operating systems use a record terminator character (such as newline) to mark where variable records end.
Delimited data is of two types: terminated or enclosed. Terminated data is followed by a specified character such as a comma, as in the following example:
1,1,2,3,5,8,13
Enclosed data is preceded and followed by a specified character such as a quotation mark, as in the following example:
'BUNKY'
A final distinction concerns the difference between logical and physical records.
A record or line in a file (either of fixed length or terminated) is referred to as a physical record. Logical record, on the other hand, corresponds to a row in a database table. Sometimes the logical and physical records are equivalent; such is the case when only a few short columns are being loaded.
However, sometimes several physical records must be combined to make one logical record. The examples below will illustrate some of the features of the SQL*Loader.
Example 1. Loading Data into Multiple Tables
CONTROL FILE: The control file for this example.
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJNO listed
-- for each employee
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'
a. REPLACE
b. INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)
b. INTO TABLE proj
-- PROJ has two columns, both not null: EMPNO and PROJNO
c. WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
c. projno POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj
b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
---------------------------------------------------------------
NOTES:
(a) REPLACE indicates that if there is data in the tables to be loaded
(EMP and PROJ), that data should be deleted before new rows are loaded.
(b) Multiple INTO clauses are used to load two tables, EMP and PROJ. The
same set of records is processed three times using different combinations
of columns each time, to load table PROJ.
(c) WHEN is used to load only rows with non-blank project numbers. When
PROJNO is defined as columns 25..27, rows are inserted into PROJ only if
there is a value in those columns.
DATA FILE - Part of the data file follows.
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE5.CTL LOG=ULCASE5.LOG
Example 2 Loading a Delimited, Free-Format File
CONTROL FILE - The control file for this example.
-- Variable-length, delimited and enclosed data format
LOAD DATA
a. INFILE *
b. APPEND
INTO TABLE emp
c. FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
d. hiredate DATE "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':',
projno,
e. loadseq SEQUENCE(MAX,1))
f. BEGINDATA
7782, "CLARK", "Manager", 7839, 09-June-1981, 2572.50, 10:101
7839, "King", "President", , 17-January-1982, 920.00, 10:102
---------------------------------------------------------------
NOTES:
(a) INFILE * signifies the data is found at the end of the control file.
(b) APPEND indicates that data may be loaded even if the table already
contains rows; the table need not be empty.
(c) The default terminator for the data fields is a comma, and some fields
may be enclosed by a double quote.
(d) The data to be loaded into column HIREDATE appears in the format
DD-Month-YYYY.
(e) The SEQUENCE function is used to generate a unique value in the column
LOADSEQ. This function finds the current maximum value in column LOADSEQ
and adds the increment (1) to it to obtain the value for LOADSEQ for each
row inserted.
(f) BEGINDATA signifies the end of the control information and the
beginning of the data.
INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE3.CTL LOG=ULCASE3.LOG
Control File Syntax
The control file usually begins with the phase LOAD DATA, followed by several phrases that describe the data to be loaded. Only comments or the OPTIONS phrase can precede the LOAD DATA phase.
For a complete control file syntax diagram see Appendix C in this manual. Only a subset of the syntax will be explained below. For a complete explanation of the above syntax, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''. Comments Comments may appear anywhere in the command section of the file, but they should not appear in the data. Comments are preceded with a double dash, which may appear anywhere on a line. All text to the right of the double dash is ignored,
until the end of line.
The OPTIONS Clause
The OPTIONS clause is useful when you usually invoke a control file with the same set of options, or when the command line and all its arguments becomes very long. This clause allows you to specify runtime arguments in the control file rather than on the command line.
SKIP = n -- Number of logical records to skip (DEFAULT 0)
LOAD = n -- Number of logical records to load (DEFAULT all)
ERRORS = n -- Number of errors to allow (DEFAULT 50)
ROWS = n -- Number of rows in conventional path bind array (DEFAULT 64)
BINDSIZE = n -- Size of conventional path bind array in bytes
SILENT = {HEADER | FEEDBACK | ERROR | DISCARDS | ALL }
-- Suppress messages during run
For example:
OPTIONS (BINDSIZE=10000, SILENT=(ERRORS, FEEDBACK) )
Values specified on the command line override values specified in the control file. With this precedence, the OPTIONS keyword in the control file established default values that are easily changed from the command line. Continuing Interrupted Loads If SQL*Loader runs out of space for data rows or index entries, the load is discontinued. (For example, the table might reach its maximum number of extents.) Discontinued loads can be continued after more space is made available. When a load is discontinued, any data already loaded remains in the tables, and the tables are left in a valid state. SQL*Loader's log file tells you the state of the tables and indexes and the number of logical records already read from the input data file. Use this information to resume the load where it left off.
For example:
SQLLOAD / CONTROL=FAST1.CTL SKIP=345
CONTINUE\_LOAD DATA statement is used to continue a discontinued direct path load involving multiple tables with a varying number of records to skip. For more information on this command, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''.
Identifying Data Files
To specify the file containing the data to be loaded, use the INFILE or INDDN keyword, followed by the filename. A filename specified on the command line overrides the first INFILE or INDDN statement in the control file. If no filename is specified, the filename defaults to the control filename with an extension or filetype of DAT.
Loading into Non-Empty Database Tables
SQL*Loader does not update existing records, even if they have null columns. If the tables you are loading already contain data, you have three choices for how SQL*Loader should proceed:
1. INSERT - This is the default option. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.
2. APPEND - If data already exists in the table, SQL*Loader appends the new rows to it; if data doesn't already exist, the new rows are simply loaded.
3. REPLACE - All rows in the table are deleted and the new data is loaded. This option requires DELETE privileges on the table.
You can create one logical record from multiple physical records using CONCATENATE and CONTINUEIF. See chapter 6 of 'ORACLE7 Server Utilities Users Guide'.
Loading Logical Records into Tables
1. The INTO TABLE clause allows you to tell which table you want to load data into. To load multiple tables, you would include one INTO TABLE clause for each table you wish to load. The INTO TABLE clause may continue with some options for loading that table. For example, you may specify different options (INSERT, APPEND, REPLACE) for each table in order to tell SQL*Loader what to do if data already exists in the table.
2. The WHEN clause appears after the table name and is followed by one or more field conditions. For example, the following clause indicates that any record with the value ``q'' in the fifth column position should be loaded:
WHEN (5) = 'q'
A WHEN clause can contain several comparisons as long as each is preceded by AND. Parentheses are optional but should be used for clarity with multiple comparisons joined by AND. For example:
WHEN (DEPTNO = '10') AND (JOB = 'SALES')
To evaluate the WHEN clause, SQL*Loader first determines the values of all the fields in the record. Then the WHEN clause is evaluated. A row is inserted into the table only if the WHEN clause is true. When the control file specifies more fields for a record than are present in the record, SQL*Loader must determine whether the remaining (specified) columns should be considered null, or whether an error should be generated. TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. For example, if the following
data
10 Accounting
is read with the following control file
INTO TABLE dept
TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
dname CHAR TERMINATED BY WHITESPACE,
loc CHAR TERMINATED BY WHITESPACE )
and the record ends after DNAME, then the remaining LOC field is set to null. Without the TRAILING NULLCOLS clause, an error would be generated, due to missing data.
Specifying Datatypes
The datatype specification in the control file tells SQL*Loader how to interpret the information in the data file. The server defines the datatypes for the columns in the database. SQL*Loader extracts data from a field in the input file, guided by the datatype specification in the control file. SQL*Loader then sends the field to the server to be stored in the appropriate column. The server does any data conversion necessary to store the data in the proper internal format. The datatype of the data in the file does not necessarily have to be the same as the datatype of the column in the ORACLE table. ORACLE automatically performs conversions - but you need to ensure that the conversion makes sense and does not generate errors.
SQL*Loader does not contain datatype specifications for ORACLE internal datatypes like NUMBER or VARCHAR2. SQL*Loader's datatypes describe data that can be produced with text editors (character datatypes) and with standard programming languages (native datatypes).
Native Datatypes
Some datatypes consist entirely of binary data, or contain binary data in their implementation. These non-character datatypes are the native datatypes:
INTEGER ZONED SMALLINT
VARCHAR FLOAT GRAPHIC
DOUBLE GRAPHIC EXTERNAL BYTEINT
VARGRAPHIC (packed) DECIMAL RAW
These datatypes will not be discussed as most of the datatypes that you will be using will be character datatypes. For more information on SQL*Loader datatypes,
Character Datatypes
The character datatypes are CHAR, DATE, and the numeric EXTERNAL datatypes (INTEGER and DECIMAL). These fields can be delimited, and can have lengths (or maximum lengths) specified in the control file.
1. CHAR - This data field contains character data. The length is optional, and is taken from the POSITION specification if it is not present here. If present, this length overrides the length in the POSITION specification. If no length is given, CHAR data is assumed to have a length of 1. A field of datatype CHAR may also be variable-length delimited or enclosed.
2. To Load LONG Data: If the column in the database table is defined as LONG, you must explicitly specify a maximum length either with a length-specifier on the CHAR keyword, or with the POSITION keyword. This guarantees that a large enough buffer is allocated for the value, and is necessary even if the data is delimited or enclosed.
3. DATE - This data is character data that should be converted to an ORACLE date using the specified date mask. The length specification is optional, unless a varying-length data mask is specified. With a specification like:
DATE "Month dd, YYYY"
the date mask is 14 characters, while the length of a field like
September 31, 1991
is 18 characters. In this case, a length must be specified. Similarly, a length is required for any Julian dates (date mask ``J'') - a field length is required any time the length of the date string could exceed the length of the mask. An explicit length specification, if present, overrides the length in the POSITION clause. Either of these overrides the length derived from the mask. The mask may be any valid ORACLE date mask. If you omit the mask, the default ORACLE date mask of 'dd-mon-yy' is used.
4. Numeric EXTERNAL - The numeric external datatypes are the numeric datatypes (INTEGER, FLOAT, DECIMAL, and ZONED) specified with the EXTERNAL keyword along with optional length and delimiter specifications. These datatypes are the human-readable, character form of numeric data. The data is a number in character form (not binary representation). As such, these datatypes are identical to CHAR and are treated identically, with one exception: the use of DEFAULTIF. If you want the default to be null, use CHAR;
5. delimiter_spec - The boundaries of CHAR, DATE, or numeric EXTERNAL fields may also be marked by specific delimiter characters contained in the input data record. You indicate how the field is delimited by using a delimiter specification after specifying the datatype. Delimited data can be TERMINATED or ENCLOSED.
SQL*Loader is a product for moving data from external files into tables in an ORACLE database. SQL*Loader loads data in a variety of formats, performs filtering (selectively loading records based upon the data values), and loads multiple tables simultaneously. During execution SQL*Loader produces a detailed log file with statistics about the load, and may also produce a bad file (records rejected because of incorrect data) and a discard file (records that did not meet your selection criteria). You have control over several loading options.
You must provide two types of input to SQL*Loader to load data from external files into an ORACLE database: the data itself, and control information describing how to perform the load. You must provide a file called the control file as an input to SQL*Loader. The control file tells SQL*Loader how to interpret the data file. For example, it describes the following:
1. The names of the data files
2. The format of the data files
3. The character sets used in the data files
4. The datatypes of the fields in those files
5. How to identify the start and end of data fields
6. Which tables and columns to load
The control file's datatype specifications tell SQL*Loader how to interpret the fields in the data files. SQL*Loader uses this information when working with the fields, and uses it to describe the data that is being passed to ORACLE. ORACLE then converts the data into the datatype specified by the table definition. Some information is mandatory (such as where to find the data and how it corresponds to the database tables). However, many options are also available to describe and manipulate the file data. For example, the instructions can include directions on how to format or filter the data, or to generate unique ID numbers.
You may load data in various formats. It is usually read from one or more data files, but the data may also be placed in the control file after the control file information. Data records may be in fixed or variable format. In fixed format, the data is contained in records which all have the same (fixed) format. That is, the records have a fixed length, and the data fields in those records have fixed length, type, and position.
In variable format (sometimes called stream format), each record is only as long as necessary to contain the data. With character data, if the first item is shorter than the second one, the first record is shorter. Also, the type of data in each record may vary. One record may contain a character string, the next may contain seven integers, the third may contain three decimals and a float, and so on. Operating systems use a record terminator character (such as newline) to mark where variable records end.
Delimited data is of two types: terminated or enclosed. Terminated data is followed by a specified character such as a comma, as in the following example:
1,1,2,3,5,8,13
Enclosed data is preceded and followed by a specified character such as a quotation mark, as in the following example:
'BUNKY'
A final distinction concerns the difference between logical and physical records.
A record or line in a file (either of fixed length or terminated) is referred to as a physical record. Logical record, on the other hand, corresponds to a row in a database table. Sometimes the logical and physical records are equivalent; such is the case when only a few short columns are being loaded.
However, sometimes several physical records must be combined to make one logical record. The examples below will illustrate some of the features of the SQL*Loader.
Example 1. Loading Data into Multiple Tables
CONTROL FILE: The control file for this example.
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJNO listed
-- for each employee
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'
a. REPLACE
b. INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)
b. INTO TABLE proj
-- PROJ has two columns, both not null: EMPNO and PROJNO
c. WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
c. projno POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj
b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
---------------------------------------------------------------
NOTES:
(a) REPLACE indicates that if there is data in the tables to be loaded
(EMP and PROJ), that data should be deleted before new rows are loaded.
(b) Multiple INTO clauses are used to load two tables, EMP and PROJ. The
same set of records is processed three times using different combinations
of columns each time, to load table PROJ.
(c) WHEN is used to load only rows with non-blank project numbers. When
PROJNO is defined as columns 25..27, rows are inserted into PROJ only if
there is a value in those columns.
DATA FILE - Part of the data file follows.
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE5.CTL LOG=ULCASE5.LOG
Example 2 Loading a Delimited, Free-Format File
CONTROL FILE - The control file for this example.
-- Variable-length, delimited and enclosed data format
LOAD DATA
a. INFILE *
b. APPEND
INTO TABLE emp
c. FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
d. hiredate DATE "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':',
projno,
e. loadseq SEQUENCE(MAX,1))
f. BEGINDATA
7782, "CLARK", "Manager", 7839, 09-June-1981, 2572.50, 10:101
7839, "King", "President", , 17-January-1982, 920.00, 10:102
---------------------------------------------------------------
NOTES:
(a) INFILE * signifies the data is found at the end of the control file.
(b) APPEND indicates that data may be loaded even if the table already
contains rows; the table need not be empty.
(c) The default terminator for the data fields is a comma, and some fields
may be enclosed by a double quote.
(d) The data to be loaded into column HIREDATE appears in the format
DD-Month-YYYY.
(e) The SEQUENCE function is used to generate a unique value in the column
LOADSEQ. This function finds the current maximum value in column LOADSEQ
and adds the increment (1) to it to obtain the value for LOADSEQ for each
row inserted.
(f) BEGINDATA signifies the end of the control information and the
beginning of the data.
INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE3.CTL LOG=ULCASE3.LOG
Control File Syntax
The control file usually begins with the phase LOAD DATA, followed by several phrases that describe the data to be loaded. Only comments or the OPTIONS phrase can precede the LOAD DATA phase.
For a complete control file syntax diagram see Appendix C in this manual. Only a subset of the syntax will be explained below. For a complete explanation of the above syntax, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''. Comments Comments may appear anywhere in the command section of the file, but they should not appear in the data. Comments are preceded with a double dash, which may appear anywhere on a line. All text to the right of the double dash is ignored,
until the end of line.
The OPTIONS Clause
The OPTIONS clause is useful when you usually invoke a control file with the same set of options, or when the command line and all its arguments becomes very long. This clause allows you to specify runtime arguments in the control file rather than on the command line.
SKIP = n -- Number of logical records to skip (DEFAULT 0)
LOAD = n -- Number of logical records to load (DEFAULT all)
ERRORS = n -- Number of errors to allow (DEFAULT 50)
ROWS = n -- Number of rows in conventional path bind array (DEFAULT 64)
BINDSIZE = n -- Size of conventional path bind array in bytes
SILENT = {HEADER | FEEDBACK | ERROR | DISCARDS | ALL }
-- Suppress messages during run
For example:
OPTIONS (BINDSIZE=10000, SILENT=(ERRORS, FEEDBACK) )
Values specified on the command line override values specified in the control file. With this precedence, the OPTIONS keyword in the control file established default values that are easily changed from the command line. Continuing Interrupted Loads If SQL*Loader runs out of space for data rows or index entries, the load is discontinued. (For example, the table might reach its maximum number of extents.) Discontinued loads can be continued after more space is made available. When a load is discontinued, any data already loaded remains in the tables, and the tables are left in a valid state. SQL*Loader's log file tells you the state of the tables and indexes and the number of logical records already read from the input data file. Use this information to resume the load where it left off.
For example:
SQLLOAD / CONTROL=FAST1.CTL SKIP=345
CONTINUE\_LOAD DATA statement is used to continue a discontinued direct path load involving multiple tables with a varying number of records to skip. For more information on this command, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''.
Identifying Data Files
To specify the file containing the data to be loaded, use the INFILE or INDDN keyword, followed by the filename. A filename specified on the command line overrides the first INFILE or INDDN statement in the control file. If no filename is specified, the filename defaults to the control filename with an extension or filetype of DAT.
Loading into Non-Empty Database Tables
SQL*Loader does not update existing records, even if they have null columns. If the tables you are loading already contain data, you have three choices for how SQL*Loader should proceed:
1. INSERT - This is the default option. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.
2. APPEND - If data already exists in the table, SQL*Loader appends the new rows to it; if data doesn't already exist, the new rows are simply loaded.
3. REPLACE - All rows in the table are deleted and the new data is loaded. This option requires DELETE privileges on the table.
You can create one logical record from multiple physical records using CONCATENATE and CONTINUEIF. See chapter 6 of 'ORACLE7 Server Utilities Users Guide'.
Loading Logical Records into Tables
1. The INTO TABLE clause allows you to tell which table you want to load data into. To load multiple tables, you would include one INTO TABLE clause for each table you wish to load. The INTO TABLE clause may continue with some options for loading that table. For example, you may specify different options (INSERT, APPEND, REPLACE) for each table in order to tell SQL*Loader what to do if data already exists in the table.
2. The WHEN clause appears after the table name and is followed by one or more field conditions. For example, the following clause indicates that any record with the value ``q'' in the fifth column position should be loaded:
WHEN (5) = 'q'
A WHEN clause can contain several comparisons as long as each is preceded by AND. Parentheses are optional but should be used for clarity with multiple comparisons joined by AND. For example:
WHEN (DEPTNO = '10') AND (JOB = 'SALES')
To evaluate the WHEN clause, SQL*Loader first determines the values of all the fields in the record. Then the WHEN clause is evaluated. A row is inserted into the table only if the WHEN clause is true. When the control file specifies more fields for a record than are present in the record, SQL*Loader must determine whether the remaining (specified) columns should be considered null, or whether an error should be generated. TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. For example, if the following
data
10 Accounting
is read with the following control file
INTO TABLE dept
TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
dname CHAR TERMINATED BY WHITESPACE,
loc CHAR TERMINATED BY WHITESPACE )
and the record ends after DNAME, then the remaining LOC field is set to null. Without the TRAILING NULLCOLS clause, an error would be generated, due to missing data.
Specifying Datatypes
The datatype specification in the control file tells SQL*Loader how to interpret the information in the data file. The server defines the datatypes for the columns in the database. SQL*Loader extracts data from a field in the input file, guided by the datatype specification in the control file. SQL*Loader then sends the field to the server to be stored in the appropriate column. The server does any data conversion necessary to store the data in the proper internal format. The datatype of the data in the file does not necessarily have to be the same as the datatype of the column in the ORACLE table. ORACLE automatically performs conversions - but you need to ensure that the conversion makes sense and does not generate errors.
SQL*Loader does not contain datatype specifications for ORACLE internal datatypes like NUMBER or VARCHAR2. SQL*Loader's datatypes describe data that can be produced with text editors (character datatypes) and with standard programming languages (native datatypes).
Native Datatypes
Some datatypes consist entirely of binary data, or contain binary data in their implementation. These non-character datatypes are the native datatypes:
INTEGER ZONED SMALLINT
VARCHAR FLOAT GRAPHIC
DOUBLE GRAPHIC EXTERNAL BYTEINT
VARGRAPHIC (packed) DECIMAL RAW
These datatypes will not be discussed as most of the datatypes that you will be using will be character datatypes. For more information on SQL*Loader datatypes,
Character Datatypes
The character datatypes are CHAR, DATE, and the numeric EXTERNAL datatypes (INTEGER and DECIMAL). These fields can be delimited, and can have lengths (or maximum lengths) specified in the control file.
1. CHAR - This data field contains character data. The length is optional, and is taken from the POSITION specification if it is not present here. If present, this length overrides the length in the POSITION specification. If no length is given, CHAR data is assumed to have a length of 1. A field of datatype CHAR may also be variable-length delimited or enclosed.
2. To Load LONG Data: If the column in the database table is defined as LONG, you must explicitly specify a maximum length either with a length-specifier on the CHAR keyword, or with the POSITION keyword. This guarantees that a large enough buffer is allocated for the value, and is necessary even if the data is delimited or enclosed.
3. DATE - This data is character data that should be converted to an ORACLE date using the specified date mask. The length specification is optional, unless a varying-length data mask is specified. With a specification like:
DATE "Month dd, YYYY"
the date mask is 14 characters, while the length of a field like
September 31, 1991
is 18 characters. In this case, a length must be specified. Similarly, a length is required for any Julian dates (date mask ``J'') - a field length is required any time the length of the date string could exceed the length of the mask. An explicit length specification, if present, overrides the length in the POSITION clause. Either of these overrides the length derived from the mask. The mask may be any valid ORACLE date mask. If you omit the mask, the default ORACLE date mask of 'dd-mon-yy' is used.
4. Numeric EXTERNAL - The numeric external datatypes are the numeric datatypes (INTEGER, FLOAT, DECIMAL, and ZONED) specified with the EXTERNAL keyword along with optional length and delimiter specifications. These datatypes are the human-readable, character form of numeric data. The data is a number in character form (not binary representation). As such, these datatypes are identical to CHAR and are treated identically, with one exception: the use of DEFAULTIF. If you want the default to be null, use CHAR;
5. delimiter_spec - The boundaries of CHAR, DATE, or numeric EXTERNAL fields may also be marked by specific delimiter characters contained in the input data record. You indicate how the field is delimited by using a delimiter specification after specifying the datatype. Delimited data can be TERMINATED or ENCLOSED.
Tuesday, October 7, 2008
All About 'FND_GLOBAL' in Oracle
Before calling a concurrent request from the backend, we need to set the Oracle Applications Context. Use the API FND_GLOBAL.APPS_INITIALIZE to set the applications context in standalone sessions that were not initialized through normal means. Typically, you would use this API in external custom programs that are establishing their own connections.
FND_GLOBAL.APPS_INITIALIZE
Procedure APPS_INITIALIZE (user_id in number, resp_id in number, resp_appl_id in number security_group_id in number);
Description:
This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as PL/SQL or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session).
The typical use for this routine would be as part of the logic for launching a separate non-Forms session from an established Oracle Applications form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection.
You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle Applications form session.
Arguments (input)
1. USER_ID - The User ID number.
SELECT *
FROM FND_USER
WHERE USER_NAME='SYSADMIN'
2. RESP_ID - The ID number of the responsibility.
SELECT *
FROM FND_RESPONSIBILITY_TL
WHERE RESPONSIBILITY_NAME ='Bills of Material'
AND LANGUAGE='US'
3. RESP_APPL_ID - The ID number of the application to which the responsibility belongs.
SELECT *
FROM FND_APPLICATION
WHERE APPLICATION_ID=702
4. SECURITY_GROUP_ID - The ID number of the security group. This argument is automatically defaulted by the API. The caller should not pass a value for it.
Example:
fnd_global.APPS_INITIALIZE (1010, 20417, 201);
FND_GLOBAL.APPS_INITIALIZE
Procedure APPS_INITIALIZE (user_id in number, resp_id in number, resp_appl_id in number security_group_id in number);
Description:
This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as PL/SQL or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session).
The typical use for this routine would be as part of the logic for launching a separate non-Forms session from an established Oracle Applications form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection.
You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle Applications form session.
Arguments (input)
1. USER_ID - The User ID number.
SELECT *
FROM FND_USER
WHERE USER_NAME='SYSADMIN'
2. RESP_ID - The ID number of the responsibility.
SELECT *
FROM FND_RESPONSIBILITY_TL
WHERE RESPONSIBILITY_NAME ='Bills of Material'
AND LANGUAGE='US'
3. RESP_APPL_ID - The ID number of the application to which the responsibility belongs.
SELECT *
FROM FND_APPLICATION
WHERE APPLICATION_ID=702
4. SECURITY_GROUP_ID - The ID number of the security group. This argument is automatically defaulted by the API. The caller should not pass a value for it.
Example:
fnd_global.APPS_INITIALIZE (1010, 20417, 201);
Monday, October 6, 2008
Different types of Oracle Applications Updates.
Oracle Applications organizes updates based on granularity:
One-off:
This is the most granular update required to change a particular behavior or fix a particular defect. These updates tend to be smaller, but due to product dependencies may be large.
Mini-pack:
This is an aggregation of all updates for a particular product, such as General Ledger (GL). The updates are aggregated into a single, easily applied patch that will take you to the latest code level for a particular product area. Mini-packs are named successively by letter. For example, 11i.GL.A, 11i.GL.B, etc. Mini-packs replace the previous concepts of Applications patchsets, mega-patches, or rollups.
Family Pack:
This is an aggregation of all updates for a particular family, such as the Procurement Family (PRC_PF), which is made up of Purchasing, Internet Procurement, Supplier Scheduling, and Internet Supplier Portal. The updates are aggregated into a single, easily applied patch that will take you to the latest code level for the entire product family.
Family packs are named successively by letter. For example, 11i.PRC_PF.F, 11i.PRC_PF.G, etc.
Maintenance Pack:
This is an aggregation of all updates for the entire E-Business Suite, including all ERP and CRM products. The updates are aggregated into a single, easily applied patch that will take you to the latest code level for the entire E-Business Suite. Maintenance Packs are numbered as point releases, 11.5.3, 11.5.4, 11.5.5, etc. Additionally, for customers not yet on 11i, the Rapid Install is repackaged at each Maintenance Pack level.
Oracle Applications updates can also be classified by type:
Interoperability patch:
This is an update to Oracle Applications code that will enable a change to the underlying technology stack. For example: Applications Release 11i interoperability patch for Oracle 9i, Applications Release 11i interoperability patch for Developer 6i patch 9, etc.
Diagnostic patch:
This type of patch is intended to gather additional information about a particular issue being encountered at a customer site. It is often used when the issue cannot be reproduced on-site at Oracle.
Translated update:
This is a translated version of a patch. It contains only the components requiring translation. Language independent portions of a base patch are not included in a translated patch, therefore you must always apply the base US patch as well as the translated patch.
Product Introduction:
This type of update introduces a new product to a release using the patching infrastructure. Examples include Oracle Process Manufacturing for Release 11.0, and Oracle Enterprise Asset Management for Release 11i.
Note: All patches can be downloaded via Oracle MetaLink for any supported platform or language. Customers can query a specific patch, or they can query for patches based on Release, Products, Platforms, or Languages. The search can also be limited to high priority patches, mini-packs, or patches not in the latest product mini-pack. The patch READMEs can also be viewed online.
One-off:
This is the most granular update required to change a particular behavior or fix a particular defect. These updates tend to be smaller, but due to product dependencies may be large.
Mini-pack:
This is an aggregation of all updates for a particular product, such as General Ledger (GL). The updates are aggregated into a single, easily applied patch that will take you to the latest code level for a particular product area. Mini-packs are named successively by letter. For example, 11i.GL.A, 11i.GL.B, etc. Mini-packs replace the previous concepts of Applications patchsets, mega-patches, or rollups.
Family Pack:
This is an aggregation of all updates for a particular family, such as the Procurement Family (PRC_PF), which is made up of Purchasing, Internet Procurement, Supplier Scheduling, and Internet Supplier Portal. The updates are aggregated into a single, easily applied patch that will take you to the latest code level for the entire product family.
Family packs are named successively by letter. For example, 11i.PRC_PF.F, 11i.PRC_PF.G, etc.
Maintenance Pack:
This is an aggregation of all updates for the entire E-Business Suite, including all ERP and CRM products. The updates are aggregated into a single, easily applied patch that will take you to the latest code level for the entire E-Business Suite. Maintenance Packs are numbered as point releases, 11.5.3, 11.5.4, 11.5.5, etc. Additionally, for customers not yet on 11i, the Rapid Install is repackaged at each Maintenance Pack level.
Oracle Applications updates can also be classified by type:
Interoperability patch:
This is an update to Oracle Applications code that will enable a change to the underlying technology stack. For example: Applications Release 11i interoperability patch for Oracle 9i, Applications Release 11i interoperability patch for Developer 6i patch 9, etc.
Diagnostic patch:
This type of patch is intended to gather additional information about a particular issue being encountered at a customer site. It is often used when the issue cannot be reproduced on-site at Oracle.
Translated update:
This is a translated version of a patch. It contains only the components requiring translation. Language independent portions of a base patch are not included in a translated patch, therefore you must always apply the base US patch as well as the translated patch.
Product Introduction:
This type of update introduces a new product to a release using the patching infrastructure. Examples include Oracle Process Manufacturing for Release 11.0, and Oracle Enterprise Asset Management for Release 11i.
Note: All patches can be downloaded via Oracle MetaLink for any supported platform or language. Customers can query a specific patch, or they can query for patches based on Release, Products, Platforms, or Languages. The search can also be limited to high priority patches, mini-packs, or patches not in the latest product mini-pack. The patch READMEs can also be viewed online.
Benefits of the Oracle E-Business Suite
Process Automation with Enterprise Integration
The Oracle E-Business Suite provides comprehensive automation across your enterprise because all the applications comprising the suite are engineered to work together. Best-of-breed applications automate only bits and pieces of any given process, so marketing remains disconnected from sales, sales remains disconnected from finance, and so on.
Complete Automation
If your applications do not work together and do not automate business processes, then you will never get good information out of your system. If you automate sales, but not marketing, you will never know how effective your marketing is. If you automate sales, but not financials, you'll never know how many customers you sold to actually got invoiced. Because you don't know, you don't ask. If you don't ask, your business never gets more efficient. Without improved efficiency your business never spends less capital. Oracle E-Business Suite provides end-to-end transactional automation that gives you more for less.
Functionally Complete
The Oracle E-Business Suite integrates your business, capturing the information needed to provide a complete view of what’s going on in your business. Oracle’s suite is functionally complete with over 100 integrated product modules for every aspect of your business, including customer relationship management, finance, human resources, professional services automation, supply chain management, enterprise asset management, and project management.
Complete, Automated E-Business Flows
To enable rapid automation of key E-Business flows inherent in the E-Business Suite, Oracle provides FastForward Flows. These fixed-price, rapid-time services enable you to reap the benefits of Oracle's applications in record time by automating critical business processes within your enterprise and providing end-to-end integration.
Complete for Targeted Industries
The unique requirements of many targeted industries are included with the Oracle E-Business Suite. Oracle's complete offering meets the needs of: aerospace and defense, high tech, consumer packaged goods, automotive, pharmaceuticals, public sector, and many more industries.
Complete, Global Business Offering
The Oracle E-Business Suite is also globally comprehensive, supporting 30 languages, all character sets, unique global business practices, and local statutory and regulatory requirements. Oracle's applications enable you to unify information and to run your business consistently and accurately worldwide.
Oracle's Open, Modular System for Easier Integration
Many customers have implemented the E-Business Suite with zero customizations. All of Oracle's applications are engineered to work together, so you can significantly reduce your IT expenses by eliminating the need for software interfacing, data translation, and integration. If you implement one or several modules of the E-Business Suite, Oracle makes integration with your legacy and third-party applications easier since Oracle's software architecture is built on open, industry standards. To further reduce implementation costs, the Oracle E-Business Suite requires few, if any, customizations.
Complete Information With A Single Database
With other software vendors, you can spend millions of dollars on application software and still not have the information you need to run your business. Why? Information is spread throughout a company -- financials data is in one database, HR data in another, and so forth. This fragmented data makes it difficult and expensive to collaborate and access information across geographies and business processes.
Single, Complete Data Model Provides Better Information at a Lower Cost
Oracle's E-Business Suite is the first and only complete set of applications for the enterprise that are integrated around a single, comprehensive data model. Oracle provides a single definition of your customers, suppliers, employees, products -- all aspects of your business. Everyone in your company can access the same enterprise-wide data. Your marketing organization accesses the same customer information that your sales and order management departments are using.
One Database For Consolidated Information, Accessible by All
Companies need to seamlessly connect all their business processes, and store all their information in as few databases as possible, one being the ideal. The fewer databases you use to store your information, the easier it is to share information between organizations, enabling better co-operation and greater productivity across departments. What's more, it actually costs less to get better information! You can eliminate the duplicate data centers, hardware, and IT costs involved with multiple databases around the world.
Daily Business Close
For the first time you can access daily organizational metrics and use this data to drive change managing your businesses by fact, instead of by guesswork. Currently, businesses close their financial books every 30 days and report business metrics on a quarterly basis. At best you get critical, corporate-wide information only 4 times a year and have to wait an additional 2 weeks to get the data. The Oracle EBusiness Suite enables companies to do a Daily Business Close, delivering critical information for all lines of business via a personalized portal. You can now review daily, your lead positions, sales positions, inventory levels, headcount, and other key performance metrics.
With Oracle's complete suite and end-to-end process automation, every application has access to the same information. Every application is built using a single data model, providing a single source of truth for a daily business close. Information is continuously summarized and displayed in customizable portals, viewable by line of business, geography, department or product. And because the business intelligence system and the data sit in the same system, the information is real-time so you don't have to wait for data to pass through a separate data aggregation and analysis system. Building a data warehouse to consolidate fragmented data is time consuming, expensive, and still doesn't provide answers. If your business applications can't deliver daily key indicators for your business then perhaps you've got the wrong IT information architecture.
The Oracle E-Business Suite provides comprehensive automation across your enterprise because all the applications comprising the suite are engineered to work together. Best-of-breed applications automate only bits and pieces of any given process, so marketing remains disconnected from sales, sales remains disconnected from finance, and so on.
Complete Automation
If your applications do not work together and do not automate business processes, then you will never get good information out of your system. If you automate sales, but not marketing, you will never know how effective your marketing is. If you automate sales, but not financials, you'll never know how many customers you sold to actually got invoiced. Because you don't know, you don't ask. If you don't ask, your business never gets more efficient. Without improved efficiency your business never spends less capital. Oracle E-Business Suite provides end-to-end transactional automation that gives you more for less.
Functionally Complete
The Oracle E-Business Suite integrates your business, capturing the information needed to provide a complete view of what’s going on in your business. Oracle’s suite is functionally complete with over 100 integrated product modules for every aspect of your business, including customer relationship management, finance, human resources, professional services automation, supply chain management, enterprise asset management, and project management.
Complete, Automated E-Business Flows
To enable rapid automation of key E-Business flows inherent in the E-Business Suite, Oracle provides FastForward Flows. These fixed-price, rapid-time services enable you to reap the benefits of Oracle's applications in record time by automating critical business processes within your enterprise and providing end-to-end integration.
Complete for Targeted Industries
The unique requirements of many targeted industries are included with the Oracle E-Business Suite. Oracle's complete offering meets the needs of: aerospace and defense, high tech, consumer packaged goods, automotive, pharmaceuticals, public sector, and many more industries.
Complete, Global Business Offering
The Oracle E-Business Suite is also globally comprehensive, supporting 30 languages, all character sets, unique global business practices, and local statutory and regulatory requirements. Oracle's applications enable you to unify information and to run your business consistently and accurately worldwide.
Oracle's Open, Modular System for Easier Integration
Many customers have implemented the E-Business Suite with zero customizations. All of Oracle's applications are engineered to work together, so you can significantly reduce your IT expenses by eliminating the need for software interfacing, data translation, and integration. If you implement one or several modules of the E-Business Suite, Oracle makes integration with your legacy and third-party applications easier since Oracle's software architecture is built on open, industry standards. To further reduce implementation costs, the Oracle E-Business Suite requires few, if any, customizations.
Complete Information With A Single Database
With other software vendors, you can spend millions of dollars on application software and still not have the information you need to run your business. Why? Information is spread throughout a company -- financials data is in one database, HR data in another, and so forth. This fragmented data makes it difficult and expensive to collaborate and access information across geographies and business processes.
Single, Complete Data Model Provides Better Information at a Lower Cost
Oracle's E-Business Suite is the first and only complete set of applications for the enterprise that are integrated around a single, comprehensive data model. Oracle provides a single definition of your customers, suppliers, employees, products -- all aspects of your business. Everyone in your company can access the same enterprise-wide data. Your marketing organization accesses the same customer information that your sales and order management departments are using.
One Database For Consolidated Information, Accessible by All
Companies need to seamlessly connect all their business processes, and store all their information in as few databases as possible, one being the ideal. The fewer databases you use to store your information, the easier it is to share information between organizations, enabling better co-operation and greater productivity across departments. What's more, it actually costs less to get better information! You can eliminate the duplicate data centers, hardware, and IT costs involved with multiple databases around the world.
Daily Business Close
For the first time you can access daily organizational metrics and use this data to drive change managing your businesses by fact, instead of by guesswork. Currently, businesses close their financial books every 30 days and report business metrics on a quarterly basis. At best you get critical, corporate-wide information only 4 times a year and have to wait an additional 2 weeks to get the data. The Oracle EBusiness Suite enables companies to do a Daily Business Close, delivering critical information for all lines of business via a personalized portal. You can now review daily, your lead positions, sales positions, inventory levels, headcount, and other key performance metrics.
With Oracle's complete suite and end-to-end process automation, every application has access to the same information. Every application is built using a single data model, providing a single source of truth for a daily business close. Information is continuously summarized and displayed in customizable portals, viewable by line of business, geography, department or product. And because the business intelligence system and the data sit in the same system, the information is real-time so you don't have to wait for data to pass through a separate data aggregation and analysis system. Building a data warehouse to consolidate fragmented data is time consuming, expensive, and still doesn't provide answers. If your business applications can't deliver daily key indicators for your business then perhaps you've got the wrong IT information architecture.
Sunday, October 5, 2008
R12 HRMS: Single record insertion for Creating Employee
DECLARE
ip_p_person_id NUMBER;
ip_p_assignment_id NUMBER;
ip_p_per_object_version_number NUMBER;
ip_p_asg_object_version_number NUMBER;
ip_p_per_effective_start_date DATE;
ip_p_per_effective_end_date DATE;
ip_p_full_name VARCHAR2 (1000);
ip_p_per_comment_id NUMBER;
ip_p_assignment_sequence NUMBER;
ip_p_assignment_number VARCHAR2 (1000);
ip_p_name_combination_warning BOOLEAN;
ip_p_assign_payroll_warning BOOLEAN;
ip_p_orig_hire_warning BOOLEAN;
ip_employee_number VARCHAR2 (1000);
BEGIN
--ip_employee_number := 'Apps-101';
hr_employee_api.create_employee
(p_hire_date => SYSDATE,
p_business_group_id => 202,
p_last_name => 'Arun',
p_sex => 'M',
p_person_type_id => 13,
p_per_comments => NULL,
p_date_employee_data_verified => NULL,
p_date_of_birth => TO_DATE
('01-April-84',
'DD-MONTH-RR'
),
p_email_address => 'arun_apps@yahoo.com',
p_employee_number => ip_employee_number,
p_expense_check_send_to_addres => NULL,
p_first_name => 'Rathod',
p_known_as => 'Arun',
p_marital_status => 'S',
p_middle_names => NULL,
p_nationality => 'IND',
p_national_identifier => NULL,
p_previous_last_name => NULL,
p_registered_disabled_flag => NULL,
p_title => 'MR.',
p_vendor_id => NULL,
p_work_telephone => '781-1234-456',
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_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_per_information_category => NULL,
p_per_information1 => NULL,
p_per_information2 => NULL,
p_per_information3 => NULL,
p_per_information4 => NULL,
p_per_information5 => NULL,
p_per_information6 => NULL,
p_per_information7 => NULL,
p_per_information8 => NULL,
p_per_information9 => NULL,
p_per_information10 => NULL,
p_per_information11 => NULL,
p_per_information12 => NULL,
p_per_information13 => NULL,
p_per_information14 => NULL,
p_per_information15 => NULL,
p_per_information16 => NULL,
p_per_information17 => NULL,
p_per_information18 => NULL,
p_per_information19 => NULL,
p_per_information20 => NULL,
p_per_information21 => NULL,
p_per_information22 => NULL,
p_per_information23 => NULL,
p_per_information24 => NULL,
p_per_information25 => NULL,
p_per_information26 => NULL,
p_per_information27 => NULL,
p_per_information28 => NULL,
p_per_information29 => NULL,
p_per_information30 => NULL,
p_date_of_death => NULL,
p_background_check_status => 'N',
p_background_date_check => NULL,
p_blood_type => 'B+',
p_correspondence_language => NULL,
p_fast_path_employee => NULL,
p_fte_capacity => NULL,
p_honors => NULL,
p_internal_location => NULL,
p_last_medical_test_by => NULL,
p_last_medical_test_date => NULL,
p_mailstop => NULL,
p_office_number => '781-2345-567',
p_on_military_service => NULL,
p_pre_name_adjunct => NULL,
p_rehire_recommendation => NULL,
p_projected_start_date => NULL,
p_resume_exists => NULL,
p_resume_last_updated => NULL,
p_second_passport_exists => NULL,
p_student_status => NULL,
p_work_schedule => NULL,
p_suffix => NULL,
p_benefit_group_id => NULL,
p_receipt_of_death_cert_date => NULL,
p_coord_ben_med_pln_no => NULL,
p_coord_ben_no_cvg_flag => NULL,
p_coord_ben_med_ext_er => NULL,
p_coord_ben_med_pl_name => NULL,
p_coord_ben_med_insr_crr_name => NULL,
p_coord_ben_med_insr_crr_ident => NULL,
p_coord_ben_med_cvg_strt_dt => NULL,
p_coord_ben_med_cvg_end_dt => NULL,
p_uses_tobacco_flag => NULL,
p_dpdnt_adoption_date => NULL,
p_dpdnt_vlntry_svce_flag => NULL,
p_original_date_of_hire => NULL,
p_adjusted_svc_date => NULL,
p_town_of_birth => NULL,
p_region_of_birth => NULL,
p_country_of_birth => 'IN',
p_global_person_id => NULL,
p_party_id => NULL,
p_person_id => ip_p_person_id,
p_assignment_id => ip_p_assignment_id,
p_per_object_version_number => ip_p_per_object_version_number,
p_asg_object_version_number => ip_p_asg_object_version_number,
p_per_effective_start_date => ip_p_per_effective_start_date,
p_per_effective_end_date => ip_p_per_effective_end_date,
p_full_name => ip_p_full_name,
p_per_comment_id => ip_p_per_comment_id,
p_assignment_sequence => ip_p_assignment_sequence,
p_assignment_number => ip_p_assignment_number,
p_name_combination_warning => ip_p_name_combination_warning,
p_assign_payroll_warning => ip_p_assign_payroll_warning,
p_orig_hire_warning => ip_p_orig_hire_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
ip_p_person_id NUMBER;
ip_p_assignment_id NUMBER;
ip_p_per_object_version_number NUMBER;
ip_p_asg_object_version_number NUMBER;
ip_p_per_effective_start_date DATE;
ip_p_per_effective_end_date DATE;
ip_p_full_name VARCHAR2 (1000);
ip_p_per_comment_id NUMBER;
ip_p_assignment_sequence NUMBER;
ip_p_assignment_number VARCHAR2 (1000);
ip_p_name_combination_warning BOOLEAN;
ip_p_assign_payroll_warning BOOLEAN;
ip_p_orig_hire_warning BOOLEAN;
ip_employee_number VARCHAR2 (1000);
BEGIN
--ip_employee_number := 'Apps-101';
hr_employee_api.create_employee
(p_hire_date => SYSDATE,
p_business_group_id => 202,
p_last_name => 'Arun',
p_sex => 'M',
p_person_type_id => 13,
p_per_comments => NULL,
p_date_employee_data_verified => NULL,
p_date_of_birth => TO_DATE
('01-April-84',
'DD-MONTH-RR'
),
p_email_address => 'arun_apps@yahoo.com',
p_employee_number => ip_employee_number,
p_expense_check_send_to_addres => NULL,
p_first_name => 'Rathod',
p_known_as => 'Arun',
p_marital_status => 'S',
p_middle_names => NULL,
p_nationality => 'IND',
p_national_identifier => NULL,
p_previous_last_name => NULL,
p_registered_disabled_flag => NULL,
p_title => 'MR.',
p_vendor_id => NULL,
p_work_telephone => '781-1234-456',
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_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_per_information_category => NULL,
p_per_information1 => NULL,
p_per_information2 => NULL,
p_per_information3 => NULL,
p_per_information4 => NULL,
p_per_information5 => NULL,
p_per_information6 => NULL,
p_per_information7 => NULL,
p_per_information8 => NULL,
p_per_information9 => NULL,
p_per_information10 => NULL,
p_per_information11 => NULL,
p_per_information12 => NULL,
p_per_information13 => NULL,
p_per_information14 => NULL,
p_per_information15 => NULL,
p_per_information16 => NULL,
p_per_information17 => NULL,
p_per_information18 => NULL,
p_per_information19 => NULL,
p_per_information20 => NULL,
p_per_information21 => NULL,
p_per_information22 => NULL,
p_per_information23 => NULL,
p_per_information24 => NULL,
p_per_information25 => NULL,
p_per_information26 => NULL,
p_per_information27 => NULL,
p_per_information28 => NULL,
p_per_information29 => NULL,
p_per_information30 => NULL,
p_date_of_death => NULL,
p_background_check_status => 'N',
p_background_date_check => NULL,
p_blood_type => 'B+',
p_correspondence_language => NULL,
p_fast_path_employee => NULL,
p_fte_capacity => NULL,
p_honors => NULL,
p_internal_location => NULL,
p_last_medical_test_by => NULL,
p_last_medical_test_date => NULL,
p_mailstop => NULL,
p_office_number => '781-2345-567',
p_on_military_service => NULL,
p_pre_name_adjunct => NULL,
p_rehire_recommendation => NULL,
p_projected_start_date => NULL,
p_resume_exists => NULL,
p_resume_last_updated => NULL,
p_second_passport_exists => NULL,
p_student_status => NULL,
p_work_schedule => NULL,
p_suffix => NULL,
p_benefit_group_id => NULL,
p_receipt_of_death_cert_date => NULL,
p_coord_ben_med_pln_no => NULL,
p_coord_ben_no_cvg_flag => NULL,
p_coord_ben_med_ext_er => NULL,
p_coord_ben_med_pl_name => NULL,
p_coord_ben_med_insr_crr_name => NULL,
p_coord_ben_med_insr_crr_ident => NULL,
p_coord_ben_med_cvg_strt_dt => NULL,
p_coord_ben_med_cvg_end_dt => NULL,
p_uses_tobacco_flag => NULL,
p_dpdnt_adoption_date => NULL,
p_dpdnt_vlntry_svce_flag => NULL,
p_original_date_of_hire => NULL,
p_adjusted_svc_date => NULL,
p_town_of_birth => NULL,
p_region_of_birth => NULL,
p_country_of_birth => 'IN',
p_global_person_id => NULL,
p_party_id => NULL,
p_person_id => ip_p_person_id,
p_assignment_id => ip_p_assignment_id,
p_per_object_version_number => ip_p_per_object_version_number,
p_asg_object_version_number => ip_p_asg_object_version_number,
p_per_effective_start_date => ip_p_per_effective_start_date,
p_per_effective_end_date => ip_p_per_effective_end_date,
p_full_name => ip_p_full_name,
p_per_comment_id => ip_p_per_comment_id,
p_assignment_sequence => ip_p_assignment_sequence,
p_assignment_number => ip_p_assignment_number,
p_name_combination_warning => ip_p_name_combination_warning,
p_assign_payroll_warning => ip_p_assign_payroll_warning,
p_orig_hire_warning => ip_p_orig_hire_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
R12 HRMS: Single record insertion for Address
DECLARE
ip_p_address_id NUMBER;
ip_p_object_version_number NUMBER;
BEGIN
hr_person_address_api.create_person_address
(p_validate => NULL,
p_effective_date => SYSDATE,
p_pradd_ovlapval_override => NULL,
p_validate_county => NULL,
p_person_id => 27754,
p_primary_flag => 'Y',
p_style => 'US',
p_date_from => '23-jan-1980',
p_date_to => SYSDATE,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => '5675 Clay Street',
p_address_line2 => NULL,
p_address_line3 => NULL,
p_town_or_city => 'Redwood Shores',
p_region_1 => 'San Mateo',
p_region_2 => 'CA',
p_region_3 => NULL,
p_postal_code => 94065,
p_country => 'US',
p_telephone_number_1 => '650-778-6065',
p_telephone_number_2 => NULL,
p_telephone_number_3 => NULL,
p_addr_attribute_category => NULL,
p_addr_attribute1 => NULL,
p_addr_attribute2 => NULL,
p_addr_attribute3 => NULL,
p_addr_attribute4 => NULL,
p_addr_attribute5 => NULL,
p_addr_attribute6 => NULL,
p_addr_attribute7 => NULL,
p_addr_attribute8 => NULL,
p_addr_attribute9 => NULL,
p_addr_attribute10 => NULL,
p_addr_attribute11 => NULL,
p_addr_attribute12 => NULL,
p_addr_attribute13 => NULL,
p_addr_attribute14 => NULL,
p_addr_attribute15 => NULL,
p_addr_attribute16 => NULL,
p_addr_attribute17 => NULL,
p_addr_attribute18 => NULL,
p_addr_attribute19 => NULL,
p_addr_attribute20 => NULL,
p_add_information13 => NULL,
p_add_information14 => NULL,
p_add_information15 => NULL,
p_add_information16 => NULL,
p_add_information17 => NULL,
p_add_information18 => NULL,
p_add_information19 => NULL,
p_add_information20 => NULL,
p_party_id => NULL,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
ip_p_address_id NUMBER;
ip_p_object_version_number NUMBER;
BEGIN
hr_person_address_api.create_person_address
(p_validate => NULL,
p_effective_date => SYSDATE,
p_pradd_ovlapval_override => NULL,
p_validate_county => NULL,
p_person_id => 27754,
p_primary_flag => 'Y',
p_style => 'US',
p_date_from => '23-jan-1980',
p_date_to => SYSDATE,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => '5675 Clay Street',
p_address_line2 => NULL,
p_address_line3 => NULL,
p_town_or_city => 'Redwood Shores',
p_region_1 => 'San Mateo',
p_region_2 => 'CA',
p_region_3 => NULL,
p_postal_code => 94065,
p_country => 'US',
p_telephone_number_1 => '650-778-6065',
p_telephone_number_2 => NULL,
p_telephone_number_3 => NULL,
p_addr_attribute_category => NULL,
p_addr_attribute1 => NULL,
p_addr_attribute2 => NULL,
p_addr_attribute3 => NULL,
p_addr_attribute4 => NULL,
p_addr_attribute5 => NULL,
p_addr_attribute6 => NULL,
p_addr_attribute7 => NULL,
p_addr_attribute8 => NULL,
p_addr_attribute9 => NULL,
p_addr_attribute10 => NULL,
p_addr_attribute11 => NULL,
p_addr_attribute12 => NULL,
p_addr_attribute13 => NULL,
p_addr_attribute14 => NULL,
p_addr_attribute15 => NULL,
p_addr_attribute16 => NULL,
p_addr_attribute17 => NULL,
p_addr_attribute18 => NULL,
p_addr_attribute19 => NULL,
p_addr_attribute20 => NULL,
p_add_information13 => NULL,
p_add_information14 => NULL,
p_add_information15 => NULL,
p_add_information16 => NULL,
p_add_information17 => NULL,
p_add_information18 => NULL,
p_add_information19 => NULL,
p_add_information20 => NULL,
p_party_id => NULL,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
R12 HRMS: Single record insertion for Assignment
DECLARE
l_cagr_grade_def_id NUMBER;
l_cagr_concatenated_segments VARCHAR2 (30);
l_concatenated_segments VARCHAR2 (280);
l_soft_coding_keyflex_id NUMBER;
l_comment_id NUMBER (15);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_no_managers_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_hourly_salaried_warning BOOLEAN;
l_gsp_post_process_warning VARCHAR2 (30);
l_api_mode VARCHAR2 (20);
l_object_version_number NUMBER;
l_assignment_id NUMBER;
BEGIN
l_api_mode := 'CORRECTION';
l_object_version_number := 2;
l_soft_coding_keyflex_id := 91;
hr_assignment_api.update_emp_asg
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => l_api_mode,
p_assignment_id => 27920,
p_object_version_number => l_object_version_number,
--l_object_version_number,
p_assignment_number => NULL,
p_change_reason => 'WS',
p_assignment_status_type_id => 1,
p_comments => NULL,
p_date_probation_end => NULL,
p_default_code_comb_id => NULL,
p_frequency => 'W',
p_internal_address_line => NULL,
p_manager_flag => 'Y',
p_normal_hours => 40,
p_perf_review_period => 1,
p_perf_review_period_frequency => 'Y',
p_probation_period => NULL,
p_probation_unit => NULL,
p_sal_review_period => 1,
p_sal_review_period_frequency => 'Y',
p_set_of_books_id => NULL,
p_source_type => NULL,
p_time_normal_finish => NULL,
p_time_normal_start => NULL,
p_bargaining_unit_code => NULL,
p_labour_union_member_flag => NULL,
p_hourly_salaried_code => NULL,
--emp_upload_rec.rate_type,
p_ass_attribute_category => NULL,
p_ass_attribute1 => NULL,
p_ass_attribute2 => NULL,
p_ass_attribute3 => NULL,
p_ass_attribute4 => NULL,
p_ass_attribute5 => NULL,
p_ass_attribute6 => NULL,
p_ass_attribute7 => NULL,
p_ass_attribute8 => NULL,
p_ass_attribute9 => NULL,
p_ass_attribute10 => NULL,
p_ass_attribute11 => NULL,
p_ass_attribute12 => NULL,
p_ass_attribute13 => NULL,
p_ass_attribute14 => NULL,
p_ass_attribute15 => NULL,
p_ass_attribute16 => NULL,
p_ass_attribute17 => NULL,
p_ass_attribute18 => NULL,
p_ass_attribute19 => NULL,
p_ass_attribute20 => NULL,
p_ass_attribute21 => NULL,
p_ass_attribute22 => NULL,
p_ass_attribute23 => NULL,
p_ass_attribute24 => NULL,
p_ass_attribute25 => NULL,
p_ass_attribute26 => NULL,
p_ass_attribute27 => NULL,
p_ass_attribute28 => NULL,
p_ass_attribute29 => NULL,
p_ass_attribute30 => NULL,
p_title => NULL,
p_segment1 => NULL,
p_segment2 => NULL,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment5 => NULL,
p_segment6 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_segment9 => NULL,
p_segment10 => NULL,
p_segment11 => NULL,
p_segment12 => NULL,
p_segment13 => NULL,
p_segment14 => NULL,
p_segment15 => NULL,
p_segment16 => NULL,
p_segment17 => NULL,
p_segment18 => NULL,
p_segment19 => NULL,
p_segment20 => NULL,
p_segment21 => NULL,
p_segment22 => NULL,
p_segment23 => NULL,
p_segment24 => NULL,
p_segment25 => NULL,
p_segment26 => NULL,
p_segment27 => NULL,
p_segment28 => NULL,
p_segment29 => NULL,
p_segment30 => NULL,
p_concat_segments => NULL,
p_contract_id => NULL,
p_establishment_id => NULL,
p_collective_agreement_id => NULL,
p_cagr_id_flex_num => NULL,
p_cag_segment1 => NULL,
p_cag_segment2 => NULL,
p_cag_segment3 => NULL,
p_cag_segment4 => NULL,
p_cag_segment5 => NULL,
p_cag_segment6 => NULL,
p_cag_segment7 => NULL,
p_cag_segment8 => NULL,
p_cag_segment9 => NULL,
p_cag_segment10 => NULL,
p_cag_segment11 => NULL,
p_cag_segment12 => NULL,
p_cag_segment13 => NULL,
p_cag_segment14 => NULL,
p_cag_segment15 => NULL,
p_cag_segment16 => NULL,
p_cag_segment17 => NULL,
p_cag_segment18 => NULL,
p_cag_segment19 => NULL,
p_cag_segment20 => NULL,
p_notice_period => NULL,
p_notice_period_uom => NULL,
p_employee_category => NULL,
p_work_at_home => NULL,
p_job_post_source_name => NULL,
p_supervisor_assignment_id => NULL,
p_cagr_grade_def_id => l_cagr_grade_def_id,
p_cagr_concatenated_segments => l_cagr_concatenated_segments,
p_concatenated_segments => l_concatenated_segments,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_comment_id => l_comment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning,
p_hourly_salaried_warning => l_hourly_salaried_warning,
p_gsp_post_process_warning => l_gsp_post_process_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
l_cagr_grade_def_id NUMBER;
l_cagr_concatenated_segments VARCHAR2 (30);
l_concatenated_segments VARCHAR2 (280);
l_soft_coding_keyflex_id NUMBER;
l_comment_id NUMBER (15);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_no_managers_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_hourly_salaried_warning BOOLEAN;
l_gsp_post_process_warning VARCHAR2 (30);
l_api_mode VARCHAR2 (20);
l_object_version_number NUMBER;
l_assignment_id NUMBER;
BEGIN
l_api_mode := 'CORRECTION';
l_object_version_number := 2;
l_soft_coding_keyflex_id := 91;
hr_assignment_api.update_emp_asg
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => l_api_mode,
p_assignment_id => 27920,
p_object_version_number => l_object_version_number,
--l_object_version_number,
p_assignment_number => NULL,
p_change_reason => 'WS',
p_assignment_status_type_id => 1,
p_comments => NULL,
p_date_probation_end => NULL,
p_default_code_comb_id => NULL,
p_frequency => 'W',
p_internal_address_line => NULL,
p_manager_flag => 'Y',
p_normal_hours => 40,
p_perf_review_period => 1,
p_perf_review_period_frequency => 'Y',
p_probation_period => NULL,
p_probation_unit => NULL,
p_sal_review_period => 1,
p_sal_review_period_frequency => 'Y',
p_set_of_books_id => NULL,
p_source_type => NULL,
p_time_normal_finish => NULL,
p_time_normal_start => NULL,
p_bargaining_unit_code => NULL,
p_labour_union_member_flag => NULL,
p_hourly_salaried_code => NULL,
--emp_upload_rec.rate_type,
p_ass_attribute_category => NULL,
p_ass_attribute1 => NULL,
p_ass_attribute2 => NULL,
p_ass_attribute3 => NULL,
p_ass_attribute4 => NULL,
p_ass_attribute5 => NULL,
p_ass_attribute6 => NULL,
p_ass_attribute7 => NULL,
p_ass_attribute8 => NULL,
p_ass_attribute9 => NULL,
p_ass_attribute10 => NULL,
p_ass_attribute11 => NULL,
p_ass_attribute12 => NULL,
p_ass_attribute13 => NULL,
p_ass_attribute14 => NULL,
p_ass_attribute15 => NULL,
p_ass_attribute16 => NULL,
p_ass_attribute17 => NULL,
p_ass_attribute18 => NULL,
p_ass_attribute19 => NULL,
p_ass_attribute20 => NULL,
p_ass_attribute21 => NULL,
p_ass_attribute22 => NULL,
p_ass_attribute23 => NULL,
p_ass_attribute24 => NULL,
p_ass_attribute25 => NULL,
p_ass_attribute26 => NULL,
p_ass_attribute27 => NULL,
p_ass_attribute28 => NULL,
p_ass_attribute29 => NULL,
p_ass_attribute30 => NULL,
p_title => NULL,
p_segment1 => NULL,
p_segment2 => NULL,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment5 => NULL,
p_segment6 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_segment9 => NULL,
p_segment10 => NULL,
p_segment11 => NULL,
p_segment12 => NULL,
p_segment13 => NULL,
p_segment14 => NULL,
p_segment15 => NULL,
p_segment16 => NULL,
p_segment17 => NULL,
p_segment18 => NULL,
p_segment19 => NULL,
p_segment20 => NULL,
p_segment21 => NULL,
p_segment22 => NULL,
p_segment23 => NULL,
p_segment24 => NULL,
p_segment25 => NULL,
p_segment26 => NULL,
p_segment27 => NULL,
p_segment28 => NULL,
p_segment29 => NULL,
p_segment30 => NULL,
p_concat_segments => NULL,
p_contract_id => NULL,
p_establishment_id => NULL,
p_collective_agreement_id => NULL,
p_cagr_id_flex_num => NULL,
p_cag_segment1 => NULL,
p_cag_segment2 => NULL,
p_cag_segment3 => NULL,
p_cag_segment4 => NULL,
p_cag_segment5 => NULL,
p_cag_segment6 => NULL,
p_cag_segment7 => NULL,
p_cag_segment8 => NULL,
p_cag_segment9 => NULL,
p_cag_segment10 => NULL,
p_cag_segment11 => NULL,
p_cag_segment12 => NULL,
p_cag_segment13 => NULL,
p_cag_segment14 => NULL,
p_cag_segment15 => NULL,
p_cag_segment16 => NULL,
p_cag_segment17 => NULL,
p_cag_segment18 => NULL,
p_cag_segment19 => NULL,
p_cag_segment20 => NULL,
p_notice_period => NULL,
p_notice_period_uom => NULL,
p_employee_category => NULL,
p_work_at_home => NULL,
p_job_post_source_name => NULL,
p_supervisor_assignment_id => NULL,
p_cagr_grade_def_id => l_cagr_grade_def_id,
p_cagr_concatenated_segments => l_cagr_concatenated_segments,
p_concatenated_segments => l_concatenated_segments,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_comment_id => l_comment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning,
p_hourly_salaried_warning => l_hourly_salaried_warning,
p_gsp_post_process_warning => l_gsp_post_process_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
R12 HRMS: Single record insertion for Assignment(Jobs, Position..)
DECLARE
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (500);
l_tax_district_changed_warning BOOLEAN;
l_object_version_number NUMBER;
l_people_group_id NUMBER;
l_special_ceiling_step_id NUMBER;
l_group_name VARCHAR2 (500);
l_effective_start_date DATE;
l_effective_end_date DATE;
BEGIN
--l_people_group_id := 155;
l_object_version_number := 3;
--l_group_name := '1A';
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => 'CORRECTION',
p_assignment_id => 27920,
p_object_version_number => l_object_version_number,
p_job_id => 18,
p_grade_id => 37,
p_position_id => NULL,
p_organization_id => 202,
p_location_id => 206,
p_pay_basis_id => 2,
p_payroll_id => 155,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_people_group_id => l_people_group_id,
--emp_upload_rec.people_group_id,
p_segment1 => NULL,
p_segment2 => NULL,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment5 => NULL,
p_segment6 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_segment9 => NULL,
p_segment10 => NULL,
p_segment11 => NULL,
p_segment12 => NULL,
p_segment13 => NULL,
p_segment14 => NULL,
p_segment15 => NULL,
p_segment16 => NULL,
p_segment17 => NULL,
p_segment18 => NULL,
p_segment19 => NULL,
p_segment20 => NULL,
p_segment21 => NULL,
p_segment22 => NULL,
p_segment23 => NULL,
p_segment24 => NULL,
p_segment25 => NULL,
p_segment26 => NULL,
p_segment27 => NULL,
p_segment28 => NULL,
p_segment29 => NULL,
p_segment30 => NULL,
p_concat_segments => '1A.Boston.No....',
p_employment_category => 'FR',
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (500);
l_tax_district_changed_warning BOOLEAN;
l_object_version_number NUMBER;
l_people_group_id NUMBER;
l_special_ceiling_step_id NUMBER;
l_group_name VARCHAR2 (500);
l_effective_start_date DATE;
l_effective_end_date DATE;
BEGIN
--l_people_group_id := 155;
l_object_version_number := 3;
--l_group_name := '1A';
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => 'CORRECTION',
p_assignment_id => 27920,
p_object_version_number => l_object_version_number,
p_job_id => 18,
p_grade_id => 37,
p_position_id => NULL,
p_organization_id => 202,
p_location_id => 206,
p_pay_basis_id => 2,
p_payroll_id => 155,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_people_group_id => l_people_group_id,
--emp_upload_rec.people_group_id,
p_segment1 => NULL,
p_segment2 => NULL,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment5 => NULL,
p_segment6 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_segment9 => NULL,
p_segment10 => NULL,
p_segment11 => NULL,
p_segment12 => NULL,
p_segment13 => NULL,
p_segment14 => NULL,
p_segment15 => NULL,
p_segment16 => NULL,
p_segment17 => NULL,
p_segment18 => NULL,
p_segment19 => NULL,
p_segment20 => NULL,
p_segment21 => NULL,
p_segment22 => NULL,
p_segment23 => NULL,
p_segment24 => NULL,
p_segment25 => NULL,
p_segment26 => NULL,
p_segment27 => NULL,
p_segment28 => NULL,
p_segment29 => NULL,
p_segment30 => NULL,
p_concat_segments => '1A.Boston.No....',
p_employment_category => 'FR',
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
R12 HRMS: Single record insertion for salary
DECLARE
l_element_entry_id NUMBER := NULL;
l_inv_next_sal_date_warning BOOLEAN;
l_proposed_salary_warning BOOLEAN;
l_approved_warning BOOLEAN;
l_payroll_warning BOOLEAN;
l_perf_object_version_number NUMBER;
l_pay_proposal_id NUMBER (15);
l_assignment_id NUMBER;
l_performance_review_id NUMBER := NULL;
BEGIN
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => l_pay_proposal_id,
p_assignment_id => 27920,
p_business_group_id => 202,
p_change_date => TRUNC (SYSDATE),
p_comments => NULL,
p_next_sal_review_date => TRUNC (SYSDATE + 30),
p_proposal_reason => 'NEWH',
p_proposed_salary_n => 10000,
p_forced_ranking => NULL,
p_performance_review_id => l_performance_review_id,
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_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_object_version_number => l_perf_object_version_number,
p_multiple_components => 'N',
p_approved => 'Y',
p_validate => FALSE,
p_element_entry_id => l_element_entry_id,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
l_element_entry_id NUMBER := NULL;
l_inv_next_sal_date_warning BOOLEAN;
l_proposed_salary_warning BOOLEAN;
l_approved_warning BOOLEAN;
l_payroll_warning BOOLEAN;
l_perf_object_version_number NUMBER;
l_pay_proposal_id NUMBER (15);
l_assignment_id NUMBER;
l_performance_review_id NUMBER := NULL;
BEGIN
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => l_pay_proposal_id,
p_assignment_id => 27920,
p_business_group_id => 202,
p_change_date => TRUNC (SYSDATE),
p_comments => NULL,
p_next_sal_review_date => TRUNC (SYSDATE + 30),
p_proposal_reason => 'NEWH',
p_proposed_salary_n => 10000,
p_forced_ranking => NULL,
p_performance_review_id => l_performance_review_id,
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_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_object_version_number => l_perf_object_version_number,
p_multiple_components => 'N',
p_approved => 'Y',
p_validate => FALSE,
p_element_entry_id => l_element_entry_id,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;
Subscribe to:
Posts (Atom)