P2P and O2C: Procure to Pay and Order to Cash Cycles

Oracle has developed this ERP solution which truly covers these both cycles as well as many others. Oracle EBS comprises of the Standard Core Business Management

applications like General Ledger, Payables, Receivables, Purchasing, Order Management, Inventory, Discrete Manufacturing, Process Manufacturing , HRMS and many

more. The application mentioned are so integrated that it handles the beginning to end of both Assets and Liabilities. When refering to Assets its referring to applications like

Order Management and Receivables, and when refering to Liabilities its referring to Purchasing and Payables and both of these Assets and Liabilities are finally pushed and

calculated in Oracle General Ledger.

The base or the heart of Oracle EBS is Oracle General Ledger. we can say GL an intrinsic.

Procure to Pay:
Procure to Pay means Procuring Raw Materials required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this

is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

Oracle Purchasing: You enter Suppliers of different materials and products you want to purchase to manufacture a finished good that your organization plans to sell.
Oracle Purchasing: You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.
Oracle Purchasing: Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best

quote.
Oracle Purchasing: You prepare a Purchase Order(PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that

supplier
Oracle Purchasing: The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in

Oracle Purchasing.

Oracle Inventory / Oracle Assets: It’s up to us whether we want to receive the goods at our head office or Inventory directly. In either case we move the received

goods to different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle

Assets at the time of Invoice creation in Oracle Payables.
Oracle General Ledger: Once we move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.
Oracle Payables: After this the supplier sends the invoice for the purchased goods and we Enter or Match the invoice against the PO from Oracle Purchasing in Oracle

Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.
Oracle General Ledger: When we enter the invoice it means that we have created a Liability against that supplier and also we have recorded the expense incurred or asset

purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.
Oracle Payables: pay the invoice and settle the Liability.
Oracle General Ledger: The liability is settled and our cash movement account is updated.
Oracle Cash Management: As we pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled,

Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.
Oracle General Ledger: our cash at bank is updated with actual balance.
Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM): we start the manufacturing of our final product. Both OPM or ODM requests the different raw

materials from our inventory organizations and manufactures a finished good.
Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count

from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.
Now the final product is ready to be sold in the market and from here the O2C cycle starts.

Order to Cash Cycle:
Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with

the marketing campaign and decides to buy your product and from here starts the O2C cycle.

Oracle Order Management: Customer places the order.
Oracle Order Management: You enter the customer order
Oracle Inventory: Check the available unit and the quantity ordered by the customer.
Oracle Order Management: You ship the product to customer site and decreases the Finished Goods inventory.
Oracle Receivables: The customer receives the product and you invoice the customer.
Oracle General Ledger: You record your revenue and receivables.
Oracle Receivables: The customer pays and you receive the cash/check.
Oracle Cash Management: Oracle Receivables sends the customer receipt for Bank Reconciliation. After reconciliation, Oracle Cash Management send the actual bank

balance or Oracle General Ledger.
Oracle General Ledger: You have the actual bank balance.
This is how the P2P and O2C cycle works, but this is not the only way, obviously there are many other applications with different cycles. This is one of them.

Post from Know oracle

Delete concurrent program from backend

begin
fnd_program.delete_program('short name of the concurrent program to be deleted','Schema');
fnd_program.delete_executable('short name of the concurrent program to be deleted','Schema');
commit;
end;

Concurrent program Queries

Responsibilities to which assigned: 

SELECT DISTINCT * FROM fnd_responsibility_tl 
WHERE responsibility_id IN ( SELECT responsibility_id FROM fnd_responsibility_vl WHERE request_group_id IN ( SELECT request_group_id FROM fnd_request_group_units WHERE request_unit_id = (SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name = 'XXXX')) AND end_date IS NULL) AND "LANGUAGE" LIKE 'US' ORDER BY responsibility_name 

Application name: 

