Oracle EBS Enterprise Tax Reform Scheme (iii)-open purchase documents and some AP invoices tax rate adjustment ____oracle

Prerequisites: Oracle EBS Enterprise Tax Reform Scheme (i)-business needs collation and foundation Setup
Oracle EBS System version: 11.5.10
Open purchase orders are more cumbersome to handle than open sales, because the underlying table schema and logic for purchase orders, such as purchase receipt, will not be split. In particular, many companies to PR, PBA, quotation or standard PO, etc. than the depth of customization, dealing with more difficult. For example: Our company's purchase orders are customized as follows, the line added tax code and include tax price .

In addition, the automatic creation of purchase orders is also customized, fortunately, the more comprehensive consideration at that time, the impact of this tax change is not particularly too, the risk is manageable. First, Supplier Site-tax modify the Supplier site level tax. Of course, depending on the default tax priority level set by different companies, you can also leave this step out of the action. Ap->supplies->entry->suppliers->suppliersites->invoice Tax

The batch API modifies the vendor location level tax, I do not have the fine Aberdeen to study. A friend of mine told me they are the tax code that updates the table directly as follows:

Update po_vendor_sites_all
   Set vat_code = ' VAT16 '
 Where vendor_site_id = 924;

I also take a step-by-step test from the pr->po->receiving->vmi/consignment->create consumption advice->create AP invoices and so on, as if nothing had been discovered. However, I am allowing users to go to a vendor manually update:)

Ii. Open purchase requisition form
Open purchase requisitions refer to all applications that do not have a purchase order created, as follows

However, I used the "purchase requisition approved but not created purchase order" to use the Autocreate,po the tax code that runs is 16% tax code (VAT16) and seemingly does not need to revise the tax rate. Want to have friends to help verify that it is not possible before our system customized modify the standard library file or package.

Third, create vmi/consignment supplier AP invoices, Note: This step is possible to set different default tax code priority level, it is possible to advance operations (including manual AP invoices matching), as far as possible to create AP invoices before the tax code change .
3.1 Create consumption Advice (creating a blanking notice): inv->reports->transactions->create consumption Advice: Submit directly without entering any conditions.
3.2 Pay on Receipt autoinvoice:ap->view->request->pay on Receipt autoinvoice Four, open purchase orders

Open purchase orders refer to all purchase orders that do not have the create AP invoices. The situation is as follows

Pay special attention to: all vmi/Consignment mode cooperation suppliers, open purchase orders can be changed without processing, directly modify the PBA corresponding line of purchase unit price and tax code priority level settings. As we have a part of the vmi/consignment settlement is at the time of the standard PO price settlement, you can refer to: Oracle EBS VMI to take the standard PO unit price, also recommended that the best not to deal with, customized report summary tax rates caused by the difference, directly in the AP invoices adjustment.

Open purchase orders can also be processed in batches with the API. However, because the resource problem has not been written for the time being, some friends can share it. However, the unit price for the newly created order line must be the same as the unit price of the original line. In particular, the price fluctuation of relatively large materials, to avoid the company to bring losses.

V. Quotations and BPA modify tax codes and unit prices:
po->rfq ' Sand quotations-> Quotations: Modify Quotations Unit Price
po->purchaseorders-> Purchase Orders: Revise the BPA rate and the tax-included unit price

Before doing a project, daily according to the color gold heavy futures price, the cartoon box Square unit price and the PCB Board Square Unit price automatically calculates each raw material's unit price, on this basis modifies pl/sql to use the API batch to modify quotations and PBA's unit price, the code is as follows:

