Oracle Apps SQL : Requisition-PO-Receipt-Invoice Number

SELECT
DISTINCT
  r.segment1 "Req Num",
  p.segment1 "PO Num",
  rsh.receipt_num "Receipt Num",
  ai.invoice_num "Invoice Num",
  pah.ACTION_DATE
FROM po_req_distributions_all rd,
  po_requisition_lines_all rl,
  po_requisition_headers_all r ,
  po_headers_all p,
  po_distributions_all d,
  RCV_SHIPMENT_HEADERS rsh,
  rcv_shipment_lines rsl,
  ap_invoice_distributions_all apid,
  ap_invoices_all ai,
  PO_ACTION_HISTORY pah
WHERE r.requisition_header_id =rl.requisition_header_id
AND rl.requisition_line_id    = rd.requisition_line_id
AND rd.distribution_id        =d.req_distribution_id (+)
AND d.po_header_id            =p.po_header_id (+)
AND d.po_distribution_id      =apid.po_distribution_id (+)
AND apid.invoice_id           = ai.invoice_id (+)
AND p.po_header_id            =rsl.po_header_id (+)
AND rsl.shipment_header_id    = rsh.shipment_header_id (+)
AND r.requisition_header_id   = pah.object_id (+)
AND pah.OBJECT_TYPE_CODE      = 'REQUISITION'
AND pah.ACTION_CODE           = 'APPROVE';

Oracles apps PO table joins

Stage 1: PO Creation :

PO_HEADERS_ALL

select po_header_id from po_headers_all where segment1 =;

select * from po_headers_all where po_header_id =;

po_lines_all

select * from po_lines_all where po_header_id =;

po_line_locations_all

select * from po_line_locations_all where po_header_id =;

po_distributions_all

select * from po_distributions_all where po_header_id =;

po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =;

Stage 2: Once PO is received data is moved to respective receving tables and inventory tables

RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =);

RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =;

RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =;

RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =);

RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =;

MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =);

AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =));

Stage 4 : Many Time there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id =  );

Stage 5 : General Ledger

Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN ('');

GL_INTERFACE

SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id ));

GL_IMPORT_REFERENCES

SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =)

The consultant life while working at client site is not easy during ERP transformation projects, many times it's required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team.

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

---used to list all Internal Requisitions that do not have an  associated Internal Sales order
Select RQH.SEGMENT1 REQ_NUM,
RQL.LINE_NUM,
RQL.REQUISITION_HEADER_ID ,
RQL.REQUISITION_LINE_ID,
RQL.ITEM_ID ,
RQL.UNIT_MEAS_LOOKUP_CODE ,
RQL.UNIT_PRICE ,
RQL.QUANTITY ,
RQL.QUANTITY_CANCELLED,
RQL.QUANTITY_DELIVERED ,
RQL.CANCEL_FLAG ,
RQL.SOURCE_TYPE_CODE ,
RQL.SOURCE_ORGANIZATION_ID ,
RQL.DESTINATION_ORGANIZATION_ID,
RQH.TRANSFERRED_TO_OE_FLAG
from
PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
where
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
and RQL.SOURCE_TYPE_CODE = 'INVENTORY'
and RQL.SOURCE_ORGANIZATION_ID is not null
and not exists (select 'existing internal order'
from OE_ORDER_LINES_ALL LIN
where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )

-----Relation with Requistion and PO
select r.segment1 "Req Num",
       p.segment1 "PO Num"
from po_headers_all p, 
po_distributions_all d,
po_req_distributions_all rd, 
po_requisition_lines_all rl,
po_requisition_headers_all r 
where p.po_header_id = d.po_header_id 
and d.req_distribution_id = rd.distribution_id 
and rd.requisition_line_id = rl.requisition_line_id 
and rl.requisition_header_id = r.requisition_header_id 

3. You need to list out all cancel Requisitions

-----list My cancel Requistion
select prh.REQUISITION_HEADER_ID,
      prh.PREPARER_ID ,
      prh.SEGMENT1 "REQ NUM",
      trunc(prh.CREATION_DATE),
      prh.DESCRIPTION,
      prh.NOTE_TO_AUTHORIZER
