Oracle OPM: Updating Batch Material Line based on the source Formula Line

I have seen quite a few times that we have a requirement of copying the DFF attributes on the Formula Line to the Material Line when the Batch containing this Material Line gets created.

One way is to create a trigger on the base table of Material Line and implement the necessary logic on ON-INSERT trigger. I personally hate triggers.

One neater way of doing it is to subscribe to Batch Created (oracle.apps.gme.batch.created).

Steps to create the Subscription for the event and execute the
DFF
copying logic thru a plsql function
Ø Go to the Workflow Administrator Webapplications -> Administrator Workflow.
Ø Open the Business Events page.
Ø Search for business event “oracle.apps.gme.batch.created”.
Ø Click on the subscription button. It displays the subscriptions page.
Ø Click on the Create Subscription button.
Ø Specify the following values for the fields in the page.
Subscriber Section
System:
Select

Triggering Event Section
Leave the Section as it is.

Execution Condition Section
Phase: 80
Status: Enabled.
Rule Data: Key

Action type Section
Action type: Custom
On Error: Skip to Next

Ø Click on Next Button:
Action Section
PL/SQL Rule Function: XX_UPDATE_MTL_LINE_DFF_PKG.update_mtl_line_dff_prc Priority: Normal

Documentation Section
Owner Name: Custom Application.
Documentation: XX.

Click on Apply Button.

The custom plsql procedure to copy the DFF will leverage the std Oracle Public API
GME_API_PUB.update_material_line

The sample plsql code below explains how the plsql procedure is hooked on with the plsql procedure via the subscription to the Business Event.

FUNCTION update_mtl_line_dff_prc( p_subscription_guid_in IN RAW
,p_event_inout IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2
IS

TYPE l_tbl_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
l_material_detail_id_tbl l_tbl_num_type;

l_batch_header_rec gme_batch_header%ROWTYPE;
l_material_detail_rec gme_material_details%ROWTYPE;
x_material_detail_rec gme_material_details%ROWTYPE;
temp_material_detail_rec gme_material_details%ROWTYPE;

l_formula_orig_line_no FM_MATL_DTL.ATTRIBUTE1%TYPE;
l_batch_id gme_batch_header.batch_id%TYPE;

l_event_name_txt VARCHAR2 (2000);
l_event_key_num VARCHAR2 (2000);

x_message_count NUMBER := 0;
x_message_list VARCHAR2(4000) := NULL;
x_return_status VARCHAR2(1) := 'U';
l_msg_index_out NUMBER := 0;

MTL_LINE_EXC EXCEPTION;
BEGIN
l_event_name_txt := p_event_inout.geteventname ();
l_event_key_num := p_event_inout.geteventkey ();

l_batch_id := to_number(l_event_key_num);

SELECT *
INTO l_batch_header_rec
FROM gme_batch_header
WHERE batch_id = l_batch_id;

SELECT material_detail_id
BULK COLLECT INTO l_material_detail_id_tbl
FROM gme_material_details
WHERE batch_id = l_batch_id
AND LINE_TYPE = -1;

FOR I IN 1..l_material_detail_id_tbl.COUNT LOOP
l_material_detail_rec := temp_material_detail_rec;

SELECT FMD.ATTRIBUTE1
INTO l_formula_orig_line_no
FROM FM_MATL_DTL FMD
, GME_MATERIAL_DETAILS GMD
WHERE FMD.FORMULALINE_ID = GMD.FORMULALINE_ID
AND GMD.MATERIAL_DETAIL_ID = l_material_detail_id_tbl(I);


l_material_detail_rec.material_detail_id := l_material_detail_id_tbl(I);
l_material_detail_rec.attribute1 := l_formula_orig_line_no;

GME_API_PUB.update_material_line( p_batch_header_rec => l_batch_header_rec
,p_material_detail_rec => l_material_detail_rec
,p_locator_code => NULL
,p_org_code => NULL
,x_material_detail_rec => x_material_detail_rec
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => x_return_status
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validate_flexfields => fnd_api.g_false);

IF x_return_status = FND_API.g_ret_sts_success THEN
COMMIT;
ELSE
IF x_message_count = 1 THEN

RAISE MTL_LINE_EXC;
ELSE
FOR j IN 1..x_message_count LOOP
FND_MSG_PUB.get( p_msg_index => j
,p_encoded => 'F'
,p_data => x_message_list
,p_msg_index_out => l_msg_index_out);

RAISE MTL_LINE_EXC;
END LOOP;
END IF;
END IF;
END LOOP;
RETURN 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
RETURN 'FAILURE';
RAISE;
END update_mtl_line_dff_prc;

No comments:

Post a Comment

There was an error in this gadget