R12: Invoice Price Variance Report

 Cost Type:AVG

PO(PO Price)與AP(AP立帳金額)間存在價格差異與匯率差異

價格差異 > 如何將價格差異分攤到存貨成本，需要定時執行請求Transfer Invoice Variance to Inventory Valuation，如果某些資料不要分攤，可以透過修改AP_INVOICE_DISTRIBUTIONS_ALL.INVENTORY_TRANSFER_STATUS從N改為NULL來讓Transfer Invoice Variance to Inventory Valuation不進行分攤

匯率差異 > 在標準功能中不會進行分攤，需要透過手動或客制處理，這時候就可以透過Invoice Price Variance Report，這支報表來查看價格差異與匯率差異

SELECT AIA.ORG_ID,
       AIA.INVOICE_ID,
       AIA.INVOICE_NUM,
       AIA.INVOICE_DATE,
       AIDA.INVENTORY_TRANSFER_STATUS,
       AIDA.LINE_TYPE_LOOKUP_CODE
  FROM AP_INVOICES_ALL AIA, AP_INVOICE_DISTRIBUTIONS_ALL AIDA
 WHERE AIA.INVOICE_ID = AIDA.INVOICE_ID(+)
   --AND AIA.ORG_ID = 136
   AND AIDA.LINE_TYPE_LOOKUP_CODE = 'ITEM' –-這邊其他型態也要押
   	   AND AIDA.INVENTORY_TRANSFER_STATUS = 'N'
		
UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
   		   SET INVENTORY_TRANSFER_STATUS = NULL
   WHERE ACCOUNTING_DATE <= TO_DATE('20220331','YYYYMMDD') --PAC to AVG go live

--Invoice Price Variance Report
SELECT GLPS.SET_OF_BOOKS_ID,
       AIA.ORG_ID,
       DECODE(AIA.ORG_ID, 136, 'TW', 103, 'TJ', 197, 'LY') ORG,
       MSI.INVENTORY_ITEM_ID,
       MSI.SEGMENT1,
       AIA.INVOICE_ID,
       AIA.INVOICE_NUM,
       POH.CURRENCY_CODE,
       GLPS.PERIOD_NAME,
       AIA.INVOICE_DATE,
       APIPV.BASE_PRICE_VAR,
       APIPV.EXCH_RATE_VAR,
       AIA.LAST_UPDATE_DATE
  FROM GL_CODE_COMBINATIONS   GCC1,
       GL_CODE_COMBINATIONS   GCC,
       GL_PERIOD_STATUSES     GLPS,
       PO_DISTRIBUTIONS_ALL   POD,
       PO_LINE_LOCATIONS_ALL  PLL,
       PO_LINES_ALL           POL,
       PO_RELEASES_ALL        POR,
       PO_HEADERS_ALL         POH,
       PO_VENDORS             POV,
       AP_INVOICE_PRICE_VAR_V APIPV,
       AP_INVOICES_ALL        AIA,
       MTL_SYSTEM_ITEMS       MSI,
       MTL_CATEGORIES_B       MCA,
       HR_LOCATIONS_ALL_TL    LOT,
       RCV_TRANSACTIONS       RCT
 WHERE 1 = 1
   AND APIPV.INVOICE_ID = AIA.INVOICE_ID
   AND APIPV.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
   AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
   AND PLL.PO_LINE_ID = POL.PO_LINE_ID
   AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
   AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
   AND POH.VENDOR_ID = POV.VENDOR_ID(+)
   AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
   AND POL.CATEGORY_ID = MCA.CATEGORY_ID
   AND GCC.CODE_COMBINATION_ID = POD.CODE_COMBINATION_ID
   AND GCC1.CODE_COMBINATION_ID = POD.VARIANCE_ACCOUNT_ID
   AND LOT.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
   AND PLL.SHIP_TO_LOCATION_ID IS NOT NULL
   AND LOT.LANGUAGE(+) = USERENV('LANG')
   AND POD.DESTINATION_TYPE_CODE IN ('INVENTORY', 'SHOP FLOOR')
   AND GLPS.APPLICATION_ID = 201
   AND GLPS.SET_OF_BOOKS_ID = 1003 --TW 1003 TJ 1001 LY 3003
   AND AIA.ORG_ID = 136 --TW 136 TJ 103 LY 197
   AND MSI.ORGANIZATION_ID = 137 --TW 137 TJ 106 LY 198
      --AND GLPS.PERIOD_NAME = '06-22' --TW Apr-22 TJ 04-22 LY Apr-22 
      --AND AIA.INVOICE_DATE BETWEEN TO_DATE('20220101','YYYYMMDD') AND TO_DATE('20221231','YYYYMMDD')
      --AND AIA.INVOICE_NUM = '220321-3'
   AND APIPV.ACCOUNTING_DATE BETWEEN GLPS.START_DATE AND GLPS.END_DATE
   AND APIPV.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID(+)
   AND POH.TYPE_LOOKUP_CODE IN ('STANDARD', 'BLANKET', 'PLANNED')
   AND PLL.SHIPMENT_TYPE IN ('STANDARD', 'BLANKET', 'SCHEDULED')
   AND MSI.SEGMENT1 LIKE '%XXX%'
 ORDER BY AIA.ORG_ID, AIA.INVOICE_DATE ASC;

--AP_INVOICE_DISTRIBUTIONS_ALL MMT紀錄