from apps.Po_Requisition_headers_all prh,
     apps.po_action_history pah 
where Action_code='CANCEL' 
and pah.object_type_code='REQUISITION' 
and pah.object_id=prh.REQUISITION_HEADER_ID 

4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)

-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.
  select 
  prh.segment1 "PR NUM", 
  trunc(prh.creation_date) "CREATED ON", 
  trunc(prl.creation_date) "Line Creation Date" ,
  prl.line_num "Seq #", 
  msi.segment1 "Item Num", 
  prl.item_description "Description", 
  prl.quantity "Qty", 
  trunc(prl.need_by_date) "Required By", 
  ppf1.full_name "REQUESTOR", 
  ppf2.agent_name "BUYER" 
  from 
  po.po_requisition_headers_all prh, 
  po.po_requisition_lines_all prl, 
  apps.per_people_f ppf1, 
  (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, 
  po.po_req_distributions_all prd, 
  inv.mtl_system_items_b msi, 
  po.po_line_locations_all pll, 
  po.po_lines_all pl, 
  po.po_headers_all ph 
  WHERE 
  prh.requisition_header_id = prl.requisition_header_id 
  and prl.requisition_line_id = prd.requisition_line_id 
  and ppf1.person_id = prh.preparer_id 
  and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date 
  and ppf2.agent_id(+) = msi.buyer_id 
  and msi.inventory_item_id = prl.item_id 
  and msi.organization_id = prl.destination_organization_id 
  and pll.line_location_id(+) = prl.line_location_id 
  and pll.po_header_id = ph.po_header_id(+) 
  AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) 
  AND PRH.AUTHORIZATION_STATUS = 'APPROVED' 
  AND PLL.LINE_LOCATION_ID IS NULL 
  AND PRL.CLOSED_CODE IS NULL 
  AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
  ORDER BY 1,2

5. You need to list all information form PR to PO ...as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.

----- List and all data entry from PR till PO

select distinct u.description "Requestor", 
porh.segment1 as "Req Number", 
trunc(porh.Creation_Date) "Created On", 
pord.LAST_UPDATED_BY, 
porh.Authorization_Status "Status", 
porh.Description "Description", 
poh.segment1 "PO Number", 
trunc(poh.Creation_date) "PO Creation Date", 
poh.AUTHORIZATION_STATUS "PO Status", 
trunc(poh.Approved_Date) "Approved Date"
from apps.po_headers_all poh, 
apps.po_distributions_all pod, 
apps.po_req_distributions_all pord, 
apps.po_requisition_lines_all porl, 
apps.po_requisition_headers_all porh, 
apps.fnd_user u 
where porh.requisition_header_id = porl.requisition_header_id 
and porl.requisition_line_id = pord.requisition_line_id 
and pord.distribution_id = pod.req_distribution_id(+) 
and pod.po_header_id = poh.po_header_id(+) 
and porh.created_by = u.user_id
order by 2 

6.Identifying all PO's which does not have any PR's

-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.
  select 
  prh.segment1 "PR NUM", 
  trunc(prh.creation_date) "CREATED ON", 
  trunc(prl.creation_date) "Line Creation Date" ,
  prl.line_num "Seq #", 
  msi.segment1 "Item Num", 
  prl.item_description "Description", 
  prl.quantity "Qty", 
  trunc(prl.need_by_date) "Required By", 
  ppf1.full_name "REQUESTOR", 
  ppf2.agent_name "BUYER" 
  from 
  po.po_requisition_headers_all prh, 
  po.po_requisition_lines_all prl, 
  apps.per_people_f ppf1, 
  (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, 
  po.po_req_distributions_all prd, 
  inv.mtl_system_items_b msi, 
  po.po_line_locations_all pll, 
  po.po_lines_all pl, 
  po.po_headers_all ph 
  WHERE 
  prh.requisition_header_id = prl.requisition_header_id 
  and prl.requisition_line_id = prd.requisition_line_id 
  and ppf1.person_id = prh.preparer_id 
  and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date 
  and ppf2.agent_id(+) = msi.buyer_id 
  and msi.inventory_item_id = prl.item_id 
  and msi.organization_id = prl.destination_organization_id 
  and pll.line_location_id(+) = prl.line_location_id 
  and pll.po_header_id = ph.po_header_id(+) 
  AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) 
  AND PRH.AUTHORIZATION_STATUS = 'APPROVED' 
  AND PLL.LINE_LOCATION_ID IS NULL 
  AND PRL.CLOSED_CODE IS NULL 
  AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
  ORDER BY 1,2

