Oracle Apps P2P ( Procure to Pay ) - Flow
Step 1: Create the Requisition in Oracle Apps
When the company/person wants to purchase any item a Requisition is created
Step 2: RFQ (Request For Quotation)
Once Requisition is approved then the company/person will Request for Quotation (RFQ) specifying
the required item, quantity.... etc and send them to the vendors who can supply the item
Step 3: Quotation
Once the vendor receives the RFQ then he will prepare the Quotation and send it back to the company/person
Step 4: Create the Purchase Order (PO) in Oracle Apps
After receiving different quotations from different vendors company/person will go through the quotations
and approves the quotation that is best suited/reliable for the company/person.
Based on the approved quotation Purchase Order is generated to the vendor. Now the vendor will supply the item.
Step 5: Create the Receipts in Oracle Apps
Once the Item is shipped the vendor will raise the Receipt to the company/person.
Step 6: Create the Invoice in Oracle Apps
Based on the receipt Invoice is generated. For this invoice the company/person will make Payments.
Step 7: Import & Post to GL
--------------------------------------------------------------------------------------------------
Oracle Apps Purchasing Key Tables and their functionalities
PO_REQUISITION_HEADERS_ALL
~~~~~~~~~~~~~~~~~~~~~~~~~~~
PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row for each requisition header you create.
Each row contains the requisition number, preparer, status, and description. REQUISITION_HEADER_ID is the unique system–generated
requisition number.
REQUISITION_HEADER_ID is invisible to the user. SEGMENT1 is the number you use to identify the requisition
in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle
Purchasing generate requisition numbers for you.
PO_REQUISITION_HEADERS_ALL is one of three tables storing requisition information.
PO_REQUISITION_HEADERS_ALL corresponds to the Header region of the Requisitions window.SEGMENT1 provides unique values for each row
in the table in addition to REQUISITION_HEADER_ID.
PO_REQUISITION_LINES_ALL
~~~~~~~~~~~~~~~~~~~~~~~~
PO_REQUISITION_LINES stores information about requisition lines. You need one row for each requisition line you create.
Each row contains the line number, item number, item category, item description, need–by date, deliver–to location, item quantities,
units, prices, requestor, notes, and suggested supplier information for the requisition line. LINE_LOCATION_ID identifies the purchase order
shipment line on which you placed the requisition.
LINE_LOCATION_ID is null if you have not placed the requisition line on a purchase order.
BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for
the requisition line. PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multisourced
this requisition line.
PO_REQUISITION_LINES is one of three tables storing requisition information. This table corresponds to the Lines region of the Requisitions window.
~~~~~~~~~~~~~~~~~~~~
PO_REQ_DISTRIBUTIONS_ALL
~~~~~~~~~~~~~~~~~~~~~~~~
PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line
must have at least one accounting distribution. You need one row for each requisition distribution you create. Each row includes
the Accounting Flexfield ID and requisition line quantity.
PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition information.
This table corresponds to the requisition Distributions window, accessible through the Requisitions window.
PO_DISTRIBUTIONS_ALL
~~~~~~~~~~~~~~~~~~~~
PO_DISTRIBUTIONS_ALL contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line
you attach to a purchase order shipment. There are four types of documents using distributions in Oracle Purchasing:
• Standard Purchase Orders
• Planned Purchase Orders
• Planned Purchase Order Releases
• Blanket Purchase Order Releases
Each row includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution. Oracle Purchasing uses this
information to record accounting and requisition information for purchase orders and releases. PO_DISTRIBUTIONS_ALL is one of five tables storing
purchase order and release information. Some columns in PO_DISTRIBUTIONS_ALL contain information only if certain conditions exist:
• If you autocreate this accounting distribution from a requisition, REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution
you copy on the purchase order.
• If you use a foreign currency on your purchase order,Oracle Purchasing stores currency conversion information in RATE and RATE_DATE.
• If you use encumbrance, GL_ENCUMBERED_DATE and GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle Purchasing uses to create
journal entries in Oracle General Ledger.
• If you do not autocreate the purchase order from online requisitions, REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain
the requisition number and requisition line number of the corresponding paper requisition. These two columns are not foreign keys to another table.
• If the distribution corresponds to a blanket purchase order release, PO_RELEASE_ID identifies this release.
• If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned purchase order release. If you cancel the distribution,
Oracle Purchasing automatically updates QUANTITY_CANCELLED or GL_CANCELLED_DATE. Oracle Purchasing also enters UNENCUMBERED_AMOUNT if you use encumbrance.
PO_HEADERS_ALL
~~~~~~~~~~~~~~~~
PO_HEADERS_ALL contains header information for your purchasing documents. You need one row for each document you create. There are six
types of documents that use PO_HEADERS_ALL:
• RFQs
• Quotations
• Standard purchase orders
• Planned purchase orders
• Blanket purchase orders
• Contracts
Each row contains buyer information, supplier information, brief notes, foreign currency information, terms and conditions information,
and the status of the document.
Oracle Purchasing uses this information to record information that is related to a complete document. PO_HEADER_ID is the unique system–generated
primary key and is invisible to the user. SEGMENT1 is the system–assigned number you use to identify the document in forms and reports.
Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONT_ALL table if you choose to let Oracle Purchasing generate
document numbers for you. SEGMENT1 is not unique for the entire table. Different document types can share the same numbers. You can uniquely
identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using PO_HEADER_ID. If APPROVED_FLAG is ’Y’, the purchase order is approved.
If your document type is a blanket purchase order, contract purchase order,RFQ, or quotation, Oracle Purchasing uses START_DATE and END_DATE
to store the valid date range for the document.
Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders. If you autocreate a quotation from
an RFQ using the Copy Document window, Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID. Oracle Purchasing also
uses FROM_TYPE_LOOKUP_CODE to indicate that you copied the quotation from an RFQ.
----- PO header level cancellation -----------------------------------------------------------
PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT
( p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
p_doc_type => v_doc_type,
p_doc_subtype => v_doc_subtype,
p_doc_id => v_po_header_id,
p_doc_num => NULL,
p_release_id => NULL,
p_release_num => NULL,
p_doc_line_id => NULL,
p_doc_line_num => NULL,
p_doc_line_loc_id => NULL,
p_doc_shipment_num => NULL,
p_action => v_action,
p_action_date => v_action_date,
p_cancel_reason => NULL,
p_cancel_reqs_flag => 'N',
p_print_flag => NULL,
p_note_to_vendor => NULL,
p_use_gldate => NULL,
p_org_id => v_org_id
);
PO_LINES_ALL
PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document.
There are five document types that use lines:
• RFQs
• Quotations
• Standard purchase orders
• Blanket purchase orders
• Planned purchase orders
Each row includes the line number, the item number and category, unit, price, tax information, matching information, and quantity ordered
for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs.
PO_LINE_ID is the unique system–generated line number invisible to the user. LINE_NUM is the number of the line on the purchase order.
Oracle Purchasing uses CONTRACT_NUM to reference a contract purchase order from a standard purchase order line. Oracle Purchasing uses
ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT, QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for blanket and planned purchase order lines.
The QUANTITY field stores the total quantity of all purchase ordershipment lines (found in PO_LINE_LOCATIONS_ALL).
Reply .
PO_VENDORS
PO_VENDORS stores information about your suppliers. You need one row for each supplier you define. Each row includes the supplier name
as well as purchasing, receiving, payment, accounting, tax, classification, and general information.
Oracle Purchasing uses this information to determine active suppliers. VENDOR_ID is the unique system–generated receipt header number
invisible to the user. SEGMENT1 is the system–generated or manually assigned number you use to identify the supplier in forms and reports.
Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate supplier
numbers for you. This table is one of three tables that store supplier information. PO_VENDORS corresponds to the Suppliers window.
PO_VENDOR_SITES_ALL
PO_VENDOR_SITES_ALL stores information about your supplier sites. You need a row for each supplier site you define. Each row includes
the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this information to store
supplier address information. This table is one of three tables that store supplier information. PO_VENDOR_SITES_ALL corresponds
to the Sites region of the Suppliers window.
select prh.segment1 "PO Requisition Number",
pha.segment1 "PO Number",
aps.SEGMENT1 "Supplier Number",
aps.vendor_name,
apss.vendor_site_code,
apsc.first_name,
apsc.last_name,
pla.item_id,
plla.ship_to_organization_id,
plla.ship_to_location_id,
rt.transaction_type,
rt.destination_type_code,2
rsh.receipt_num "PO Receipt Number",
aia.invoice_num,
aida.dist_code_combination_id,
aca.check_number,gjh.ledger_id,
gjh.name
from po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
po_line_locations_all plla,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_supplier_contacts apsc,
rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_invoice_payments_all aipa,
ap_checks_all aca,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
where prh.segment1 = '14575'
and aps.vendor_id = pha.vendor_id
and apss.vendor_id = aps.vendor_id
and apss.vendor_site_id (+) = pha.vendor_site_id
and apss.vendor_site_id = aca.vendor_site_id
and apsc.vendor_site_id = apss.vendor_site_id
and apsc.vendor_contact_id = pha.vendor_contact_id
and prl.requisition_header_id = prh.requisition_header_id
and prd.requisition_line_id = prl.requisition_line_id
and pda.req_distribution_id = prd.distribution_id
and pla.po_header_id = pda.po_header_id
and pla.po_line_id = pda.po_line_id
and pha.po_header_id = pla.po_header_id
and pha.org_id = 204
and plla.po_header_id = pla.po_header_id
and plla.po_line_id = pla.po_line_id
and rt.transaction_type = 'DELIVER'
and rt.po_header_id = pha.po_header_id
and rt.po_line_id = pla.po_line_id
and rsh.shipment_header_id = rt.shipment_header_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rsl.shipment_line_id = rt.shipment_line_id
and aila.po_header_id = pha.po_header_id
and aila.po_line_id = pla.po_line_id
and aia.invoice_id = aila.invoice_id
and aida.invoice_id = aila.invoice_id
and aida.invoice_line_number = aila.line_number
and aipa.invoice_id = aia.invoice_id
and aca.check_id = aipa.check_id
and xte.entity_code = 'AP_PAYMENTS'
and xte.transaction_number = aca.check_number
and xte.source_id_int_1 = aipa.check_id
and xte.security_id_int_1 = aia.org_id
and xe.entity_id = xte.entity_id
and xah.event_id = xe.event_id
and xal.ae_header_id = xah.ae_header_id
and xal.ae_line_num = aida.invoice_line_number
and xdl.ae_header_id = xah.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
and xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
and gir.reference_5 = xte.entity_id -- Entity Id
and gir.reference_6 = to_char(xe.event_id) -- Event Id
and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
and gir.gl_sl_link_id = xal.gl_sl_link_id
--and gir.created_by = 1318
and gjb.je_batch_id = gir.je_batch_id
and gjh.je_batch_id=gjb.je_batch_id
and gjh.je_header_id = gir.je_header_id
and gjl.je_header_id=gjh.je_header_id
and gjl.je_line_num= gir.je_line_num
Step 1: Create the Requisition in Oracle Apps
When the company/person wants to purchase any item a Requisition is created
Step 2: RFQ (Request For Quotation)
Once Requisition is approved then the company/person will Request for Quotation (RFQ) specifying
the required item, quantity.... etc and send them to the vendors who can supply the item
Step 3: Quotation
Once the vendor receives the RFQ then he will prepare the Quotation and send it back to the company/person
Step 4: Create the Purchase Order (PO) in Oracle Apps
After receiving different quotations from different vendors company/person will go through the quotations
and approves the quotation that is best suited/reliable for the company/person.
Based on the approved quotation Purchase Order is generated to the vendor. Now the vendor will supply the item.
Step 5: Create the Receipts in Oracle Apps
Once the Item is shipped the vendor will raise the Receipt to the company/person.
Step 6: Create the Invoice in Oracle Apps
Based on the receipt Invoice is generated. For this invoice the company/person will make Payments.
Step 7: Import & Post to GL
--------------------------------------------------------------------------------------------------
Oracle Apps Purchasing Key Tables and their functionalities
PO_REQUISITION_HEADERS_ALL
~~~~~~~~~~~~~~~~~~~~~~~~~~~
PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row for each requisition header you create.
Each row contains the requisition number, preparer, status, and description. REQUISITION_HEADER_ID is the unique system–generated
requisition number.
REQUISITION_HEADER_ID is invisible to the user. SEGMENT1 is the number you use to identify the requisition
in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle
Purchasing generate requisition numbers for you.
PO_REQUISITION_HEADERS_ALL is one of three tables storing requisition information.
PO_REQUISITION_HEADERS_ALL corresponds to the Header region of the Requisitions window.SEGMENT1 provides unique values for each row
in the table in addition to REQUISITION_HEADER_ID.
PO_REQUISITION_LINES_ALL
~~~~~~~~~~~~~~~~~~~~~~~~
PO_REQUISITION_LINES stores information about requisition lines. You need one row for each requisition line you create.
Each row contains the line number, item number, item category, item description, need–by date, deliver–to location, item quantities,
units, prices, requestor, notes, and suggested supplier information for the requisition line. LINE_LOCATION_ID identifies the purchase order
shipment line on which you placed the requisition.
LINE_LOCATION_ID is null if you have not placed the requisition line on a purchase order.
BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for
the requisition line. PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multisourced
this requisition line.
PO_REQUISITION_LINES is one of three tables storing requisition information. This table corresponds to the Lines region of the Requisitions window.
~~~~~~~~~~~~~~~~~~~~
PO_REQ_DISTRIBUTIONS_ALL
~~~~~~~~~~~~~~~~~~~~~~~~
PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line
must have at least one accounting distribution. You need one row for each requisition distribution you create. Each row includes
the Accounting Flexfield ID and requisition line quantity.
PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition information.
This table corresponds to the requisition Distributions window, accessible through the Requisitions window.
PO_DISTRIBUTIONS_ALL
~~~~~~~~~~~~~~~~~~~~
PO_DISTRIBUTIONS_ALL contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line
you attach to a purchase order shipment. There are four types of documents using distributions in Oracle Purchasing:
• Standard Purchase Orders
• Planned Purchase Orders
• Planned Purchase Order Releases
• Blanket Purchase Order Releases
Each row includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution. Oracle Purchasing uses this
information to record accounting and requisition information for purchase orders and releases. PO_DISTRIBUTIONS_ALL is one of five tables storing
purchase order and release information. Some columns in PO_DISTRIBUTIONS_ALL contain information only if certain conditions exist:
• If you autocreate this accounting distribution from a requisition, REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution
you copy on the purchase order.
• If you use a foreign currency on your purchase order,Oracle Purchasing stores currency conversion information in RATE and RATE_DATE.
• If you use encumbrance, GL_ENCUMBERED_DATE and GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle Purchasing uses to create
journal entries in Oracle General Ledger.
• If you do not autocreate the purchase order from online requisitions, REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain
the requisition number and requisition line number of the corresponding paper requisition. These two columns are not foreign keys to another table.
• If the distribution corresponds to a blanket purchase order release, PO_RELEASE_ID identifies this release.
• If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned purchase order release. If you cancel the distribution,
Oracle Purchasing automatically updates QUANTITY_CANCELLED or GL_CANCELLED_DATE. Oracle Purchasing also enters UNENCUMBERED_AMOUNT if you use encumbrance.
PO_HEADERS_ALL
~~~~~~~~~~~~~~~~
PO_HEADERS_ALL contains header information for your purchasing documents. You need one row for each document you create. There are six
types of documents that use PO_HEADERS_ALL:
• RFQs
• Quotations
• Standard purchase orders
• Planned purchase orders
• Blanket purchase orders
• Contracts
Each row contains buyer information, supplier information, brief notes, foreign currency information, terms and conditions information,
and the status of the document.
Oracle Purchasing uses this information to record information that is related to a complete document. PO_HEADER_ID is the unique system–generated
primary key and is invisible to the user. SEGMENT1 is the system–assigned number you use to identify the document in forms and reports.
Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONT_ALL table if you choose to let Oracle Purchasing generate
document numbers for you. SEGMENT1 is not unique for the entire table. Different document types can share the same numbers. You can uniquely
identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using PO_HEADER_ID. If APPROVED_FLAG is ’Y’, the purchase order is approved.
If your document type is a blanket purchase order, contract purchase order,RFQ, or quotation, Oracle Purchasing uses START_DATE and END_DATE
to store the valid date range for the document.
Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders. If you autocreate a quotation from
an RFQ using the Copy Document window, Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID. Oracle Purchasing also
uses FROM_TYPE_LOOKUP_CODE to indicate that you copied the quotation from an RFQ.
----- PO header level cancellation -----------------------------------------------------------
PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT
( p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
p_doc_type => v_doc_type,
p_doc_subtype => v_doc_subtype,
p_doc_id => v_po_header_id,
p_doc_num => NULL,
p_release_id => NULL,
p_release_num => NULL,
p_doc_line_id => NULL,
p_doc_line_num => NULL,
p_doc_line_loc_id => NULL,
p_doc_shipment_num => NULL,
p_action => v_action,
p_action_date => v_action_date,
p_cancel_reason => NULL,
p_cancel_reqs_flag => 'N',
p_print_flag => NULL,
p_note_to_vendor => NULL,
p_use_gldate => NULL,
p_org_id => v_org_id
);
PO_LINES_ALL
PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document.
There are five document types that use lines:
• RFQs
• Quotations
• Standard purchase orders
• Blanket purchase orders
• Planned purchase orders
Each row includes the line number, the item number and category, unit, price, tax information, matching information, and quantity ordered
for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs.
PO_LINE_ID is the unique system–generated line number invisible to the user. LINE_NUM is the number of the line on the purchase order.
Oracle Purchasing uses CONTRACT_NUM to reference a contract purchase order from a standard purchase order line. Oracle Purchasing uses
ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT, QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for blanket and planned purchase order lines.
The QUANTITY field stores the total quantity of all purchase ordershipment lines (found in PO_LINE_LOCATIONS_ALL).
Reply .
PO_VENDORS
PO_VENDORS stores information about your suppliers. You need one row for each supplier you define. Each row includes the supplier name
as well as purchasing, receiving, payment, accounting, tax, classification, and general information.
Oracle Purchasing uses this information to determine active suppliers. VENDOR_ID is the unique system–generated receipt header number
invisible to the user. SEGMENT1 is the system–generated or manually assigned number you use to identify the supplier in forms and reports.
Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate supplier
numbers for you. This table is one of three tables that store supplier information. PO_VENDORS corresponds to the Suppliers window.
PO_VENDOR_SITES_ALL
PO_VENDOR_SITES_ALL stores information about your supplier sites. You need a row for each supplier site you define. Each row includes
the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this information to store
supplier address information. This table is one of three tables that store supplier information. PO_VENDOR_SITES_ALL corresponds
to the Sites region of the Suppliers window.
select prh.segment1 "PO Requisition Number",
pha.segment1 "PO Number",
aps.SEGMENT1 "Supplier Number",
aps.vendor_name,
apss.vendor_site_code,
apsc.first_name,
apsc.last_name,
pla.item_id,
plla.ship_to_organization_id,
plla.ship_to_location_id,
rt.transaction_type,
rt.destination_type_code,2
rsh.receipt_num "PO Receipt Number",
aia.invoice_num,
aida.dist_code_combination_id,
aca.check_number,gjh.ledger_id,
gjh.name
from po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
po_line_locations_all plla,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_supplier_contacts apsc,
rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_invoice_payments_all aipa,
ap_checks_all aca,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
where prh.segment1 = '14575'
and aps.vendor_id = pha.vendor_id
and apss.vendor_id = aps.vendor_id
and apss.vendor_site_id (+) = pha.vendor_site_id
and apss.vendor_site_id = aca.vendor_site_id
and apsc.vendor_site_id = apss.vendor_site_id
and apsc.vendor_contact_id = pha.vendor_contact_id
and prl.requisition_header_id = prh.requisition_header_id
and prd.requisition_line_id = prl.requisition_line_id
and pda.req_distribution_id = prd.distribution_id
and pla.po_header_id = pda.po_header_id
and pla.po_line_id = pda.po_line_id
and pha.po_header_id = pla.po_header_id
and pha.org_id = 204
and plla.po_header_id = pla.po_header_id
and plla.po_line_id = pla.po_line_id
and rt.transaction_type = 'DELIVER'
and rt.po_header_id = pha.po_header_id
and rt.po_line_id = pla.po_line_id
and rsh.shipment_header_id = rt.shipment_header_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rsl.shipment_line_id = rt.shipment_line_id
and aila.po_header_id = pha.po_header_id
and aila.po_line_id = pla.po_line_id
and aia.invoice_id = aila.invoice_id
and aida.invoice_id = aila.invoice_id
and aida.invoice_line_number = aila.line_number
and aipa.invoice_id = aia.invoice_id
and aca.check_id = aipa.check_id
and xte.entity_code = 'AP_PAYMENTS'
and xte.transaction_number = aca.check_number
and xte.source_id_int_1 = aipa.check_id
and xte.security_id_int_1 = aia.org_id
and xe.entity_id = xte.entity_id
and xah.event_id = xe.event_id
and xal.ae_header_id = xah.ae_header_id
and xal.ae_line_num = aida.invoice_line_number
and xdl.ae_header_id = xah.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
and xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
and gir.reference_5 = xte.entity_id -- Entity Id
and gir.reference_6 = to_char(xe.event_id) -- Event Id
and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
and gir.gl_sl_link_id = xal.gl_sl_link_id
--and gir.created_by = 1318
and gjb.je_batch_id = gir.je_batch_id
and gjh.je_batch_id=gjb.je_batch_id
and gjh.je_header_id = gir.je_header_id
and gjl.je_header_id=gjh.je_header_id
and gjl.je_line_num= gir.je_line_num
http://sravan20apps.blogspot.in/
ReplyDelete