Monthly Archives: March 2015

Oracle R12 – Pre and Post Upgrade – #Document Statistics

SELECT month_col, AP_Invoices,ap_payments, PO_Created, iProc_Reqs, –iExp_Requests,
AR_INVOICES, –Sales_Orders,
GL_Journals FROM (
SELECT ap_month month_col,AP_Invoices,ap_payments, PO_Created, iProc_Reqs, –iExp_Requests,
AR_Invoices, –Sales_Orders,
GL_Journals FROM
(select to_char(trunc(creation_date), ‘Month YYYY’) ap_month, count(*) AP_Invoices from ap.ap_invoices_all
where trunc(creation_date) > ’01-nov-14′
AND trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’)) AP_INVOICE,
(select to_char(trunc(creation_date), ‘Month YYYY’) apay_month, count(*) AP_Payments from ap.ap_checks_all
where void_date is null
and trunc(creation_date) > ’01-nov-14′
AND trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’)) AP_PAYMENT,
(select to_char(trunc(creation_date), ‘Month YYYY’) po_month, count(*) PO_Created from po.po_headers_all
WHERE trunc(creation_date) > ’01-nov-14′
AND trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’)) PO_CREATE,
(select to_char(trunc(creation_date), ‘Month YYYY’) iproc_month, count(*) iProc_Reqs from po.po_requisition_headers_all
where trunc(creation_date) > ’01-nov-14′
and trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’) ) IPROC_REQS,
/* (SELECT TO_CHAR(trunc(creation_date), ‘Month YYYY’) Iexp_MONTH, COUNT(*) iExp_Requests FROM AP.AP_EXPENSE_REPORT_HEADERS_ALL
where trunc(creation_date) > ’01-nov-14′
and trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’)) EXP_REP, */
(select to_char(trunc(creation_date), ‘Month YYYY’) ar_month, count(*) AR_Invoices from ra_customer_trx_all
where trunc(creation_date) > ’01-nov-14′
and trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’)) AR_INVOICE,
/* (select to_char(trunc(creation_date), ‘Month YYYY’) oe_month, count(*) Sales_Orders from oe_order_headers_all
where trunc(creation_date) > ’01-nov-14′
and trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’) ) OE_ORDER,*/
(select to_char(trunc(creation_date), ‘Month YYYY’) gl_month, count(*) GL_Journals from gl_je_headers
where trunc(creation_date) > ’01-nov-14′
and trunc(creation_date) < ’01-mar-15′
GROUP BY TO_CHAR(trunc(creation_date), ‘Month YYYY’) ) GL_HDR
WHERE AP_INVOICE.AP_MONTH = PO_CREATE.PO_MONTH(+)
AND AP_INVOICE.AP_MONTH = IPROC_REQS.iproc_month(+)
–AND AP_INVOICE.AP_MONTH = EXP_REP.IEXP_MONTH(+)
AND AP_INVOICE.AP_MONTH = AR_INVOICE.AR_MONTH(+)
–AND AP_INVOICE.AP_MONTH = OE_ORDER.OE_MONTH(+)
AND AP_INVOICE.AP_MONTH = GL_HDR.GL_MONTH(+)
AND AP_INVOICE.AP_MONTH = ap_payment.APay_MONTH(+))

Oracle R12 – PO_DOC_MANAGER_PUB.CALL_DOC_MANAGER

Issue:

Requisition/PO documents gets approved without checking the authority level.

Issue: PO_DOC_MANAGER_PUB.CALL_DOC_MANAGER has become obsolete in R12

Fix: Use API po_document_action_pvt.verify_authority and check both the columns to determine if workflow activity is completed or not.

IF x_return_status = ‘S’ and x_return_code is null
THEN
RETURN(‘COMPLETE:Y’);
ELSE
RETURN(‘COMPLETE:N’);
END IF;