Query : To find PO corrections

execute fnd_client_info.set_org_context('ORG_ID');

SELECT pol.po_header_id,pol.po_line_id, rct.po_line_location_id Line_location_id
, sum (nvl(rct1.source_doc_quantity,0) ) Qty_corrected
from rcv_transactions rct
, rcv_transactions rct1
, po_lines pol
, po_line_locations pll
where rct.transaction_type in ( 'RECEIVE' ,'MATCH')
and rct.po_line_location_id = pll.line_location_id
and rct1.transaction_type = 'CORRECT'
and rct1.parent_transaction_id = rct.transaction_id
and rct1.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id
union all
SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id
, sum (nvl(rct1.amount,0) ) Qty_corrected
from rcv_transactions rct
, rcv_transactions rct1
, po_lines pol
, po_line_locations pll
where rct.transaction_type in ( 'RECEIVE' ,'MATCH')
and rct.po_line_location_id = pll.line_location_id
and rct1.transaction_type = 'CORRECT'
and rct1.parent_transaction_id = rct.transaction_id
and rct1.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id

Read More

INFO: Project Accounting main Interfaces

PRC: Interface Assets to Oracle Assets (PAXCPCAL)
The Interface Assets process sends valid asset lines to Oracle Assets to become fixed assets. The process creates one mass addition line in Oracle Assets for each asset line in Oracle Projects, assigning the asset information you entered for the CIP asset to the mass addition line in Oracle Assets. Interface the costs to General Ledger before you run the Interface Assets process.

PRC: Interface Cross Charge Distributions to General Ledger (PACCGLTR)
The process identifies the cross charged transactions that fit the parameters you specify and then interfaces the cross charged distributions to General Ledger.

PRC: Interface Expense Reports from Payables(PAAPIMP)
This process gets expense report information from Payables. This process creates pre- approved expense report batches from expense report information entered in Self-Service Expenses or in the invoices window (in payables).

PRC: Interface Expense Reports to Payables (PATTER)
The Interface Expense Reports to Payables process collects all eligible expense reports, including adjustments, in Oracle Projects and interfaces them to the Oracle Payables interface tables.

PRC: Interface Intercompany Invoices to Receivables (PATTAR-IC)
This process collects all eligible intercompany invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables.

PRC: Interface Invoices to Receivables (PATTAR)
This process collects all eligible draft invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables. The process also maintains the project balances of unbilled receivable and unearned revenue and creates accounting transactions for these amounts.

PRC: Interface Labor Costs to General Ledger (PAGGLT)
This process collects all eligible labor costs in Oracle Projects and interfaces them to the Oracle General Ledger interface tables. The interface process also determines the liability account for the labor costs.

PRC: Interface Revenue to General Ledger (PATTGL)
This process collects all eligible revenue in Oracle Projects and interfaces it to the Oracle General Ledger interface tables. This process also maintains project balances for unbilled receivables and unearned revenue and creates accounting transactions for these amounts.
PRC: Interface Supplier Costs (PAAPIMP_SI)

The PRC: Interface Supplier Invoices from Payables process retrieves the following items and interface them to Oracle Projects: All eligible posted, project related supplier invoices from Oracle Payables, Tax lines for project related intercompany invoices. The process first populates the Transaction Import Interface table, creating a cost distributed expenditure item and cost distribution line for each invoice distribution line, and expenditure for each invoice.

PRC: Interface Supplier Invoice Adjustment Costs to Payables (PAVTVC)
This process collects all eligible supplier invoice adjustment costs in Oracle Projects and interfaces them to Oracle Payables. The process determines the liability account for the supplier invoice costs.
PRC: Interface Total Burdened Cost to GL PACTFTBC This process collects all eligible total burdened distribution lines in Oracle Projects and interfaces them to Oracle General Ledger.

PRC: Interface Usage and Miscellaneous Costs to General Ledger (PASGLT) This process collects all eligible cost distribution lines of the following transactions in Oracle Projects and interfaces them to the Oracle General Ledger interface tables: usage costs, miscellaneous transaction costs, burden transaction costs, and Inventory and WIP transactions not already costed or accounted. The interface process also determines the liability account for these costs.

PRC: Transaction Import PAXTRTRX Transaction Import
This is an open interface that enables you to load transactions from external cost collection systems into Oracle Projects. Transaction Import creates pre-approved expenditure items from transaction data entered in external cost collection systems.

Read More

How : to fix the ieframe.dll error

When you are downloading any software, if you encounter any error like below.

res://ieframe.dll/acr_error.htm
is shown on the address bar

Step 1)
Go to internet
tools, internet options, security , Custom Level

Enable -> ActiveX Controls and Plug ins

Step 2)
Internet Options
Advanced tab Settings
Security->
Uncheck "Enable memory protection to help mitigate online attacks*"

Step 3)
restart internet explorer

Read More

Vendors with no Invoices

select segment1 VENDOR NBR,
vendor_name NAME,
vendor_site_code SITE_CODE,
end_date_active VEND_INACTIVE,
inactive_date SITE_INACTIVE
from po_vendors vend,
po_vendor_sites_all sites,
ap_invoices_all inv
where vend.vendor_id = sites.vendor_id
and sites.vendor_site_id = inv.vendor_site_id(+)
and inv.vendor_site_id is null;

Read More

INFO: Project Accounting main tables

GL_JE_BATCHES, STATUS = 'P', means "Posted".
GL_JE_BATCHES, STATUS = 'U', means unposted.
GL_JE_BATCHES, STATUS = 'S' mean "Selecting".
GL_JE_BATCHES, STATUS = 'A' implies "Funds reservation fails"

Read More

Query : List the orders that have failed in transaction

select order_number
from oks_reprocessing a
where a.success_flag = 'E' and order_number not in (Select nvl(source_header_ref,'11111') from csi_txn_errors)

Read More

How: Need to Update

SELECT a.asset_number,
b.life_in_months LIFE_IN_BOOKS,
m.asset_number,
m.attribute1 LIFE_IN_MASS_ADD
FROM fa_additions a,
fa_books b,
fa_mass_additions m
WHERE a.asset_id = b.asset_id
AND a.asset_number = m.asset_number
AND b.book_type_code LIKE 'BOOK NAME' --- pass the book
AND m.attribute1 IS NOT NULL

once you get , you need to take count
SELECT COUNT(*)
FROM fa_additions a,
fa_books b,
fa_mass_additions m
WHERE a.asset_id = b.asset_id
AND a.asset_number = m.asset_number
AND b.book_type_code LIKE 'BOOK NAME' --- pass the book
AND m.attribute1 IS NOT NULL

and finally here is your scripts for Update
UPDATE fa_books b
SET life_in_months = (
SELECT m.attribute1
FROM fa_mass_additions m, fa_additions a
WHERE a.asset_id = b.asset_id
AND a.asset_number = m.asset_number
AND m.book_type_code LIKE 'BOOK NAME' --- pass the book
AND m.attribute1 IS NOT NULL)
WHERE b.book_type_code LIKE 'BOOK NAME' --- pass the book

Read More