7. Relation between Requisition and PO tables

Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

8.You need to find table which hold PO Approval path...

These two table keeps the data:

• PO_APPROVAL_LIST_HEADERS
• PO_APPROVAL_LIST_LINES

9. List all the PO's with there approval ,invoice and Payment Details

List PO's with Approval , invoice and Payment info

10.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..

The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.

The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.

These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is
Purchasing.

Budgetary Records from PO (These include reservations, reversals and cancellations):

• REFERENCE_1- Source (PO or REQ)
• REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
  po_requisition_headers_all.requisition_header_id)
• REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or
  po_req_distributions_all.distribution_id)
• REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or
  po_requisition_headers_all.segment1)
• REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)

Accrual Records from PO:

• REFERENCE_1- Source (PO)
• REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
• REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
• REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
• REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)

Take a note for Period end accruals, the REFERENCE_5 column is not used.

11. List me all open PO's

12.There are different authorization_status can a requisition have.

• Approved
• Cancelled
• In Process
• Incomplete
• Pre-Approved
• Rejected

and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'.

----- List all open PO'S
select 
h.segment1 "PO NUM", 
h.authorization_status "STATUS", 
l.line_num "SEQ NUM", 
ll.line_location_id, 
d.po_distribution_id , 
h.type_lookup_code "TYPE" 
from 
po.po_headers_all h, 
po.po_lines_all l, 
po.po_line_locations_all ll, 
po.po_distributions_all d 
where h.po_header_id = l.po_header_id 
and ll.po_line_id = l.po_Line_id 
and ll.line_location_id = d.line_location_id 
and h.closed_date is null 
and h.type_lookup_code not in ('QUOTATION') 

13. A standard Quotations one that you can tie back to a PO.
Navigate to RFQ -> Auto create -> enter a PO and reference it back.

14. I want to debug for a PO , where should I start.

Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.

Purchase Requisition details

SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",

prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,

prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,

prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,

prl.cancel_reason

FROM po_requisition_headers_all prh,

po_requisition_lines_all prl,

po_req_distributions_all prd,

per_people_x ppx,

po_headers_all poh,

po_distributions_all pda

WHERE prh.requisition_header_id = prl.requisition_header_id

AND ppx.person_id = prh.preparer_id

AND prh.type_lookup_code = 'PURCHASE'

AND prd.requisition_line_id = prl.requisition_line_id

AND pda.req_distribution_id = prd.distribution_id

AND pda.po_header_id = poh.po_header_id







Internal Requisition details

SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name", prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prl.line_num, prl.line_type_id, prl.source_type_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered, prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason FROM po_requisition_headers_all prh, po_requisition_lines_all prl, po_req_distributions_all prd, per_people_x ppx, po_headers_all poh, po_distributions_all pda WHERE prh.requisition_header_id = prl.requisition_header_id AND ppx.person_id = prh.preparer_id AND prh.type_lookup_code = 'INTERNAL' AND prd.requisition_line_id = prl.requisition_line_id AND pda.req_distribution_id (+) = prd.distribution_id AND pda.po_header_id = poh.po_header_id (+) Purchase Order details -- Purchase Orders for non inventory items like service SELECT ph.SEGMENT1 po_num , ph.CREATION_DATE , hou.name "Operating Unit" , ppx.full_name "Buyer Name" , ph.type_lookup_code "PO Type" , plc.displayed_field "PO Status" , ph.COMMENTS , pl.line_num , plt.order_type_lookup_code "Line Type" , NULL "Item Code" , pl.item_description , pl.unit_meas_lookup_code "UOM" , pl.base_unit_price , pl.unit_price , pl.quantity , ood.organization_code "Shipment Org Code" , ood.organization_name "Shipment Org Name" , pv.vendor_name supplier , pvs.vendor_site_code , (pl.unit_price * pl.quantity) "Line Amount" , prh.segment1 req_num , prh.type_lookup_code req_method , ppx1.full_name "Requisition requestor" FROM po_headers_all ph , po_lines_all pl , po_distributions_all pda , po_vendors pv , po_vendor_sites_all pvs , po_distributions_all pd , po_req_distributions_all prd , po_requisition_lines_all prl , po_requisition_headers_all prh , hr_operating_units hou , per_people_x ppx , po_line_types_b plt , org_organization_definitions ood , per_people_x ppx1 , po_lookup_codes plc WHERE 1=1 AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011) AND ph.vendor_id = pv.vendor_id AND ph.po_header_id = pl.po_header_id AND ph.vendor_site_id = pvs.vendor_site_id AND ph.po_header_id = pd.po_header_id and pl.po_line_id = pd.po_line_id AND pd.req_distribution_id = prd.distribution_id (+) AND prd.requisition_line_id = prl.requisition_line_id (+) AND prl.requisition_header_id = prh.requisition_header_id (+) and hou.organization_id = ph.org_id and ph.agent_id = ppx.person_id and pda.po_header_id = ph.po_header_id and pda.po_line_id = pl.po_line_id and pl.line_type_id = plt.line_type_id and ood.organization_id = pda.destination_organization_id and ppx1.person_id (+) = prh.preparer_id and plc.lookup_type = 'DOCUMENT STATE' and plc.LOOKUP_CODE = ph.closed_code and pl.item_id is null UNION -- Purchase Orders for inventory items SELECT ph.SEGMENT1 po_num , ph.CREATION_DATE , hou.name "Operating Unit" , ppx.full_name "Buyer Name" , ph.type_lookup_code "PO Type" , plc.displayed_field "PO Status" , ph.COMMENTS , pl.line_num , plt.order_type_lookup_code "Line Type" , msi.segment1 "Item Code" , pl.item_description , pl.unit_meas_lookup_code "UOM" , pl.base_unit_price , pl.unit_price , pl.quantity , ood.organization_code "Shipment Org Code" , ood.organization_name "Shipment Org Name" , pv.vendor_name supplier , pvs.vendor_site_code , (pl.unit_price * pl.quantity) "Line Amount" , prh.segment1 req_num , prh.type_lookup_code req_method , ppx1.full_name "Requisition requestor" FROM po_headers_all ph , po_lines_all pl , po_distributions_all pda , po_vendors pv , po_vendor_sites_all pvs , po_distributions_all pd , po_req_distributions_all prd , po_requisition_lines_all prl , po_requisition_headers_all prh , hr_operating_units hou , per_people_x ppx , mtl_system_items_b msi , po_line_types_b plt , org_organization_definitions ood , per_people_x ppx1 , po_lookup_codes plc WHERE 1=1 AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011) AND ph.vendor_id = pv.vendor_id AND ph.po_header_id = pl.po_header_id AND ph.vendor_site_id = pvs.vendor_site_id AND ph.po_header_id = pd.po_header_id and pl.po_line_id = pd.po_line_id AND pd.req_distribution_id = prd.distribution_id (+) AND prd.requisition_line_id = prl.requisition_line_id (+) AND prl.requisition_header_id = prh.requisition_header_id (+) and hou.organization_id = ph.org_id and ph.agent_id = ppx.person_id and pda.po_header_id = ph.po_header_id and pda.po_line_id = pl.po_line_id and pl.line_type_id = plt.line_type_id and ood.organization_id = pda.destination_organization_id and ppx1.person_id (+) = prh.preparer_id and pda.destination_organization_id = msi.organization_id (+) and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)-- OR pl.item_id is null) and plc.lookup_type = 'DOCUMENT STATE' and plc.LOOKUP_CODE = ph.closed_code and pl.item_id is not null

