VIEW: APPS.AP_WFAPPROVAL_HISTORY_V

Source

View Text - Preformatted

(
SELECT DISTINCT HIS.INVOICE_ID,
HIS.LINE_NUMBER,
HIS.RESPONSE,
HIS.APPROVER_ID,
HIS.APPROVER_NAME,
HIS.AMOUNT_APPROVED,
HIS.APPROVER_COMMENTS,
HIS.LAST_UPDATE_DATE,
ALC1.DISPLAYED_FIELD,
HIS.ITERATION,
HIS.HISTORY_TYPE,
ALC2.DISPLAYED_FIELD,
-1
FROM AP_INV_APRVL_HIST_ALL HIS,
     AP_LOOKUP_CODES ALC1,
     AP_LOOKUP_CODES ALC2
WHERE alc1.lookup_code(+) = his.response
AND alc1.lookup_type (+) = 'AP_WFAPPROVAL_STATUS'
AND alc2.lookup_type (+) ='AP_WFAPPROVAL_CONTEXT'
AND alc2.lookup_code (+) = his.history_type
UNION ALL
SELECT AGT.TRANSACTION_ID,
AGT.LINE_NUMBER,
ALC1.LOOKUP_CODE,
AGT.PERSON_ID,
AGT.DISPLAY_NAME,
0,
NULL,
sysdate,
ALC1.DISPLAYED_FIELD,
0,
ALC2.LOOKUP_CODE,
ALC2.DISPLAYED_FIELD,
AGT.APPROVER_ORDER_NUMBER
FROM AP_APPROVERS_LIST_GT AGT,
     AP_LOOKUP_CODES ALC1,   /* Bug 5624200 */
     AP_LOOKUP_CODES ALC2
WHERE AGT.APPROVAL_STATUS IS NULL
AND ALC1.LOOKUP_TYPE = 'NLS TRANSLATION'  /* Bug 5624200 */
AND ALC1.LOOKUP_CODE = 'FUTURE'
AND ALC2.lookup_type (+) ='AP_WFAPPROVAL_CONTEXT'
AND ALC2.lookup_code (+) =
                     decode(agt.item_class, 'header', 'DOCUMENTAPPROVAL', 'LINESAPPROVAL')
/* Bug 8546490*/
AND AGT.orig_system_id not in (SELECT his.approver_id
			FROM AP_INV_APRVL_HIST_ALL his, AP_INVOICES_ALL AI
			WHERE AI.invoice_id = his.invoice_id
                        AND AI.APPROVAL_ITERATION = his.ITERATION
                        AND his.response = 'SENT'
			AND his.invoice_id = transaction_id
                        AND his.history_type =
                     decode(agt.item_class, 'header', 'DOCUMENTAPPROVAL', 'LINESAPPROVAL'))
AND EXISTS (SELECT 'Initiated Invoice'
		FROM AP_INVOICES_ALL ai
		WHERE ai.invoice_id = transaction_id
		AND ai.wfapproval_status = 'INITIATED')
)

View Text - HTML Formatted

( SELECT DISTINCT HIS.INVOICE_ID
, HIS.LINE_NUMBER
, HIS.RESPONSE
, HIS.APPROVER_ID
, HIS.APPROVER_NAME
, HIS.AMOUNT_APPROVED
, HIS.APPROVER_COMMENTS
, HIS.LAST_UPDATE_DATE
, ALC1.DISPLAYED_FIELD
, HIS.ITERATION
, HIS.HISTORY_TYPE
, ALC2.DISPLAYED_FIELD
, -1 FROM AP_INV_APRVL_HIST_ALL HIS
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2 WHERE ALC1.LOOKUP_CODE(+) = HIS.RESPONSE AND ALC1.LOOKUP_TYPE (+) = 'AP_WFAPPROVAL_STATUS' AND ALC2.LOOKUP_TYPE (+) ='AP_WFAPPROVAL_CONTEXT' AND ALC2.LOOKUP_CODE (+) = HIS.HISTORY_TYPE UNION ALL SELECT AGT.TRANSACTION_ID
, AGT.LINE_NUMBER
, ALC1.LOOKUP_CODE
, AGT.PERSON_ID
, AGT.DISPLAY_NAME
, 0
, NULL
, SYSDATE
, ALC1.DISPLAYED_FIELD
, 0
, ALC2.LOOKUP_CODE
, ALC2.DISPLAYED_FIELD
, AGT.APPROVER_ORDER_NUMBER FROM AP_APPROVERS_LIST_GT AGT
, AP_LOOKUP_CODES ALC1
, /* BUG 5624200 */ AP_LOOKUP_CODES ALC2 WHERE AGT.APPROVAL_STATUS IS NULL AND ALC1.LOOKUP_TYPE = 'NLS TRANSLATION' /* BUG 5624200 */ AND ALC1.LOOKUP_CODE = 'FUTURE' AND ALC2.LOOKUP_TYPE (+) ='AP_WFAPPROVAL_CONTEXT' AND ALC2.LOOKUP_CODE (+) = DECODE(AGT.ITEM_CLASS
, 'HEADER'
, 'DOCUMENTAPPROVAL'
, 'LINESAPPROVAL') /* BUG 8546490*/ AND AGT.ORIG_SYSTEM_ID NOT IN (SELECT HIS.APPROVER_ID FROM AP_INV_APRVL_HIST_ALL HIS
, AP_INVOICES_ALL AI WHERE AI.INVOICE_ID = HIS.INVOICE_ID
AND AI.APPROVAL_ITERATION = HIS.ITERATION
AND HIS.RESPONSE = 'SENT' AND HIS.INVOICE_ID = TRANSACTION_ID
AND HIS.HISTORY_TYPE = DECODE(AGT.ITEM_CLASS
, 'HEADER'
, 'DOCUMENTAPPROVAL'
, 'LINESAPPROVAL')) AND EXISTS (SELECT 'INITIATED INVOICE' FROM AP_INVOICES_ALL AI WHERE AI.INVOICE_ID = TRANSACTION_ID AND AI.WFAPPROVAL_STATUS = 'INITIATED') )