VIEW: APPS.AP_INVOICE_PRICE_VAR_V

Source

View Text - Preformatted

SELECT   api.invoice_num                     Invoice_Num
,        api.invoice_date                    Invoice_Date /*13682517*/
,        aid.quantity_invoiced
,        api.exchange_rate                   Invoice_rate /*13682517*/
,        aid.amount                          Invoice_amount /*req. is dist amt */
,        aid.base_amount		     Invoice_base_amount /*req. is dist base amt */
,        aid.unit_price                      Invoice_price
,        api.invoice_currency_code           Invoice_currency
/* ,        aid.invoice_price_variance          Invoice_Price_Variance*/
,        nvl(aidipv.amount,0)                       Invoice_Price_Variance /*13682517*/
,        nvl(aidipv.base_amount,0)                  base_inv_price_var /*13682517*/
         /* bug 12691324 */
,        nvl(aiderv.base_amount,0)                  ex_rate_vari
,        aid.po_distribution_id
,        aid.rcv_transaction_id
,        aid.accounting_date
FROM     ap_invoices                  api
,        ap_invoice_distributions     aid
,        ap_invoice_distributions     aidipv
,        ap_invoice_distributions     aiderv
WHERE    api.invoice_id = aid.invoice_id
AND      aidipv.posted_flag(+) ||'' = 'Y'
AND      aiderv.posted_flag(+) ||'' = 'Y'
AND      aid.line_type_lookup_code IN ( 'ITEM' , 'ACCRUAL' )
AND      aidipv.related_id(+) = aid.invoice_distribution_id
AND      aidipv.line_type_lookup_code(+) = 'IPV'
AND      aiderv.related_id(+) = aid.invoice_distribution_id
AND      aiderv.line_type_lookup_code(+) = 'ERV'
          /* bug 12691324 */
AND      (aiderv.base_amount is not null or aidipv.amount is not null )
UNION ALL
/* for price correction invoices */
SELECT   api.invoice_num                     Invoice_Num
,        api.invoice_date                    Invoice_Date /*13682517*/
,        aid.quantity_invoiced
,        api.exchange_rate                   Invoice_rate /*13682517*/
,        aid.amount                          Invoice_amount
,        aid.base_amount		     Invoice_base_amount
,        aid.unit_price                      Invoice_price
,        api.invoice_currency_code           Invoice_currency
/* ,        aid.invoice_price_variance          Invoice_Price_Variance*/
,        nvl(aidipv.amount,0)                       Invoice_Price_Variance /*13682517*/
,        nvl(aidipv.base_amount,0)                  base_inv_price_var /*13682517*/
,        nvl(aiderv.base_amount,0)                  ex_rate_vari /*13682517*/
,        aid.po_distribution_id
,        aid.rcv_transaction_id
,        aid.accounting_date       /*13682517*/
FROM     ap_invoices                  api
,        ap_invoice_distributions     aid
,        ap_invoice_distributions     aidipv
,        ap_invoice_distributions     aiderv /*13682517*/
WHERE    api.invoice_id = aid.invoice_id
AND      aidipv.posted_flag ||''           = 'Y'
AND      aiderv.posted_flag(+) ||''           = 'Y' /*13682517*/
AND      aid.line_type_lookup_code IN ( 'ITEM' , 'ACCRUAL' )
AND      aidipv.corrected_invoice_dist_id = aid.invoice_distribution_id
AND      aidipv.line_type_lookup_code = 'IPV'
/*13682517*/
AND      aiderv.related_id(+) = aidipv.invoice_distribution_id
AND      aiderv.line_type_lookup_code(+) = 'ERV'
AND      (aiderv.base_amount is not null or aidipv.amount is not null)

View Text - HTML Formatted

