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;
/