To retrieve transaction details, including item and quantity information from the transaction line, we can use query on the transaction line table. By default, this query will include both standalone items and components of Kit Items.
To exclude Kit Item components and return only the items listed on the sales order, include the following condition in the WHERE clause:
sql
ti.kitmemberof IS NULL
This condition ensures that only top-level items are displayed, omitting any subcomponents of Kit Items from the results.
Query:
SELECT ti.item AS item, ti.location AS locationId, SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 0, ti.quantity, 0 ) ) AS "currentMonth" , SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 1, ti.quantity, 0 ) ) AS "1MonthAgo" , SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 2, ti.quantity, 0 ) ) AS "2MonthAgo" , SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 3, ti.quantity, 0 ) ) AS "3MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 4, ti.quantity, 0 ) ) AS "4MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 5, ti.quantity, 0 ) ) AS "5MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 6, ti.quantity, 0 ) ) AS "6MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 7, ti.quantity, 0 ) ) AS "7MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 8, ti.quantity, 0 ) ) AS "8MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 9, ti.quantity, 0 ) ) AS "9MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 10, ti.quantity, 0 ) ) AS "10MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 11, ti.quantity, 0 ) ) AS "11MonthAgo",
SUM( DECODE( TO_CHAR(CURRENT_DATE, 'MM') - TO_CHAR(t.trandate, 'MM') + 12 * (TO_CHAR(CURRENT_DATE, 'YYYY') - TO_CHAR(t.trandate, 'YYYY')), 12, ti.quantity, 0 ) ) AS "12MonthAgo",
FROM transaction t JOIN transactionline ti ON t.id = ti.transaction JOIN item i ON ti.item = i.id
WHERE t.trandate BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12) AND LAST_DAY(SYSDATE) AND ti.item IN ('741585','457070') AND t.type = 'SalesOrd' AND ti.mainline = 'F' AND ti.kitmemberof IS NULL
AND ti.subsidiary IN ('7')
GROUP BY ti.item, ti.location