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; CURSOR c1 IS SELECT msa.inventory_item_id, msa.assignment_set_id, mas.assignment_set_name, msa.entity_name item, msa.description item_desc, msa.organization_id, msa.sourcing_rule_id, msa.sourcing_rule_type, msa.sourcing_rule_name, msa.assignment_type, msa.assignment_id FROM mrp_assignment_sets mas, mrp_sr_assignments_v msa WHERE mas.assignment_set_id = msa.assignment_set_id AND mas.assignment_set_name = '<Assignment Set Name>'
AND msa.inventory_item_id = <Inventory Item Id>; BEGIN FOR i IN c1 LOOP fnd_message.clear; L_assignment_tbl (1).assignment_set_id := i.assignment_set_id; L_assignment_tbl (1).assignment_type := i.assignment_type; --Item-Organization (SELECT lookup_code, MEANING FROM mfg_lookups WHERE lookup_type = 'MRP_ASSIGNMENT_TYPE') L_assignment_tbl (1).operation := 'DELETE'; --'CREATE'; L_assignment_tbl (1).organization_id := i.organization_id; L_assignment_tbl (1).inventory_item_id := i.inventory_item_id; L_assignment_tbl (1).sourcing_rule_id := i.sourcing_rule_id; --N1-SHIV ENGINEERING PRODUCTS L_assignment_tbl (1).sourcing_rule_type := i.sourcing_rule_type; --Sourcing Rule L_assignment_tbl (1).assignment_id := i.assignment_id; 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 LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.Put_line ('Error :' ||SQLERRM); END;
PROCEDURE Submit_po_appr_proc1(p_po_header_id NUMBER,
po_release_id NUMBER)
AS
v_item_key VARCHAR2(100);
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_po_release_id NUMBER := po_release_id;
po_status VARCHAR2(100);
po_msg VARCHAR2(2000);
ln_request_id NUMBER;
CURSOR c_po_details IS
SELECT DISTINCT PHA.org_id,
pdt.document_subtype,
pdt.document_type_code,
PHA.created_by,
pha.authorization_status,
pha.agent_id,
pha.segment1,
pha.po_header_id
FROM apps.po_headers_all PHA,
apps.po_releases_all PRA,
apps.po_document_types_all PDT
WHERE 1 = 1
AND CASE
WHEN PHA.type_lookup_code = 'PLANNED'
AND v_po_release_id IS NOT NULL THEN 'SCHEDULED'
ELSE PHA.type_lookup_code
END = pdt.document_subtype
AND PHA.org_id = PDT.org_id
AND PHA.po_header_id = PRA.po_header_id(+)
AND PDT.document_type_code = CASE WHEN pha.type_lookup_code = 'PLANNED' AND v_po_release_id IS NULL THEN 'PO'
WHEN PHA.type_lookup_code = 'PLANNED' AND v_po_release_id IS NOT NULL THEN 'RELEASE'
WHEN PHA.type_lookup_code = 'STANDARD' THEN 'PO'
WHEN PHA.type_lookup_code = 'BLANKET' AND v_po_release_id IS NOT NULL THEN 'RELEASE'
WHEN PHA.type_lookup_code = 'BLANKET' AND v_po_release_id IS NULL THEN 'PA'
END
AND PHA.po_header_id = p_po_header_id
AND ( PRA.po_release_id = v_po_release_id OR v_po_release_id IS NULL );
BEGIN
FOR p_rec IN c_po_details LOOP
SELECT fresp.responsibility_id,
fresp.application_id
INTO v_resp_id, v_resp_appl_id
FROM fnd_responsibility_tl fresp
WHERE fresp.responsibility_name LIKE '<RESPONSIBILITY_NAME>'
AND LANGUAGE = Userenv('LANG');
apps.mo_global.Init ('PO');
fnd_global.Apps_initialize (user_id => p_rec.created_by, resp_id => v_resp_id, resp_appl_id => v_resp_appl_id);
mo_global.Set_policy_context ('S', p_rec.org_id);
SELECT p_rec.po_header_id
||'-'
||To_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM dual;
dbms_output.Put_line ( ' Calling po_reqapproval_init1.start_wf_process for po_id=>' || p_rec.segment1);
po_reqapproval_init1.Start_wf_process(
itemtype => 'POAPPRV',
itemkey => v_item_key,
workflowprocess => 'POAPPRV_TOP',
actionoriginatedfrom => 'PO_FORM',
documentid => Nvl(v_po_release_id, p_po_header_id),
documentnumber => p_rec.segment1, -- Purchase Order Number
preparerid => p_rec.agent_id, -- Buyer/Preparer_id
documenttypecode => p_rec.document_type_code,--'PO'
documentsubtype => p_rec.document_subtype, --'STANDARD'
submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => NULL,
note => NULL,
printflag => 'N',
faxflag => 'N',
faxnumber => NULL,
emailflag => 'N',
emailaddress => NULL,
createsourcingrule => 'N',
releasegenmethod => 'N',
updatesourcingrule => 'N',
massupdatereleases => 'N',
retroactivepricechange => 'N',
orgassignchange => 'N',
communicatepricechange => 'N',
p_background_flag => 'N',
p_initiator => NULL,
p_xml_flag => NULL,
fpdsngflag => 'N',
p_source_type_code => NULL);
COMMIT;
dbms_output.Put_line ('The PO which is Approved Now =>' || p_rec.segment1);
END LOOP;
fnd_file.Put_line(fnd_file.log, 'Status:' ||po_status);
fnd_file.Put_line(fnd_file.log, 'Message:' ||po_msg);
EXCEPTION
WHEN OTHERS THEN
po_status := 'E';
po_msg := 'Error' ||' '|| SQLERRM;
fnd_file.Put_line(fnd_file.log, 'Status:' ||po_status);
fnd_file.Put_line(fnd_file.log, 'Message:' ||po_msg);
END submit_po_appr_proc;
