Below the sample script could be used for calling that API :
DECLARE
CURSOR c1
IS
SELECT bht.organization_id, bht.batch_no, bht.batch_id,
mdt.material_detail_id,
msi.segment1
|| '-'
|| msi.segment2
|| '-'
|| msi.segment3
|| '-'
|| msi.segment4
|| '-'
|| msi.segment5 item_code,
mdt.plan_qty, mdt.wip_plan_qty, mdt.actual_qty,
bht.actual_start_date,bht.batch_status
FROM gme_material_details mdt,
gme_batch_header bht,
mtl_system_items msi
WHERE bht.batch_status = 2
AND bht.batch_id = mdt.batch_id
AND mdt.plan_qty = 0
AND mdt.wip_plan_qty = 0
AND mdt.actual_qty <> 0
AND mdt.line_type = 1
AND bht.organization_id = mdt.organization_id
AND bht.organization_id = msi.organization_id
AND mdt.inventory_item_id = msi.inventory_item_id
AND bht.actual_start_date BETWEEN TO_DATE ('2014/01/01' || '00:00:00',
'RRRR/MM/DD HH24:MI:SS'
)
AND TO_DATE ('2014/02/28' || '23:59:59',
'RRRR/MM/DD HH24:MI:SS'
)
AND bht.organization_id = 112;
l_batch_header_rec gme_batch_header%ROWTYPE;
l_material_detail_rec gme_material_details%ROWTYPE;
x_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
FOR r1 IN c1
LOOP
fnd_global.apps_initialize (user_id => 1114,
resp_id => 23326,
resp_appl_id => 552
);
l_batch_header_rec.batch_id := r1.batch_id;
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 = r1.material_detail_id;
l_material_detail_rec.material_detail_id := r1.material_detail_id;
l_material_detail_rec.attribute1 := l_formula_orig_line_no;
--l_material_detail_rec.plan_qty := r1.plan_qty;
l_material_detail_rec.wip_plan_qty := r1.actual_qty;
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
);
commit;
dbms_output.put_line(x_return_status);
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;
dbms_output.put_line(x_message_list);
END LOOP;
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(x_return_status);
dbms_output.put_line(x_message_list);
END;
No comments:
Post a Comment