Saturday, February 22, 2020

Oracle EBS R12 Procure to Pay Process




It is a Seven steps Procure to Pay process which will help you:
  • To Overview Procure to Pay complete process step by step.
  • To describe the Procure to Pay flow from Requisition creation to make payment and transfer to General Ledger with each steps and examples.
  • To provide reference material and definitions with snapshots.
Graphical flow diagram with steps:


Step-1 Create Requisition

What is Requisition? Requisitions represent demand for goods or services.
The Purchase Requisition will be raised whenever an Item needs to be ordered from the Vendor.
Navigation: Purchasing Responsibility > Requisitions > Requisitions

Click on this and below Requisition form will open.

In Requisitions form enter following information:
  • Select Operating Unit for the destination organization.
  • Select Type as Purchase Requisition.
  • Enter the description, if any for this requisition.
  • To select the item to be placed on requisition, click the Catalog button by keeping the cursor in the lines region.
  • Enter other required information as shown

Requisition Approval

After saving the requisition, click Approve button. This will send the requisition for approval as per Red highlighted.

After selecting the Approve button in Requisitions window, approve documents window opens.

Select the Submit for Approval checkbox to send the requisition for approval as per Red highlighted.

Requisition have been created, now next step is creating Purchase Order.

Step-2 Creating Purchase Order

Types of Purchase orders:

Oracle Purchasing supports four types of purchase orders
1.Standard PO: A Standard PO is created for one–time purchase of various items.
2.Planned PO: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
3.Blanket Agreement: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.
4.Contract Agreement: Contract purchase agreements are created with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing.
Auto Create Purchase Order
There are several methods that can be used to create purchase orders. You can manually create purchase orders or search approved requisitions and add them to purchase orders. Here is Auto create purchase order.
Navigation: Purchasing responsibility à Auto create
All purchase requisitions will appear in the Auto create window of the buyer.



In Find Requisition Lines window Operating Unit, Buyer and Approved status appears as yes.

Buyer can search the requisition lines that are to be placed on Purchase Order based on various criteria.

After searching the required requisition i.e. after selecting the Find button on Find Requisition Lines window, Auto create Documents window opens.


With the help of Auto create Documents window, you can create following documents:

Standard Purchase Order
Blanket Release
Sourcing RFQ.

Automatic: For automatic creation mode use the checkboxes to select the requisition lines that you want to use on the document and then select automatic button. You can also use Select All or Deselect All in Edit Menu.
Manual: For manual creation mode select manual button. It is available only when you have chosen Default grouping for Standard Purchase Order, blanket releases and RFQs.
  • If you select manual creation mode, supplier is not defaulted. In case of automatic creation supplier is defaulted from requisition.

In New document window, Most of the fields defaults, you can enter global agreement number if you want to refer to any global agreement.

Once you click on Create button all requisition information is transferred to Purchase Order Form.

Check all required details in:
  • Lines Tab
  • Price Reference
  • Reference doc
  • More tabs
Sent to Supplier
  • Now PO being sent to Supplier , we can expect the products to be delivered to us. Once items / products are delivered to us we will create Receipt to record the details of the products received.
  • We can create Receipt in purchasing module or inventory module.

Step-3 Inventory Receipt

Receiving?
Using Oracle Purchasing, you can process receipts from suppliers, receipts from other warehouses or inventory organizations, in-transit shipments and receipts due to customer returns. You can search for expected receipts based on a purchase order


Items Receiving
Navigation: Purchasing Responsibility > Receiving > Receipts

Receiving Items and Check Quantity in Inventory
By entering Purchase Order Number

Enable Quantity Receive
After saving click on Header
From Purchasing Module Submit Program for Pay on Receipt Auto invoice


Make a note of Invoice Number

Step- 4 Create AP Invoice

Navigation: Invoices> Entry> Invoices
Invoice number can be Query or find from find Window.
Enter Invoice Number:
Validate Invoice

Click Action Button and Enable Validate

Now Status of Invoice is validated

