[Cost] MTA - ACCOUNTING_LINE_TYPE

ACCOUNTING_LINE_TYPE

1 Inv valuation
2 Account
3 Overhead absorption
4 Resource absorption
5 Receiving Inspection
6 Purchase price variance or rate variance
7 WIP valuation
8 WIP variance
9 Inter-org payables
10 Inter-org receivables
11 Inter-org transfer credit
12 Inter-org freight charge
13 Average cost variance
14 Intransit Inventory
15 Encumbrance Reversal
99 Unknown

範例

SELECT MTT.TRANSACTION_TYPE_NAME,
       MMT.TRANSACTION_DATE,
       GCC.SEGMENT1||','||GCC.SEGMENT2||','||GCC.SEGMENT3||','||GCC.SEGMENT4 ACCT,
       ROUND(SUM(NVL(MTA.BASE_TRANSACTION_VALUE,0)), 1) NET_ACTIVITY,
       ROUND(SUM(DECODE( NVL(MTA.BASE_TRANSACTION_VALUE,0) /
                         ABS(DECODE(
                                    NVL(MTA.BASE_TRANSACTION_VALUE,0),
                                    0, 1, NVL( MTA.BASE_TRANSACTION_VALUE,0  )  )), 
                  1, NVL(MTA.BASE_TRANSACTION_VALUE,0), 0 )  ), 1) SUM_DEBITS,
       ROUND(SUM(DECODE( NVL(MTA.BASE_TRANSACTION_VALUE,0) /
                         ABS(DECODE(
                                     NVL(MTA.BASE_TRANSACTION_VALUE,0),
                                     0, 1, NVL( MTA.BASE_TRANSACTION_VALUE,0  )  )), 
                 -1, NVL(ABS(MTA.BASE_TRANSACTION_VALUE),0), 0 )  ), 1) SUM_CREDITS
  FROM MTL_MATERIAL_TRANSACTIONS MMT,    
       --MTL_SYSTEM_ITEMS MSI,
       GL_CODE_COMBINATIONS GCC,
       MTL_TRANSACTION_ACCOUNTS MTA,
       MTL_TRANSACTION_TYPES MTT,
       ORG_GL_BATCHES OGB
WHERE MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
   AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
   --AND    MTA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
   --AND    MSI.ORGANIZATION_ID  =  MMT.ORGANIZATION_ID
   AND MTA.REFERENCE_ACCOUNT = GCC.CODE_COMBINATION_ID
   AND MMT.ORGANIZATION_ID = MTA.ORGANIZATION_ID
   AND MTA.ORGANIZATION_ID = :P_ORG_ID
   AND MTA.ACCOUNTING_LINE_TYPE <> 15
   AND MTA.TRANSACTION_DATE BETWEEN ...
   AND OGB.GL_BATCH_ID (+) = MTA.GL_BATCH_ID
GROUP BY MTT.TRANSACTION_TYPE_NAME,
       MMT.TRANSACTION_DATE,
       GCC.SEGMENT1||','||GCC.SEGMENT2||','||GCC.SEGMENT3||','||GCC.SEGMENT4