SELECT * FROM fnd_application "application name" 
WHERE application_id IN (SELECT application_id FROM fnd_request_group_units WHERE request_unit_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX')) 

concurrent program short name: 

SELECT * FROM fnd_concurrent_programs 
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX') 

Execution file name: 

SELECT * FROM fnd_executables 
WHERE executable_id=(SELECT executable_id FROM fnd_concurrent_programs WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX')) 

Requests groups: 

SELECT * FROM fnd_request_groups "requests groups" 
WHERE request_group_id IN (SELECT request_group_id FROM fnd_request_group_units WHERE request_unit_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXXX')) 

Flow of Order Management

Begin by placing the order in Order Management (OM):
• Entered (OM): Order is saved but not booked.
• Booked (OM): Order is booked.
• Scheduled (OM): You can customize the workflow to show the Scheduled statuswhich indicates that the order line has been successfully scheduled by adding acustomized activity after the Schedule activity. This activity will make a process Shipping 
14-31order API call to update the status to Scheduled. When the ship line logic starts, theorder line status changes to Awaiting Shipping.
• Awaiting Shipping (OM): Order is booked and scheduled but lines have not beenpicked. This status is also displayed after the line has been ship confirmed but beforethe Order Management interface has been run.
• Picked (OM): Order is booked and lines are picked.
• Open (OM): This status of a delivery on the Additional Line Information formindicates that none of the delivery lines associated with that delivery have beenship confirmed.Navigating to Shipping Execution, the delivery line status flow is:
• Ready to Release (SE): Order line is booked and passed to Shipping Execution. Itis now a delivery line that is eligible for Pick Release.
• Not Ready to Release (SE): A delivery line might be in this status when it is interfacedmanually into Shipping Execution, is not scheduled, and has no reservations. Whenlines are imported automatically from Order Management this status is not used.
• Backordered (SE): The delivery line is pick released but no allocations were createdor partial allocations occurred. As an example, if a delivery line has a quantity of100, and at pick release only 25 are available for allocation, the original delivery linesplits to create a new line (quantity of 75) for the unallocated portion with a status ofBackordered. The quantity on the original delivery line changes to 25 to reflect theallocated portion with a status of Staged/Pick Confirmed.
• Staged/Pick Confirmed (SE): The delivery line is successfully pick released. It occursafter pick confirm to indicate subinventory transfer from source location to staginglocation is complete. Lines remain staged until they are ship confirmed.
• Released to Warehouse (SE): Pick Release has started but not completed. Either noallocations were created or allocations have not been Pick Confirmed.Note: Both Backordered and Staged/Pick Confirmed statusesprovide the ability to perform opportunistic cross-docking forwarehouse organizations with Oracle Warehouse Management(WMS) installed.
• Shipped (SE): This line status indicates that the delivery associated with the deliverylines is ship confirmed.
• Interfaced (SE): If delivery was sourced from Oracle OM: The delivery line isshipped and the OM Interface and Inventory Interface concurrent processeshave completed. If delivery was sourced from an Oracle Application other thanOM: The delivery line is shipped and the Inventory Interface concurrent processhas completed.
• Canceled (SE): This status indicates that the delivery line was cancelled.Navigate back to Order Management and query the order which results in OM pullingupdated Pick Release information from Shipping Execution:
• Picked (OM): Pick release has completed normally (both allocation and pickconfirm). The delivery associated with the delivery line(s) may have also been ShipConfirmed but the Delivery may not be set in transit and the Trip may not be closed.14-32 Oracle Order Management Implementation Manual
• Picked Partial (OM): This status occurs when a delivery line is not allocated the fullquantity during Pick Release and Ship Confirm has not occurred.The delivery line splits during Ship Confirm and the information passes toOrder Management through the Process Order API. The order line splits toreflect the changes that occurred during the Shipping process. As an example, acustomer orders quantity 50. There are 20 on hand in inventory. The delivery linesplits into two delivery lines and therefore represents two order lines in OrderManagement. The order line with quantity 20 has the status of Picked or Shippeddepending on whether or not the delivery line is Ship Confirmed, the Delivery set intransit, and the Trip closed. The second order line with a quantity of 30 has statusof Awaiting Shipping.Shipping Execution passes the status information to Order Management when ShipConfirm is complete:
• Shipped (OM): The delivery associated with the line is Ship Confirmed. TheDelivery status is set to In transit. This status appears at the line level as well as inthe Additional Line Information at the Pick Status field.
• Awaiting Shipping (OM): Awaiting information from shipping. This status willremain until the Order Management interface is run.
• Awaiting Fulfillment (OM): Not all shippable lines in a fulfillment set or aconfiguration are fulfilled. The current line is waiting for other lines in the fulfillmentset or the configuration to be fulfilled. This is a synchronization step within theworkflow process.
• Fulfilled (OM): All lines in a fulfillment set are fulfilled.Fulfillment Sets are defined as a group of order lines that are fulfilled together. Itemsthat are not shippable can be in fulfillment sets with shippable items, and then willnot be fulfilled (and therefore invoiced) until the shippable items are fulfilled. A linecan belong to either a ship set or an arrival set, but can belong to multiple fulfillmentsets.
• Interfaced to Receivables (OM): Order Management has written information tothe Receivables Interface tables. You should run Auto Invoice (from Receivables)to generate the Invoice.
• Partially Interfaced to Receivables (OM): This status is used in a PTO flow andindicates that the particular PTO item is required for revenue.
• Closed (OM): Closed indicates that the line is closed. It does not necessarily indicatethat the line is interfaced to Accounts Receivable (AR) since you must close lineactivity in a no-bill flow.
• Canceled (OM): Indicates that the line is completely canceled. No further processing will occur for this line.
Posted by Sairam Goud at 2:12 PM 

Query to get Business Group, Legal Entity Name, Operating Unit Details

SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

Drop Ship PO

Q1. What is a Drop Ship PO?
A: Oracle Order Management and Oracle Purchasing integrate to provide Drop Shipments. Drop Shipments are orders for items that your supplier ships directly to the customer either because you don't stock or currently don't have the items in inventory, or because it's more cost effective for the supplier to ship the item to the customer directly. Drop Shipment was introduced in R11.

Q2. How is a Drop Ship PO created?
A: Drop Shipments are created as Sales Orders in Order Management. The Purchase Release concurrent program or workflow in Order Management creates rows in the Requisition Import tables in Purchasing. Then Purchasing's Requisition Import process creates the requisitions. Drop Shipments are marked with the Source Type of External in Order Management and Supplier in Purchasing.

Q3. What is the setup required for Drop Ship PO?
A: ITEM ATTRIBUTES:
Navigate: Inventory -> Items - > Organization items
Purchased (PO) Enabled
Purchasable (PO) Enabled
Transactable (INV) Enabled
Stockable (INV) Optional
Reservable (INV) Optional
Inventory Item (INV) Optional
Customer Ordered (OM) Enabled
Customer Orders Enabled (OM) Enabled
Internal Ordered (OM) Disabled
Internal Orders Enabled (OM) Disabled
Shippable (OM) Optional
OE Transactable (OM) Enabled
All Drop Ship items must be defined in the organization entered in the profile option OE: Item Validation Organization and in the Receiving Organization.
All Drop Ship sub-inventory must have Reservable box checked. If the sub-inventory is not Reservable the Sales Order issue transaction will not be created in MTL_TRANSACTIONS_INTERFACE. After Drop Ship inventory organization is created, subinventories should be defined. To create the subinventory, go to an inventory responsibility and navigate to Setup -> Organizations -> Subinventories. Asset subinventories must have the reservable and Asset boxes checked. Expense subinventories must have the Reservable box checked and the Asset box unchecked.
Subinventory Attributes for Asset Subinventory
Reservable/Allow Reservations
Asset Subinventory
Subinventory Attributes for Expense Subinventory
Reservable
Asset-must NOT be enabled.

Q4. How can we avoid the miscounting of supply as logical organization is involved?
A: You must receive drop-ship items in a logical organization. If you use Oracle master Scheduling/MRP and Oracle Supply Chain Planning, to avoid miscounting supply you may not want to include logical organizations in your planning. If you choose to include logical organizations, ensure that doing so does not cause planning and forecasting complications.

Q5. If you make changes to a Sales Order after the Purchase Order (PO) has been generated, will the order changes automatically be updated on the PO?
A: Order changes will not be automatically updated on the PO. Pulling up the Discrepancy report will allow you to view the differences between the Sales Order and PO. However, you will have to manually update the POs in the Purchasing application. Please note: Sales Order changes will be automatically updated on the PO if the PO Status is Incomplete or Require Re-approval. If the PO is already approved, you cannot make changes to the Sales Order line (without un-approving the PO).

Q6. If items on a Drop Ship order are cancelled, does the system automatically generate a PO Change to the PO originally sent to the supplier?
A: No, Drop Ship functionality in this regard remains the same as in R11. There is a discrepancy report available that will report differences between the PO and the Sales Order.

Q7. Does Order Management 11i have functionality to do serial number management with Drop Shipments?
A: You are able to receive serial numbered Drop Ship stock. Order Management will receive the serial number noted on the PO.

Q8. Can Configurable Items be Drop Shipped?
A: Currently only Standard Items can be Drop Shipped. Functionality for Configurable Items will be added in future releases.


Q9. How do I Drop Ship across operating units?
A: Release 11i does not currently support this functionality.

Q10. How are over/under shipments handled in Drop Shipment?
A: If part of a drop-ship line ships, and you do not wish to fulfill the remaining quantity, cancel the line. Over shipments must also be handled manually. If the supplier ships more than the ordered quantity, you can bill your customer for the additional quantity or request that they return the item. Use the Drop Ship Order Discrepancy Report to view differences between your drop-ship Sales Orders and their associated purchase requisitions and orders.

Q11. Will Blanket PO's work with Drop Shipment?
A: Blanket PO's will not work with Drop Shipment because the PO must be created when OM notifies PO that a Drop Ship order has been created. This PO is linked to the Drop Ship order so that when the receipt is done (partial or complete) .OM is updated to receiving interface eligible. Drop Ship lines do not use the pick release, ship confirm or inv interface order cycles.

Q12. Is it possible to create releases based on drop shipment orders for the items in the blanket agreement when a valid Blanket PO is created ?
A: Yes, it is possible to create releases from drop ship orders automatically or via Autocreate window, if the drop ship Requisition has the correct Blanket Information (Source)

1.Set up the ASL entry for the supplier site desired.
2.Depending on the hierachy determine which assignment  better suits and define sourcing rule according to the required level with   the required supplier,site.

Also ensure that ASL entry exists for the above supplier/site.

For automatic sourcing to be done also set profile  'PO:Allow Automatic Sourcing' to Yes so that the source document will be determined automatically depending on the creation dates and the type of the document.

Q13. Can we cancel Drop Shipment after it is received?
A: Drop Shipments cannot be cancelled once Oracle Purchasing obtains the receipt. A user who wants to cancel a Drop Ship Sales Order line must ensure no receipts have been created against the line and that the requisition and/or Purchase Order associated with the line is cancelled. Cancellation of a Partial Drop Ship receipt is allowable. But only the portion that has not been received can be cancelled. If you cancel a Drop Shipment line for which you have not shipped the entire quantity, the order processing splits the line. The first line contains the quantity shipped and the second line contains the non-shipped quantity in backorder. You can cancel the second line the backorder on the Sales Order. The PO line quantity should be changed to reflect the new quantity.


Q14. What debugging tools are available for Drop Shipments?
A: 1. Note 133464.1 contains a diagnostic script that can be used for troubleshooting problems with Sales Orders.
2. Debugging receipt transaction or the Sales Order issue transaction, Set the following profile options:
RCV: Processing Mode to Immediate or Batch
RCV: Debug Mode to Yes
OM: Debug Level to 5
INV: Debug Trace to Yes
INV: Debug level to 10
TP: INV Transaction processing mode to Background
-Then go to Sys Admin: Concurrent: Program: Define; query up the Receiving Transaction Processor and check the Enable Trace box.
-Save the receipt for the deliver transaction (destination type will say Inventory for the deliver transaction).
-View the Receiving Transaction Processor log file, the Inventory Transaction Worker log file, as well as, the trace for the errors.


Q15. What is the Import source and status of PO generated from Drop Shipment?
A: Import source is Order Entry.
Status of PO will always be Approved.

Q16. Can we receive a PO Shipment for which the related Sales Order Line is Cancelled / Closed?
A: PO Shipments for which the relates Sales Order line has been cancelled or closed cannot be received. The PO shipment should be cancelled. A new Sales Order Line should be created, if needed.

SQL Query to print Column Values in Row wise

SELECT
   XXHeader_seq,JOB1,value
FROM
   Q_GTF_AUTOCLAVE_LOAD_DATA_V
UNPIVOT
EXCLUDE NULLS
(
   VALUE
   FOR
     JOB1
   IN
   (
      XX_JOB_1,
      XX_JOB_2,
      XX_JOB_3,
      XX_JOB_4,
      XX_JOB_5
   )
)
WHERE
   XXHeader_seq=8980;
   
   
   select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;