R12 – OPM Operations Creation using API
CURSOR c_operation IS
SELECT DISTINCT oprn_no
, oprn_desc
, process_qty_uom
, oprn_vers
, delete_mark
, effective_start_date
, operation_status
, owner_organization_id
, activity
, offset_interval
, activity_factor
FROM mii_gmd_operations
WHERE flag IS NULL;
CURSOR c_resource (
p_oprn_no varchar2,
p_activity varchar2
) IS
SELECT resources,
process_qty,
resource_process_uom,
resource_usage,
resource_usage_uom,
cost_cmpntcls_id,
cost_analysis_code,
prim_rsrc_ind,
resource_count,
scale_type,
offset_interval
FROM mii_gmd_operations
WHERE oprn_no = p_oprn_no
AND activity = p_activity
ORDER BY PRIM_RSRC_IND;
l_operations gmd_operations%ROWTYPE;
l_oprn_actv_tbl gmd_operations_pub.gmd_oprn_activities_tbl_type;
l_oprn_rsrc_tbl gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
l_count NUMBER := 0;
l_count1 NUMBER := 0;
l_loop_cnt NUMBER := 0;
l_record_count NUMBER := 0;
l_data VARCHAR2 (2000);
l_data1 VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_return_status1 VARCHAR2 (1);
l_status VARCHAR2 (1);
i NUMBER := 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER := 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_oprn_line_id number;
v_count number;
v_resources varchar2(100);
v_process_qty number;
v_resource_process_uom varchar2(5);
v_resource_usage number;
v_resource_usage_uom varchar2(5);
v_cost_cmpntcls_id number;
v_cost_analysis_code varchar2(5);
v_prim_rsrc_ind number;
v_resource_count number;
v_scale_type number;
v_offset_interval number;
BEGIN
fnd_global.apps_initialize (user_id => 1090,
resp_id => 22882,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR l_oprn IN c_operation
LOOP
DBMS_OUTPUT.put_line (‘Value Test ‘ || l_operations.oprn_no);
l_operations.oprn_no := l_oprn.oprn_no;
l_operations.oprn_desc := l_oprn.oprn_desc;
l_operations.process_qty_uom := l_oprn.process_qty_uom;
l_operations.oprn_vers := l_oprn.oprn_vers;
l_operations.delete_mark := l_oprn.delete_mark;
l_operations.effective_start_date := l_oprn.effective_start_date;
l_operations.operation_status := l_oprn.operation_status;
l_operations.owner_organization_id := l_oprn.owner_organization_id;
l_oprn_actv_tbl (1).activity := l_oprn.activity;
l_oprn_actv_tbl (1).offset_interval := l_oprn.offset_interval;
l_oprn_actv_tbl (1).activity_factor := l_oprn.activity_factor;
l_oprn_actv_tbl (1).delete_mark := l_oprn.delete_mark;
–l_oprn_actv_tbl (1).activity := l_master.activity;
SELECT COUNT(*)
INTO v_count
FROM mii_gmd_operations
WHERE oprn_no = l_oprn.oprn_no
AND activity = l_oprn.activity
ORDER BY PRIM_RSRC_IND;
OPEN c_resource (l_oprn.oprn_no, l_oprn.activity);
FOR i IN 1 .. v_count
LOOP
FETCH c_resource INTO v_resources,
v_process_qty,
v_resource_process_uom,
v_resource_usage,
v_resource_usage_uom,
v_cost_cmpntcls_id,
v_cost_analysis_code,
v_prim_rsrc_ind,
v_resource_count,
v_scale_type,
v_offset_interval;
l_oprn_rsrc_tbl (i).activity := l_oprn.activity;
l_oprn_rsrc_tbl (i).resources := v_resources;
l_oprn_rsrc_tbl (i).process_qty := v_process_qty;
l_oprn_rsrc_tbl (i).resource_process_uom := v_resource_process_uom;
l_oprn_rsrc_tbl (i).resource_usage := v_resource_usage;
l_oprn_rsrc_tbl (i).resource_usage_uom := v_resource_usage_uom;
l_oprn_rsrc_tbl (i).cost_cmpntcls_id := v_cost_cmpntcls_id;
l_oprn_rsrc_tbl (i).cost_analysis_code := v_cost_analysis_code;
l_oprn_rsrc_tbl (i).prim_rsrc_ind := v_prim_rsrc_ind;
l_oprn_rsrc_tbl (i).resource_count := v_resource_count;
l_oprn_rsrc_tbl (i).scale_type := v_scale_type;
l_oprn_rsrc_tbl (i).offset_interval := v_offset_interval;
UPDATE mii_gmd_operations
set flag = ‘Y’
WHERE oprn_no = l_oprn.oprn_no
AND resources = v_resources;
END LOOP;
CLOSE c_resource;
gmd_operations_pub.insert_operation (p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_operations => l_operations,
p_oprn_actv_tbl => l_oprn_actv_tbl,
p_oprn_rsrc_tbl => l_oprn_rsrc_tbl,
x_message_count => l_count,
x_return_status => l_return_status,
x_message_list => l_data
);
UPDATE gmd_operations_b
set operation_status = 700
WHERE operation_status = 100
AND oprn_no = l_oprn.oprn_no;
IF l_status = ‘E’ OR l_status = ‘U’
THEN
UPDATE mii_gmd_operations
set flag = ‘E’
, note = l_data
WHERE oprn_no = l_oprn.oprn_no;
END IF;
END LOOP;
IF l_count >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);
IF l_status = ‘E’ OR l_status = ‘U’
THEN
l_data := CONCAT (‘ERROR ‘, l_data);
END IF;
DBMS_OUTPUT.put_line (l_data);
IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;
l_loop_cnt := l_loop_cnt + 1;
IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line (‘Sucess’);
END;
/
How to use Update API?