Oracle Apps(EBS- E business suite) Procure to pay

Oracle Apps(EBS- E business suite) Procure to pay:--

IBM COMPANY—3 EMPLOYEEES:-

ROLES ASSIGNED:--REQUESTER, PREPARER, BUYER

Prerequisites:--

1.Requisition-- Purchasing> Requisitions> Requisitions

a. Requisition Approval

RFQ—from our company to suppliers

quotations --- from suppliers to our company

quote analysis ---------our company finalizing supplier

b. Request for Quotations

c. Quotations

d. Quote analysis

2.Po

3.Receipt>purchasing>receiving>receipts

4.Ap invoice > payables>invoices>entry>invoices

5.Ap payment > in invoice window, go to action->validation, create accounting, pay in full

6.Transfer to gl

Item creation in inventory

1)Requisitions:--list of items that we are going to buy ex: 3 laptops, 3 mouses, 3 key boards—it is done by preparer

If we want to create a template(purchased items) for creating items to inventory:--

Inventory>Setup>items>templates

Click on new to create a template

1.Requisition nav:

Purchasing> Requisitions> Requisitions

Click on save requisition number will be generated(automatic).

Ex:--item= 11111

requisition number=15980.

We can make the requisition number as manual,

nav: Purchasing>setup>Organization>purchasing options

Tables: PO_REQUISITION_HEADERS_V, PO_REQUISITION_LINES_V, PO_REQ_DISTRIBUTIONS_V

Charge account field=01-000-1410-0000-000 is coming from gl_code_combination table

Nav:GL>setup>accounts>combination

SELECT papf.full_name, prha.segment1 requisition_number

FROM per_all_people_f papf,

po_requisition_headers_all prha,

po_action_history pah

WHERE prha.requisition_header_id = pah.object_id

AND papf.person_id = pah.employee_id

and prha.SEGMENT1='15980'---requisition number

AND pah.action_code = 'APPROVE'

AND prha.org_id = 204

2)After Requisition, Approval

3) Request for quotation

Link between requisition and rfq??

SELECT prha.segment1 requisition_number, pha.segment1 rfq_number

FROM po_requisition_headers_all prha,

po_headers_all pha,

po_requisition_lines_all prla,

po_lines_all pla

WHERE prha.requisition_header_id = prla.requisition_header_id

AND pha.po_header_id = pla.po_header_id

and pla.CREATION_DATE=prla.LAST_UPDATE_DATE

and prla.ON_RFQ_FLAG='Y'

and pha.SEGMENT1='310'

RFQ—from our company to suppliers

quotations --- from suppliers to our company

quote analysis ---------our company finalizing supplier

PO - purchase order ------Buyer -----------------------6587

Autocreate po based on the requisition number

Preparer,requester ,buyer

Po types – 4 –(SPBC Purchase order)

1.Standard-----once or twice

2.Planned ---- scheduled purchasing,future purpose

3.Blanket -------- agreement 1 year,laptop(long term for purchasing specific item)

4.Contract --------like blanket po,we can purchase any item(long term for purchasing any item)

PO_HEADERS_all ----po_header_id

PO_LINES_All----- po_line_id

PO_LINE_LOCATIONS_all—po_line_location_id

PO_DISTRIBUTIONS_All- po_distribution_id

Link between requisition number and po number??

/* Formatted on 2020/10/22 08:20 (Formatter Plus v4.8.8) */

SELECT prha.segment1 requisition_number, pha.segment1 po_number

FROM po_distributions_all pda,

po_req_distributions_all prda,

po_headers_all pha,

po_requisition_lines_all prla,

po_requisition_headers_all prha

WHERE pda.req_distribution_id = prda.distribution_id

AND prda.requisition_line_id = prla.requisition_line_id

AND prla.requisition_header_id = prha.requisition_header_id

AND pha.po_header_id = pda.po_header_id

AND pha.segment1 = '6587'

Match approval options- 4 way matching

2 way ,3 way 4 way (po quantity, billed quantity, receipt, inspection required)

Po_line_locations_all