On BULK COLLECT-Oracle PL/SQL

Best practices for knowing your LIMIT and kicking %NOTFOUND



I have started using BULK COLLECT whenever I need to fetch large volumes of data. This has caused me some trouble with my DBA, however. He is complaining that although my programs might be running much faster, they are also consuming way too much memory. He refuses to approve them for a production rollout. What's a programmer to do?

The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory."

Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.

Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.

Suppose I need to retrieve all the rows from the employees table and then perform some compensation analysis on each row. I can use BULK COLLECT as follows:

PROCEDURE process_all_rows

IS

TYPE employees_aat

IS

  TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;

  l_employees employees_aat;

BEGIN

  SELECT * BULK COLLECT INTO l_employees FROM employees;

  

  FOR indx IN 1 .. l_employees.COUNT

  LOOP

    analyze_compensation (l_employees(indx));

  END LOOP;

END process_all_rows;

Very concise, elegant, and efficient code. If, however, my employees table contains tens of thousands of rows, each of which contains hundreds of columns, this program can cause excessive PGA memory consumption.

Consequently, you should avoid this sort of "unlimited" use of BULK COLLECT. Instead, move the SELECT statement into an explicit cursor declaration and then use a simple loop to fetch many, but not all, rows from the table with each execution of the loop body, as shown in Listing 1.

Code Listing 1: Using BULK COLLECT with LIMIT clause

PROCEDURE process_all_rows

  (

    limit_in IN PLS_INTEGER DEFAULT 100)

             IS

  CURSOR employees_cur

  IS

    SELECT * FROM employees;

TYPE employees_aat

IS

  TABLE OF employees_cur%ROWTYPE INDEX BY PLS_INTEGER;

  l_employees employees_aat;

BEGIN

  OPEN employees_cur;

  LOOP

    FETCH employees_cur BULK COLLECT INTO l_employees LIMIT limit_in;

    

    FOR indx IN 1 .. l_employees.COUNT

    LOOP

      analyze_compensation (l_employees(indx));

    END LOOP;

    EXIT

  WHEN l_employees.COUNT < limit_in;

  END LOOP;

  CLOSE employees_cur;

END process_all_rows;

The process_all_rows procedure in Listing 1 requests that up to the value of limit_in rows be fetched at a time. PL/SQL will reuse the same limit_in elements in the collection each time the data is fetched and thus also reuse the same memory. Even if my table grows in size, the PGA consumption will remain stable.

How do you decide what number to use in the LIMIT clause? Theoretically, you will want to figure out how much memory you can afford to consume in the PGA and then adjust the limit to be as close to that amount as possible.

From tests I (and others) have performed, however, it appears that you will see roughly the same performance no matter what value you choose for the limit, as long as it is at least 25. The test_diff_limits.sql script, included with the sample code for this column, demonstrates this behavior, using the ALL_SOURCE data dictionary view on an Oracle Database 11g instance. Here are the results I saw (in hundredths of seconds) when fetching all the rows (a total of 470,000):

Elapsed CPU time for limit of 1 = 1839 Elapsed CPU time for limit of 5 = 716 Elapsed CPU time for limit of 25 = 539 Elapsed CPU time for limit of 50 = 545 Elapsed CPU time for limit of 75 = 489 Elapsed CPU time for limit of 100 = 490 Elapsed CPU time for limit of 1000 = 501 Elapsed CPU time for limit of 10000 = 478 Elapsed CPU time for limit of 100000 = 527


Kicking the %NOTFOUND Habit

I was very happy to learn that Oracle Database 10g will automatically optimize my cursor FOR loops to perform at speeds comparable to BULK COLLECT. Unfortunately, my company is still running on Oracle9i Database, so I have started converting my cursor FOR loops to BULK COLLECTs. I have run into a problem: I am using a LIMIT of 100, and my query retrieves a total of 227 rows, but my program processes only 200 of them. [The query is shown in Listing 2.] What am I doing wrong?

