--PR转PO
DECLARECursor Cur Isselect DISTINCT pv.vendor_id vendor_id,Wip_Operation_Seq_Num,Wip_Resource_Seq_Num,PDL.DISTRIBUTION_ID DISTRIBUTION_ID,pr.bom_resource_id,pr.requisition_line_id requisition_line_id,pvs.vendor_site_id vendor_site_id,ppf.person_id person_id,pr.item_id item_id,--pr.unit_price unit_price,t.unit_price unit_price,t.unit_price_tax l_attribute10,t.tax_code tax_code,pr.quantity quantity,pr.item_description item_description,pr.unit_meas_lookup_code unit_meas_lookup_code,pr.deliver_to_location_id deliver_to_location_id,pr.destination_organization_id destination_organization_id,wdj.attribute2 h_attribute3,msib.segment1 l_attribute7,MSIT.DESCRIPTION l_attribute8,we.wip_entity_name po_number,pr.wip_entity_id job_idfrom po_requisition_lines_all pr,PO_REQ_DISTRIBUTIONS_ALL PDL,wip_discrete_jobs wdj,wip_entities we,mtl_system_items_b msib,mtl_system_items_tl msit ,po_vendors pv,cux.po_vendor_temp_tbl t,po_vendor_sites_all pvs,per_people_f ppfwhere pr.creation_date >= to_date('2024-12-20','yyyy-mm-dd')and we.wip_entity_id = pr.wip_entity_idand we.organization_id = wdj.organization_idand msib.inventory_item_id = wdj.primary_item_idand msib.organization_id = wdj.organization_idand we.wip_entity_id = wdj.wip_entity_idand we.organization_id = wdj.organization_idand t.po_num = we.wip_entity_nameand msib.inventory_item_id = msit.inventory_item_idand msib.organization_id = msit.organization_idand msit.language = 'ZHS'and t.vendor_name = pv.vendor_nameand pv.VENDOR_ID = pvs.VENDOR_IDand pvs.vendor_site_code = '材料采购'and ppf.FULL_NAME = t.user_nameAND PR.REQUISITION_LINE_ID = PDL.REQUISITION_LINE_IDand pr.cancel_date is nulland we.wip_entity_name <> 'WO-24B0057'and reqs_in_pool_flag = 'Y';l_iface_rec po.po_headers_interface%ROWTYPE;l_iface_lines_rec po.po_lines_interface%ROWTYPE;l_iface_dis_rec po.po_distributions_interface%ROWTYPE;l_org_id NUMBER := 81; --OU IDl_user_id NUMBER := 1111; --User ID?Sysadminx_Return_Status Varchar2(5);x_Msg_Count Number;x_Msg_Data Varchar2(4000);x_Num_Lines_Processed Number;x_Document_Number Varchar2(4000);x_Po_Header_Id Number;l_ret_status Varchar2(30);BEGINFor Rec In Cur Loopl_iface_rec.org_id := l_org_id;SELECT po_headers_interface_s.NEXTVALINTO l_iface_rec.interface_header_idFROM dual;l_iface_rec.process_code := 'PENDING';l_iface_rec.action := 'ORIGINAL';l_iface_rec.document_type_code := 'STANDARD';l_iface_rec.document_subtype := NULL;-- l_iface_rec.document_num := NULL;-- l_iface_rec.document_subtype := 'STANDARD';--l_iface_rec.Group_Code := 'DEFAULT';-- l_iface_rec.Style_Id := 1;l_iface_rec.document_num := Rec.po_number;l_iface_rec.approval_status := 'APPROVED';l_iface_rec.agent_id := rec.person_id;l_iface_rec.vendor_id := rec.vendor_id;l_iface_rec.vendor_site_id := rec.vendor_site_id;l_iface_rec.interface_source_code := '202501PR转PO';l_iface_rec.currency_code := 'CNY';l_iface_rec.attribute3 := Rec.H_attribute3;l_iface_rec.batch_id := 2025022802;l_iface_rec.Creation_Date := Sysdate;l_iface_rec.Created_By := l_user_id;l_iface_rec.Last_Update_Date := Sysdate;l_iface_rec.Last_Updated_By := l_user_id;INSERT INTO po.po_headers_interface VALUES l_iface_rec;l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;l_iface_lines_rec.process_code := 'PENDING';l_iface_lines_rec.action := 'ORIGINAL';l_iface_lines_rec.line_num := 1;l_iface_lines_rec.line_type_id := 3;l_iface_lines_rec.need_by_date := sysdate ;l_iface_lines_rec.requisition_line_id := rec.requisition_line_id;l_iface_lines_rec.item_id := rec.item_id;l_iface_lines_rec.item := rec.item_description;l_iface_lines_rec.unit_price := rec.unit_price;l_iface_lines_rec.quantity := rec.quantity;l_iface_lines_rec.unit_of_measure := rec.unit_meas_lookup_code;l_iface_lines_rec.ship_to_location_id := rec.deliver_to_location_id;l_iface_lines_rec.line_attribute10 := rec.l_attribute10;l_iface_lines_rec.line_attribute7 := rec.l_attribute7;l_iface_lines_rec.line_attribute8 := rec.l_attribute8;if rec.Tax_Code is not null thenl_Iface_Lines_Rec.Tax_Name := rec.Tax_Code;l_Iface_Lines_Rec.Taxable_Flag := 'Y';end if;l_iface_lines_rec.Creation_Date := Sysdate;l_iface_lines_rec.Created_By := l_user_id;l_iface_lines_rec.Last_Update_Date := Sysdate;l_iface_lines_rec.Last_Updated_By := l_user_id;l_iface_lines_rec.ship_to_organization_id := rec.destination_organization_id;SELECT po_lines_interface_s.NEXTVALINTO l_iface_lines_rec.interface_line_idFROM dual;INSERT INTO po.po_lines_interface VALUES l_iface_lines_rec;SELECT po_distributions_interface_s.NEXTVALINTO l_iface_dis_rec.interface_distribution_idFROM dual;l_iface_dis_rec.interface_header_id := l_iface_rec.interface_header_id;l_iface_dis_rec.interface_line_id := l_iface_lines_rec.interface_line_id;l_iface_dis_rec.distribution_num := 1;l_iface_dis_rec.wip_entity_id := rec.job_id;l_iface_dis_rec.QUANTITY_ORDERED := rec.quantity;l_iface_dis_rec.CHARGE_ACCOUNT_ID :=1164;l_iface_dis_rec.wip_operation_seq_num := 10;l_iface_dis_rec.wip_resource_seq_num := 10;l_iface_dis_rec.Creation_Date := Sysdate;l_iface_dis_rec.Created_By := l_user_id;l_iface_dis_rec.Last_Update_Date := Sysdate;l_iface_dis_rec.Last_Updated_By := l_user_id;l_iface_dis_rec.req_distribution_id :=rec.DISTRIBUTION_ID;l_iface_dis_rec.deliver_to_location_id := rec.deliver_to_location_id;-- l_iface_dis_rec.SOURCE_DISTRIBUTION_ID :=rec.DISTRIBUTION_ID;l_iface_dis_rec.bom_resource_id :=rec.bom_resource_id;l_iface_dis_rec.destination_context := 'SHOP FLOOR';l_iface_dis_rec.destination_type_code := 'SHOP FLOOR';BeginSelect Pha.Segment1,Pla.Line_Num,Pda.Distribution_Id,Pda.Code_Combination_Id,Pda.Accrual_Account_Id,Pda.Variance_Account_Id/*,Pla.Requisition_Line_Id*/Into l_iface_dis_rec.Req_Header_Reference_Num,l_iface_dis_rec.Req_Line_Reference_Num,l_iface_dis_rec.Req_Distribution_Id,l_iface_dis_rec.Charge_Account_Id, --借记帐户l_iface_dis_rec.Accrual_Account_Id,l_iface_dis_rec.Variance_Account_Id-- l_iface_dis_rec.Global_Attribute1From Po_Requisition_Headers_All Pha,Po_Requisition_Lines_All Pla,Po_Req_Distributions_All PdaWhere Pha.Requisition_Header_Id = Pla.Requisition_Header_IdAnd Pla.Requisition_Line_Id = Pda.Requisition_Line_Id-- And Pla.Wip_Entity_Id = Rec_Loca.Wip_Entity_Idand pla.requisition_line_id = rec.requisition_line_idAnd Pla.Wip_Operation_Seq_Num = rec.Wip_Operation_Seq_NumAnd Pla.Wip_Resource_Seq_Num = rec.Wip_Resource_Seq_Num;End;INSERT INTO po.po_distributions_interface VALUES l_iface_dis_rec;Commit;--Should initialize before po_docs_interface_sv5.process_po_headers_interface/*apps.fnd_global.apps_initialize(user_id => l_user_id,resp_id => 50637,resp_appl_id => 201);*/-- Mo_Global.Set_Policy_Context('S', l_org_id);/* Po_Interface_s.Create_Documents(p_Api_Version => 1.0,x_Return_Status => x_Return_Status,x_Msg_Count => x_Msg_Count,x_Msg_Data => x_Msg_Data,p_Batch_Id => l_iface_rec.interface_header_id,p_Req_Operating_Unit_Id => l_org_id,p_Purch_Operating_Unit_Id => l_org_id,x_Document_Id => x_Po_Header_Id,x_Number_Lines => x_Num_Lines_Processed,x_Document_Number => x_Document_Number,p_Document_Creation_Method => 'AUTOCREATE', -- <DBI FPJ>p_Sourcing_k_Doc_Type => Null,p_Conterms_Exist_Flag => Null,p_Orig_Org_Id => Null);*/-- dbms_output.put_line(x_Po_Header_Id || x_Return_Status || x_Msg_Count || x_Msg_Data);/*po_docs_interface_sv5.process_po_headers_interface(x_selected_batch_id => l_iface_rec.batch_id,x_buyer_id => NULL,x_document_type => l_iface_rec.document_type_code,x_document_subtype => l_iface_rec.document_subtype,x_create_items => 'N',x_create_sourcing_rules_flag => NULL,x_rel_gen_method => NULL,x_approved_status => l_iface_rec.approval_status,x_commit_interval => 1,x_process_code => 'PENDING',x_interface_header_id => NULL,x_org_id_param => NULL,x_ga_flag => NULL);*//*po_pdoi_grp.start_process(p_api_version => 1.0,p_init_msg_list => fnd_api.g_true,p_validation_level => fnd_api.g_valid_level_full,p_commit => fnd_api.g_false,x_return_status => l_ret_status,p_gather_intf_tbl_stat => fnd_api.g_false,p_calling_module => po_pdoi_constants.g_call_mod_concurrent_prgm,p_selected_batch_id => NULL, --lv_batch_id, --p_file_id, ?????????????p_batch_size => po_pdoi_constants.g_def_batch_size,p_buyer_id => NULL,p_document_type => 'STANDARD', --'BLANKET', --'STANDARD',p_document_subtype => NULL,p_create_items => 'N',p_create_sourcing_rules_flag => 'N',p_rel_gen_method => NULL,p_sourcing_level => NULL,p_sourcing_inv_org_id => NULL,p_approved_status => 'INCOMPLETE',p_process_code => po_pdoi_constants.g_process_code_pending,p_interface_header_id => l_iface_rec.interface_header_id, --ln_interface_header_id,p_org_id => l_org_id,p_ga_flag => NULL);*/Commit;End Loop;
END;
update po_requisition_lines_all prl set prl.reqs_in_pool_flag = 'N',PRL.LINE_LOCATION_ID = (SELECT C.LINE_LOCATION_ID FROM cux_2025022202 C WHERE C.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID),PRL.suggested_buyer_id = (SELECT C.agent_id FROM cux_2025022202 C WHERE C.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID)
WHERE PRL.REQUISITION_LINE_ID IN ( select REQUISITION_LINE_ID from cux_2025022202)pr转po后，由于pr还可以继续转po,需要修改pr行标识