Receipt_required_flag

Inspection_required_flag

PO Receipt------------------------------ 9082

RCV_SHIPMENT_HEADERS

Rcv_shipment_lines

Rcv_transactions

Receiving Transaction Processor--------po receipt creation

ADS (Pay On Receipt AutoInvoice)

Payables Open Interface Import--------------it will create ap invoice automatically and give the ap invoice number in view_log

Join between Po and receipt

---------------

SELECT pha.segment1 po_number, rsh.receipt_num, rsh.creation_date

FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_headers_all pha

WHERE rsh.shipment_header_id = rsl.shipment_header_id

AND rsl.po_header_id = pha.po_header_id

--AND rsh.receipt_num = '9082'

AND pha.segment1 = '6587'

--------

Ref> https://rpforacle.blogspot.com/2018/07/link-between-po-and-receipt-in-oracle-apps.html

Tables Involved

rcv_shipment_headers

rcv_shipment_lines -----------po receipt table name

rcv_transactions



po_headers_all ------------- po header and line table names

po_lines_all

po_distributions_all

po_line_locations_all

Ap invoice

Payment

Transferjournal entry to gl

1.Requisition

Requisition Approval

Request for Quotations(RFQ)

Quotations

Quote analysis

2.Po

3.Receipt

4.Ap invoice

5.Ap payment

6.Transfer to gl

approval

AFTER Creating requisition and getting requisition number

Go to Purchasing> requisition>requisitions and query the requisition number and click on approve to get approved as shown below

in the same requisition window .

click on approve.

To find the status=approved go to Notification summary:--

and find the status in requisition summary window.(give the requisition number and find)

requisition > requisition summary

to find the persons who has submitted and who has approved>

go to tools-->view-->action history

to find the complete info.

go to tools-->view -->click on approval through workflow button.

to cancel or close the requisition.we can go to

tools-->control

we can select the cancel requisition.

provide the comments

base table:-----

-------------

po_action_history

opracle query link to Get the purchase order requisition information and approver related to it:--

-----------------------------------------------------------

SELECT

pah.action_code,

ppf.full_name approver,

prha.segment1,

prha.org_id

FROM

po_action_history pah,

po_requisition_headers_all prha,

per_all_people_f ppf

WHERE

prha.REQUISITION_HEADER_ID=pah.object_id and

pah.action_code='APPROVE' and

pah.EMPLOYEE_ID=ppf.person_id and

sysdate between ppf.effective_start_date and

ppf.effective_end_Date

and prha.segment1='15980'—requisition number

to find out the approval limit

--------------------------------

SELECT pav.agent_name buyer_name, pav7.d_job_id job,

pav7.d_supervisor_id buyer_supervisor, pav7.d_organization_id,

DECODE (ppc.control_function_id,

4, 'Approve Blanket Purchase Agreements',

5, 'Approve Blanket Releases',

2, 'Approve Contract Purchase Orders',

3, 'Approve Planned Purchase Orders',

6, 'Approve Scheduled Releases',

1, 'Approve Standard Purchase Orders',

7, 'Approve Internal Requisitions',

8, 'Approve Purchase Requisitions'

) documenttype,

pcg.description,

TO_CHAR (pcr.amount_limit, '$999,999,999,999.99') approval_limit,

pcr.amount_limit, pav.start_date_active, pav.end_date_active

FROM apps.po_agents_v pav,

apps.per_assignments_v7 pav7,

apps.po_position_controls ppc,

apps.po_control_groups_all pcg,

apps.po_control_rules pcr

WHERE pav.agent_id = pav7.person_id

AND SYSDATE BETWEEN pav7.effective_start_date AND pav7.effective_end_date

AND ppc.job_id = pav7.job_id

AND ppc.control_group_id = pcg.control_group_id

AND pcg.control_group_id = pcr.control_group_id

AND pcr.object_code = 'DOCUMENT_TOTAL'

AND pcg.org_id = 204

AND ppc.org_id = 204

AND ppc.position_id = pav7.position_id

RFQ(Request for quotation);