SELECT API.INVOICE_NUM INVOICE_NUM
, API.INVOICE_DATE INVOICE_DATE /*13682517*/
, AID.QUANTITY_INVOICED
, API.EXCHANGE_RATE INVOICE_RATE /*13682517*/
, AID.AMOUNT INVOICE_AMOUNT /*REQ. IS DIST AMT */
, AID.BASE_AMOUNT INVOICE_BASE_AMOUNT /*REQ. IS DIST BASE AMT */
, AID.UNIT_PRICE INVOICE_PRICE
, API.INVOICE_CURRENCY_CODE INVOICE_CURRENCY /*
, AID.INVOICE_PRICE_VARIANCE INVOICE_PRICE_VARIANCE*/
, NVL(AIDIPV.AMOUNT
, 0) INVOICE_PRICE_VARIANCE /*13682517*/
, NVL(AIDIPV.BASE_AMOUNT
, 0) BASE_INV_PRICE_VAR /*13682517*/ /* BUG 12691324 */
, NVL(AIDERV.BASE_AMOUNT
, 0) EX_RATE_VARI
, AID.PO_DISTRIBUTION_ID
, AID.RCV_TRANSACTION_ID
, AID.ACCOUNTING_DATE FROM AP_INVOICES API
, AP_INVOICE_DISTRIBUTIONS AID
, AP_INVOICE_DISTRIBUTIONS AIDIPV
, AP_INVOICE_DISTRIBUTIONS AIDERV WHERE API.INVOICE_ID = AID.INVOICE_ID AND AIDIPV.POSTED_FLAG(+) ||'' = 'Y' AND AIDERV.POSTED_FLAG(+) ||'' = 'Y' AND AID.LINE_TYPE_LOOKUP_CODE IN ( 'ITEM'
, 'ACCRUAL' ) AND AIDIPV.RELATED_ID(+) = AID.INVOICE_DISTRIBUTION_ID AND AIDIPV.LINE_TYPE_LOOKUP_CODE(+) = 'IPV' AND AIDERV.RELATED_ID(+) = AID.INVOICE_DISTRIBUTION_ID AND AIDERV.LINE_TYPE_LOOKUP_CODE(+) = 'ERV' /* BUG 12691324 */ AND (AIDERV.BASE_AMOUNT IS NOT NULL OR AIDIPV.AMOUNT IS NOT NULL ) UNION ALL /* FOR PRICE CORRECTION INVOICES */ SELECT API.INVOICE_NUM INVOICE_NUM
, API.INVOICE_DATE INVOICE_DATE /*13682517*/
, AID.QUANTITY_INVOICED
, API.EXCHANGE_RATE INVOICE_RATE /*13682517*/
, AID.AMOUNT INVOICE_AMOUNT
, AID.BASE_AMOUNT INVOICE_BASE_AMOUNT
, AID.UNIT_PRICE INVOICE_PRICE
, API.INVOICE_CURRENCY_CODE INVOICE_CURRENCY /*
, AID.INVOICE_PRICE_VARIANCE INVOICE_PRICE_VARIANCE*/
, NVL(AIDIPV.AMOUNT
, 0) INVOICE_PRICE_VARIANCE /*13682517*/
, NVL(AIDIPV.BASE_AMOUNT
, 0) BASE_INV_PRICE_VAR /*13682517*/
, NVL(AIDERV.BASE_AMOUNT
, 0) EX_RATE_VARI /*13682517*/
, AID.PO_DISTRIBUTION_ID
, AID.RCV_TRANSACTION_ID
, AID.ACCOUNTING_DATE /*13682517*/ FROM AP_INVOICES API
, AP_INVOICE_DISTRIBUTIONS AID
, AP_INVOICE_DISTRIBUTIONS AIDIPV
, AP_INVOICE_DISTRIBUTIONS AIDERV /*13682517*/ WHERE API.INVOICE_ID = AID.INVOICE_ID AND AIDIPV.POSTED_FLAG ||'' = 'Y' AND AIDERV.POSTED_FLAG(+) ||'' = 'Y' /*13682517*/ AND AID.LINE_TYPE_LOOKUP_CODE IN ( 'ITEM'
, 'ACCRUAL' ) AND AIDIPV.CORRECTED_INVOICE_DIST_ID = AID.INVOICE_DISTRIBUTION_ID AND AIDIPV.LINE_TYPE_LOOKUP_CODE = 'IPV' /*13682517*/ AND AIDERV.RELATED_ID(+) = AIDIPV.INVOICE_DISTRIBUTION_ID AND AIDERV.LINE_TYPE_LOOKUP_CODE(+) = 'ERV' AND (AIDERV.BASE_AMOUNT IS NOT NULL OR AIDIPV.AMOUNT IS NOT NULL)