The Purchase Order is Automatically Match to Invoice and PO Number, Receipt Number and Quantity


Step – 5 Make Payment Against Invoices

You can make payment through invoice form just click on Actions button then pay in Full

Enter all payment related details and save it payment will be created.


Step – 6 Create Accounting

Again click on Action button and then click on create accounting. You can create final accounting here.


Step – 7 Transfer to GL

Run Transfer To GL program to transfer this payment to GL.

Procure to Payment process has been completed.

Saturday, February 15, 2020

R12 BOM Get Resource Overheads


SELECT cro.* FROM bom_resources_v brv, mtl_parameters mp, cst_resource_overheads_v cro WHERE mp.organization_id = brv.organization_id AND cro.resource_id = brv.resource_id AND cro.organization_id = mp.organization_id -- AND mp.organization_code IN ( 'BGA' ) AND nvl(brv.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND brv.resource_code NOT IN ( SELECT brv1.resource_code FROM bom_resources_v brv1, mtl_parameters mp1 WHERE mp1.organization_id = brv1.organization_id AND mp1.organization_code IN ('ABC' ) ) ORDER BY brv.resource_code

R12 BOM Get Resource Rates

Exported from Notepad++
SELECT brv.resource_code, cst.cost_type_code, resource_rate FROM bom_resources_v brv, mtl_parameters mp, cst_resource_costs_v cst WHERE mp.organization_id = brv.organization_id AND cst.resource_id = brv.resource_id AND cst.organization_id = mp.organization_id -- AND mp.organization_code IN ( 'BGA' ) AND nvl(brv.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND brv.resource_code NOT IN ( SELECT brv1.resource_code FROM bom_resources_v brv1, mtl_parameters mp1 WHERE mp1.organization_id = brv1.organization_id AND mp1.organization_code IN ( 'ABC' ) ) ORDER BY brv.resource_code

R12 Get BOM Resource Data

Exported from Notepad++
SELECT brv.resource_code, brv.disable_date, brv.description, res_lkp.meaning resource_type, brv.unit_of_measure, charge_typ_lkp.meaning charge_type, ( SELECT meaning FROM apps.mfg_lookups_v WHERE lookup_type = 'BOM_BASIS_TYPE' AND lookup_code = brv.default_basis_type ) basis, expenditure_type, supply_subinventory, supply_locator_id, decode(brv.attribute1, 'Y', 'Yes', 'N', 'No', '') attribute1--"Display On Machine Shop Earned/Actual Hours Reports?" , brv.attribute2 --"Dispatch Printer?" --OUTSIDE PROCESSING---------- , decode(cost_code_type, 4, 'Yes', 3, 'No', '') outside_processing, ( SELECT segment1 FROM mtl_system_items_b msib WHERE msib.inventory_item_id = brv.purchase_item_id AND ROWNUM = 1 ) outside_process_item_code, ( SELECT segment1 FROM mtl_system_items_b msib WHERE msib.inventory_item_id = brv.billable_item_id AND ROWNUM = 1 ) billing_item_code -----COSTED------------------------------------------------- , decode(allow_costs_flag, 1, 'Yes', 2, 'No', '') costed, default_activity, decode(standard_rate_flag, 1, 'Yes', 2, 'No', '') standard_rate, ( SELECT ( segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.' || segment5 || '.' || segment6 ) x FROM gl_code_combinations WHERE code_combination_id = absorption_account ) absorption_account, ( SELECT ( segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.' || segment5 || '.' || segment6 ) x FROM gl_code_combinations WHERE code_combination_id = rate_variance_account ) rate_variance_account ------SKILLS--------a----------------------------------- , competence_id competence, '' skill_level, qualification_type_id qualification ----BATCHABLE------------------------------------------ , decode(batchable, 1, 'Yes', 2, 'No', '') batchable, min_batch_capacity, batch_window, max_batch_capacity, batch_capacity_uom, batch_window_uom FROM bom_resources_v brv, mtl_parameters mp, apps.mfg_lookups_v res_lkp, apps.mfg_lookups_v charge_typ_lkp WHERE mp.organization_id = brv.organization_id AND res_lkp.lookup_code = brv.resource_type AND res_lkp.lookup_type = 'BOM_RESOURCE_TYPE' -- AND charge_typ_lkp.lookup_code = brv.autocharge_type AND charge_typ_lkp.lookup_type = 'BOM_AUTOCHARGE_TYPE' -- AND mp.organization_code IN ( 'BGA' ) --AND brv.RESOURCE_CODE = 'ASSY' AND nvl(brv.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND NOT EXISTS ( SELECT 1 FROM bom_resources_v bdv, mtl_parameters mp--, -- BOM_DEPARTMENT_RESOURCES_V BDR1 WHERE mp.organization_id = bdv.organization_id AND mp.organization_code IN ( 'ABC' ) AND brv.resource_code = bdv.resource_code )

Wednesday, February 12, 2020

R12 Get Employee and Supervisor Information

Exported from Notepad++
SELECT DISTINCT level,e.* FROM ( SELECT DISTINCT papf.person_id, nvl(papf.employee_number, papf.attribute12) employee_number, papf.full_name "EMPLOYEE_FULL_NAME", papf.email_address, paaf.supervisor_id, papf1.employee_number "SUPERVISOR_EMP_NUMBER", papf1.full_name "SUPERVISOR_FULL_NAME" FROM apps.per_all_people_f papf, apps.per_all_assignments_f paaf, apps.per_all_people_f papf1, apps.per_person_types ppt WHERE papf.person_id = paaf.person_id AND papf1.person_id = paaf.supervisor_id AND papf.business_group_id = paaf.business_group_id AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND ppt.person_type_id = papf.person_type_id AND ppt.user_person_type <> 'Ex-employee' ) e CONNECT BY PRIOR person_id = supervisor_id START WITH employee_number = '1234567' ORDER BY employee_number select * from apps.per_all_people_f papf where papf.full_name ='Mr. James Bond'

R12 BOM Get Departments

Exported from Notepad++
SELECT mp.organization_code, bdv.department_code, description, disable_date, department_class_code, class_description, location_code, location_description, bdv.attribute1, bdv.attribute2 FROM bom_departments_v bdv, mtl_parameters mp WHERE mp.organization_id = bdv.organization_id AND mp.organization_code IN ( 'ABC','PQR' ) AND nvl(disable_date,(sysdate + 1)) > sysdate

Oracle Apps R12 TCA Customer Information Extract


SELECT hou.name source_ou, hp.party_type, hp.party_name customer_name, hp.known_as, --Account Information -- hca.account_number customer_number, hca.account_name, NULL orig_system_customer_ref, hca.customer_type, hcsu.location, hcsu.site_use_code, hcsub.location bill_to_loc_shipto, hcsu.primary_flag site_primary_flag, hcsu.status site_status, hps.party_site_name, hps.party_site_number, hcsu.primary_flag primary_site_use_flag, NULL orig_system_address_ref, --Site Address Information -- hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.state, hl.postal_code, --hl.country, ftv.territory_short_name, hcsu.gl_id_rec gl_id_rec_acnt_code, hcsu.gl_id_rev gl_id_rev_acnt_code, hcsu.tax_code, hcpc.name customer_profile_class_name, ac.name collector_name, rt.name payment_terms, hps.addressee, hcsu.ship_sets_include_lines_flag, hcsu.arrivalsets_include_lines_flag, hcsu.sched_date_push_flag, hcsu.freight_term, hcsu.fob_point, hcsu.ship_via, hcsu.ship_partial, hcsu.gsa_indicator, hcp.credit_checking, hcp.discount_terms, hcp.dunning_letters, hcp.interest_charges, hcp.send_statements, hcp.credit_balance_statements, hcp.credit_hold, hcp.standard_terms, hcp.interest_period_days, hcp.statement_cycle_id, hcp.tax_printing_option, hcp.auto_rec_incl_disputed_flag FROM hz_parties hp, hz_party_sites hps, hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, hz_cust_site_uses_all hcsub, hz_customer_profiles hcp, hz_cust_profile_classes hcpc, hz_locations hl, fnd_territories_vl ftv, ra_terms rt, ar_collectors ac, hr_operating_units hou WHERE hp.party_id = hca.party_id AND hca.cust_account_id = hcas.cust_account_id AND hps.party_site_id = hcas.party_site_id AND hcp.site_use_id (+) = hcsu.site_use_id AND hps.location_id = hl.location_id AND hcsub.site_use_id (+) = hcsu.bill_to_site_use_id AND hp.status = 'A' AND hcas.status = 'A' AND hca.status = 'A' AND hcsu.status = 'A' AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id AND rt.term_id (+) = hcsu.payment_term_id AND hcp.profile_class_id = hcpc.profile_class_id (+) AND hcsu.org_id = hou.organization_id AND ac.collector_id (+) = hcp.collector_id AND ftv.territory_code (+) = hl.country AND hou.name = :P_OU_NAME

Tuesday, February 11, 2020

R12 Generate Intended BOM


set serveroutput on size unlimited DECLARE err_msg VARCHAR2(5000); error_code NUMBER; grp_id NUMBER; session_id NUMBER; CURSOR c_get_items_for_BOM IS SELECT inventory_item_id, organization_id FROM mtl_system_items msi WHERE 1=1 AND msi.segment1 in ('ITEM_A','ITEM_B') ---- PUT YOUR ITEMS HERE AND organization_id =2; BEGIN SELECT bom_explosion_temp_s.NEXTVAL INTO grp_id FROM Dual ; SELECT bom_explosion_temp_session_s.NEXTVAL INTO session_id FROM Dual ; FOR r_get_items_for_BOM IN c_get_items_for_BOM LOOP bompexpl.exploder_userexit ( 0 --verify_flag IN NUMBER DEFAULT 0, ,r_get_items_for_BOM.organization_id -- org_id IN NUMBER, ,1 -- order_by IN NUMBER DEFAULT 1, ,grp_id -- grp_id unique value to identify current explosion -- use value from sequence bom_explosion_temp_s ,session_id -- session_id -- IN NUMBER DEFAULT 0, it is unique value to identify current session ,use value from bom_explosion_temp_session_s ,10 -- levels_to_explode IN NUMBER DEFAULT 1, ,1 -- bom_or_eng IN NUMBER DEFAULT 1, ,1 -- impl_flag IN NUMBER DEFAULT 1, ,2 -- plan_factor_flag IN NUMBER DEFAULT 2, 2 means NO ,2 -- explode_option IN NUMBER DEFAULT 2,explode_option 1 - All, 2 - Current,3 - Current and future ,2 -- module IN NUMBER DEFAULT 2, ,0 -- cst_type_id IN NUMBER DEFAULT 0, ,2 -- std_comp_flag IN NUMBER DEFAULT 0, ,1 -- expl_qty IN NUMBER DEFAULT 1, ,r_get_items_for_BOM.inventory_item_id --1681441 -- item_id IN NUMBER, ,'' -- alt_desg IN VARCHAR2 DEFAULT '', ,'' -- comp_code IN VARCHAR2 DEFAULT '', ,to_char(sysdate,'dd-mon-yy HH24:MI') -- rev_date IN VARCHAR2, ,'' --unit_number IN VARCHAR2 DEFAULT '', ,0 --release_option IN NUMBER DEFAULT 0, ,err_msg -- err_msg OUT NOCOPY VARCHAR2, ,error_code -- error_code OUT NOCOPY NUMBER ); IF err_msg is not null or error_code is not null then dbms_output.put_line(err_msg); dbms_output.put_line(error_code); END IF; END LOOP; dbms_output.put_line('Use grp_id = '||grp_id ' to query the table BOM_EXPLOSION_TEMP in this session only.'); dbms_output.put_line('session_id = '||session_id); END;