Code Listing 2: BULK COLLECT, %NOTFOUND, and missing rows

PROCEDURE process_all_rows

IS

  CURSOR table_with_227_rows_cur

  IS

    SELECT * FROM table_with_227_rows;

TYPE table_with_227_rows_aat

IS

  TABLE OF table_with_227_rows_cur%ROWTYPE INDEX BY PLS_INTEGER;

  l_table_with_227_rows table_with_227_rows_aat;

BEGIN

  OPEN table_with_227_rows_cur;

  LOOP

    FETCH table_with_227_rows_cur BULK COLLECT

    INTO l_table_with_227_rows LIMIT 100;

    

    EXIT

  WHEN table_with_227_rows_cur%NOTFOUND;

    /* cause of missing rows */

    FOR indx IN 1 .. l_table_with_227_rows.COUNT

    LOOP

      analyze_compensation (l_table_with_227_rows(indx));

    END LOOP;

  END LOOP;

  CLOSE table_with_227_rows_cur;

END process_all_rows;

You came so close to a completely correct conversion from your cursor FOR loop to BULK COLLECT! Your only mistake was that you didn't give up the habit of using the %NOTFOUND cursor attribute in your EXIT WHEN clause.

The statement

EXIT WHEN table_with_227_rows_cur%NOTFOUND;


makes perfect sense when you are fetching your data one row at a time. With BULK COLLECT, however, that line of code can result in incomplete data processing, precisely as you described.

Let's examine what is happening when you run your program and why those last 27 rows are left out. After opening the cursor and entering the loop, here is what occurs:

1. The fetch statement retrieves rows 1 through 100.
2. table_with_227_rows_cur%NOTFOUND evaluates to FALSE, and the rows are processed.
3. The fetch statement retrieves rows 101 through 200.
4. table_with_227_rows_cur%NOTFOUND evaluates to FALSE, and the rows are processed.
5. The fetch statement retrieves rows 201 through 227.
6. table_with_227_rows_cur%NOTFOUND evaluates to TRUE , and the loop is terminated—with 27 rows left to process!



When you are using BULK COLLECT and collections to fetch data from your cursor, you should never rely on the cursor attributes to decide whether to terminate your loop and data processing.


So, to make sure that your query processes all 227 rows, replace this statement:

EXIT WHEN table_with_227_rows_cur%NOTFOUND; 

 with 

 EXIT WHEN l_table_with_227_rows.COUNT = 0;
Generally, you should keep all of the following in mind when working with BULK COLLECT:

• The collection is always filled sequentially, starting from index value 1.
• It is always safe (that is, you will never raise a NO_DATA_FOUND exception) to iterate through a collection from 1 to collection .COUNT when it has been filled with BULK COLLECT.
• The collection is empty when no rows are fetched.
• Always check the contents of the collection (with the COUNT method) to see if there are more rows to process.
• Ignore the values returned by the cursor attributes, especially %NOTFOUND.

SQL Developer - Full Pathname for java.exe - Doesn't Stick in Windows

If SQL Developer prompts you for JDK path (please specify the path to the java jdk home) then do the following.

Solution,


Try running it "as administrator" and set the path this will preserve the path in the configuration file.


or


Alternatively you can edit \sqldeveloper\bin\sqldeveloper.conf and add SetJavaHome C:\Java (for example)

Antenna maker launches website that locates OTA broadcasters, USA

A new website called TV-For-Free has been launched to help viewers find free network and local television stations for viewing and also helps them determine the right kind of antenna with which to view those programs.

Mohu, a provider of OTA TV antennas, produced the new locator, called TV-For-Free, to offer free alternatives to more expensive pay television programming. It was designed to help viewers determine locate free programming and to determine the best ways to access those programs.

The locator is accessible at www.GoMohu.com/TV-For-Free. Mohu said until now searching for a listing of free broadcast TV channels in a specific location and figuring out the right antenna to buy was often a complex and inaccurate process.

TV-For-Free displays available channels and subchannels, including those local stations broadcasting in HDTV. The tool taps into data from the more than 1800 broadcast towers located across all 50 U.S. states. In addition, the tool helps determine the right kind of antenna consumers will need in order to receive the channels they want.