Declare L_iface_rec PO.
  Po_headers_interface%rowtype;

  L_iface_lines_rec Po.po_lines_interface%rowtype; V_BATCH_ID number: = 180417001;
  --to_number (To_char (To_char (sysdate, ' Mmddhh24miss ')); --Organization Identification l_orgid Number: = 114; --ou ID l_oldtaxname Varchar2: = ' VAT17 '; --17% Tax Rate Code l_newtaxname VARCHAR2 (200); --16% Rate Code L_NEWTAXID number; --16% Tax rate ID l_newtaxrate number; --16% Rate--quotation & PBA order header Note: There is no price segment Cursor CrH is Select PHA. po_header_id, PHA. vendor_id, PHA. vendor_site_id, PHA. SEGMENT1 Po_number, PHA. Type_lookup_code, PHA. Authorization_status, PHA. Status_lookup_code, PHA. agent_id, PHA. Start_date from Po_headers_all pha Where pha.org_id = 114--and pha. SEGMENT1 = ' 211000024137 ' and PHA. Type_lookup_code in (' quotation ', ' BLANKET ')--quotation represents a quote, BLANKET represents the PBA package purchase agreement and NVL (PHA. Start_date, Trunc (sysdate)) <= Trunc (sysdate)--Valid start date and NVL (PHA. End_date, Trunc (sysdate)) >= Trunc (sysdate)--effective result date and NVL (PHA. Closed_code, ' n ') = ' n '--whether to close and (PHA. Status_lookup_code = ' A ' or (PHA. Authorization_status = ' APPROVED ' and NVL (PHA.
  Closed_code, ' OPEN ')!= ' CLOSED '); --Find the tax rate for quotation & PBA line 17% Cursor CrL (p_poheader_id number) is Select PLA. po_line_id, PLA. Line_num, MSI. SEGMENT1 Item_Number, MSI. DESCRIPTION item_description, To_number (PLA. ATTRIBUTE1) Tax_unit_price,--including the tax unit price PLA. Unit_price,--excluding tax unit price TAX.
           Tax_rate--17% tax rate from Po_headers_all PHA, Po_lines_all PLA, Mtl_system_items_b MSI, Ap_tax_codes_all TAX Where PHA. po_header_id = PLA. po_header_id and 122 = msi.organization_id and PLA. item_id = MSI. INVENTORY_ITEM_ID and PLA. tax_code_id = TAX. TAX_ID (+) and (PHA. Type_lookup_code = ' quotation ' Or (PHA. Type_lookup_code = ' BLANKET ' and tax.name = L_oldtaxname) and PHA. po_header_id = p_poheader_id and PLA. Line_num in (1, 9, one) and NVL (PLA. Cancel_flag, ' n ') = ' n ' and NVL (PLA. Closed_code, ' OPEN ')!= ' FINALLY CLOSED ' ORDER by PLA.
  Line_num; -Update PBA's tax-included unit and tax code Cursor the CrR is Select PHI by updating the successful interface table records. Document_num, PHI. Document_type_code, PLI. Line_num, PLI. Process_code, PLI. po_line_id, PLI. Line_attribute1, PLI. Unit_price from PO. Po_headers_interface Phi, Po_lines_interface PLI where Phi. interface_header_id = PLI. INTERFACE_HEADER_ID and PHI. batch_id = v_batch_id and PHI. Document_type_code = ' BLANKET ' and PHI. Process_code = ' accepted ' ORDER by PHI. Document_num, PLI.

  Line_num;  
  V_price number;
  V_taxprice number;
  L_userid number: = 1110;
  L_respid number: = 50244;
L_respapplid number: = 201; Begin--Initialize Fnd_global.apps_initIalize (user_id => L_userid, resp_id =>, L_respid,
  resp_appl_id => l_respapplid); --Deletes the interface table data Begin Delete from Po_lines_interface Where interface_header_id in (Select interface_header _id from PO.
    Po_headers_interface Where batch_id = v_batch_id); Delete from PO.
    Po_headers_interface Where batch_id = v_batch_id;
  Commit;
  Exception when others Then Null;

  End;
    For RsH in CrH Loop--start Header l_iface_rec.org_id: = L_orgid;
    SELECT Po_headers_interface_s.nextval into l_iface_rec.interface_header_id from dual;
    L_iface_rec.process_code: = ' PENDING ';
    L_iface_rec.action: = ' UPDATE ';
    L_iface_rec.document_type_code: = Rsh.type_lookup_code;
    L_iface_rec.document_subtype: = NULL;
    L_iface_rec.document_num: = Rsh.po_number; L_iface_rec.approval_status: = ' ApproVED ';
    l_iface_rec.agent_id: = rsh.agent_id;
    l_iface_rec.vendor_id: = rsh.vendor_id;
    l_iface_rec.vendor_site_id: = rsh.vendor_site_id;
    L_iface_rec.interface_source_code: = ' 17to16 ';
    l_iface_rec.batch_id: = v_batch_id;
    INSERT into Po.po_headers_interface VALUES L_iface_rec; --Get Supplier Place level tax rate Begin Select PSV. Vat_code, TAX. Tax_rate, TAX. tax_id into L_newtaxname, L_newtaxrate, L_newtaxid from Ap_vendor_sites_v PSV, Ap_tax_codes TAX whe Re PSV.
    Vat_code = Tax.name (+) and psv.vendor_site_id = rsh.vendor_site_id;
    Exception when No_data_found Then l_newtaxrate: = 0;
    End; --Supplier location level rate not set, default to take the tax rate of the financial coping system If (NVL (l_newtaxrate, 0) = 0) Then Begin Select FIN. Vat_code, TAX. Tax_rate, TAX.  tax_id into L_newtaxname, L_newtaxrate, L_newtaxid from Financials_system_parameters FIN, ap_tax_codes TAX Where FIN.
  Vat_code = Tax.name (+)         and fin.org_id = L_orgid;
      Exception when No_data_found Then l_newtaxrate: = Null;
    End;
    End If;
    L_newtaxrate: = NVL (l_newtaxrate, 0);
    Dbms_output.put_line (' l_newtaxname= ' | | | | l_newtaxname);
      For RsL in CrL (rsh.po_header_id) Loop 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: = ' UPDATE ';
      l_iface_lines_rec.po_line_id: = rsl.po_line_id;
    
      L_iface_lines_rec.line_num: = Rsl.line_num;
                                        If (rsh.type_lookup_code = ' quotation ') Then--Update price for quotation lines V_price: = Rsl.unit_price * (1/(1 + rsl.tax_rate/100));  --Calculate the tax-free Unit price V_taxprice: = V_taxprice * (1 + l_newtaxrate/ 100);
   --Calculate the tax-containing unit price l_iface_lines_rec.unit_price: = Round (V_taxprice, 5);     --dbms_output.put_line (L_iface_lines_rec.line_num | | ': ' | | l_iface_lines_rec.unit_price); Else--Update the unit price of the purchase agreement line--Calculate the tax-free unit price V_taxprice according to the tax-included unit Price: = Rsl.tax_unit_price; -tax-included unit price v_price: = Round (Rsl.tax_unit_price/(1 + l_newtaxrate/100), 5); --Calculate the/*--unit price v_price: = Rsl.unit_price According to the excluding tax unit price; --excluding tax Unit price V_taxprice: = Round (V_price * (1 + l_newtaxrate/100), 5);
        --to calculate the tax-containing unit price/--Update the tax-containing unit price l_iface_lines_rec.line_attribute1: = V_taxprice;
        --Update the new non-containing tax unit price l_iface_lines_rec.unit_price: = V_price;
      --dbms_output.put_line (L_iface_lines_rec.line_num | | ': ' | | l_iface_lines_rec.line_attribute1);
      End If;
      SELECT Po_lines_interface_s.nextval into l_iface_lines_rec.interface_line_id from dual;
    INSERT into Po_lines_interface VALUES L_iface_lines_rec; End Loop; --end line Begin 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); --update PBA included tax unit price and tax code for RsR in CrR loop update po_lines_all Set attribute1 = rsr.line_attribute1,--p  BA tax-included unit price tax_code_id = l_newtaxid, Tax_name = l_newtaxname-tax code where po_line_id = RSR.
      po_line_id;
      End Loop;
         --Deletes the temporary interface record for the update success Delete from Po_lines_interface Where interface_header_id = l_iface_rec.interface_header_id
      and Process_code = ' accepted '; Delete from PO. Po_headers_interface Where interface_header_id in (Select interface_header_id from Po_ Lines_interface Where interface_header_id = l_iface_rec.interface_header_id and Process_co
   DE = ' accepted ');   Commit;
        Exception when others Then Rollback; Dbms_output.put_line (Rsh.type_lookup_code | | ' Type Order ' | | Rsh.po_number | |
    "Tax rate: Failure");
  End; End Loop; --end Header end;

After the execution is complete, check for the following code that has not been updated successfully, and after the operation is completed, delete the data in the related interface table

Select PHI. Document_num,
       PHI. Document_type_code,
       PLI. Line_num,
       PLI. Process_code,
       PLI. po_line_id,
       PLI. Line_attribute1,
       PLI. Unit_price from
  PO. Po_headers_interface Phi, Po_lines_interface PLI
 where Phi. interface_header_id = PLI. interface_header_id and
   PHI. batch_id = &p_batch_id and
   PHI. Document_type_code = ' BLANKET ' and
   PLI. Process_code!= ' accepted ' ORDER by
 PHI. Document_num, PLI. Line_num;

Note: Our quotations price is included in the tax price.

Vi. AP Invoices
When reconciliation with suppliers, it is unavoidable that PO receives or vmi/consignment consumption needs adjustment tax rate, demand financial manual operation.