By entering their ZIP code or actual street address, people can view a map with detailed information on which stations are available and the distance of broadcast towers from their location. TV-For-Free also offers a way to match people with the proper antenna to help them watch the channels they want. The tool was developed by Mohu, which also markets a line of OTA HDTV antennas, such as the Mohu Leaf and SkyHDTV.“Twenty-two of the top 25 most popular television programs in the United States are broadcast over-the-air for free,” said Mark Buff, co-founder and president of Mohu. “A quick search on TV-For-Free allows anyone to find the stations available to them, which is a great help when looking for ways to trim cable or satellite bills.”

TV-For-Free currently provides information on local OTA TV broadcast reception in all 50 U.S. states and will add coverage for Canada in the coming months.

Oracle Pricing APIs

QP_Price_formula_PUB.Get_Price_Formula (Formula Calculation API): The Formula Calculation package consists of entities to calculate the value of a formula.

QP_Price_formula_PUB.Process_Price_Formula (Update Formula Prices API): The Update Formula Prices package consists of entities to update formula prices.

QP_CUSTOM.Get_Custom_Price (Get Custom Price API): You may add custom code to this customizable function. The pricing engine while evaluating a formula that contains a formula line (step) of type "function" calls this API.

QP_PREQ_GRP.Price_Request (Price Request API): The Price Request Application Program Interface (API) is a public API that allows you to get a base price and to apply price adjustments, other benefits, and charges to a transaction.

QP_MODIFIERS_PUB.Process_Modifiers (Business Object for Modifier Setup API): The Business Object for Modifier Setup package consists of entities to set up modifiers.

QP_QUALIFIER_RULES_PUB.Process_Qualifier_Rules (Qualifiers API): The Qualifiers package consists of entities to set up qualifiers.

QP_ATTR_MAPPING_PUB. Build_Contexts (Attribute Mapping API): The Attribute Mapping package consists of entities to map attributes.

QP_Price_List_PUB.Process_Price_List (Price List Setup API): The Price List Setup package consists of entities to set up price lists.

Oracle API Availability -Purchasing

To get the brief idea about some available standard available Oracle Application interfaces applicable within oracle purchasing modules and hopefully it should serve to familiarize individuals with limited knowledge of Oracle's API functionality.


1. Open Requisition Interface


What you can do:

By this you can Automatically import requisitions from other oracle Application system or any other system using this interface. This allows you to integrate your oracle purchasing application with new or existing applications, such as material requirement planning, inventory management.etc.


What tables involved
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL


2. Requisition Reschedule


What you can do:


This is required when you are having Oracle Master Scheduling/MRP or a non-Oracle MRP system integrated with your oracle Purchasing, you may find that you need to reschedule requisitions as your

Planning requirements change. This API'S lets you reschedule requisition lines according to changes in your planned orders.


What tables involved
PO_RESCHEDULE_INTERFACE


3. Purchasing Documents Open Interface








What you can do:


You can Automatically import and update standard purchase orders, price/sales catalog information, and responses to request for Quotations(RFQ's) from suppliers through this interface. This interface uses the API's to process document data in the oracle applications interface table to ensure that it is valid before importing it into oracle purchasing. After the data is validated, the program converts the information in the interface table into the appropriate document in purchasing.


What tables involved
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE


4. Receiving Open Interface


What you can do:


You can Automatically import receipt information from other oracle applications or other system using the receiving open interface. This interface lets you integrate your oracle purchasing applications with new or existing applications. E.g. you can load bar-coded and other receiving information from scanners.

More over the good things is that you can also bring Advance Shipment Notices (ASNs) sent from suppliers by this interface.


What tables involved
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE


What you can't done by this API's
Serial numbering
Separate receive and deliver
Corrections
Returns
Movement statistics
Dynamic locators
Receiving against Internal Orders
Receiving against Inter-Organization transfers
Receiving against Drop Ship Orders
Receiving against RMAs


For more information for these three interface, you can refer these documenst.