بسم الله الرحمن الرحيم

 

السلام عليكم ورحمة الله وبركاته

 

 

من فضلكم اريد شرح كامل للتقرير هذا و اريد جميع المعلومات لأني مبتدئ في مجال البرمجة واجد صعوبة في كتابة الأكواد ولكم جزيل الشكر

SELECT W.ITEM_NUMBER PART_NO, 
               W.DESCRIPTION,
               W.UOM,
               W.PLANNER_CODE,
               W.CATEGORY PO_CAT,
               BU_GET_ITEM_COST_F(W.ITEM_ID,W.ORGANIZATION_ID) ITEM_COST,
               TO_CHAR(TRANSACTION_DATE,'YYYY') PERIOD,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'JAN',M.TRANSACTION_QUANTITY,0)) JAN,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'FEB',M.TRANSACTION_QUANTITY,0)) FEB,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'MAR',M.TRANSACTION_QUANTITY,0)) MAR,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'APR',M.TRANSACTION_QUANTITY,0)) APR,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'MAY',M.TRANSACTION_QUANTITY,0)) MAY,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'JUN',M.TRANSACTION_QUANTITY,0)) JUN,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'JUL',M.TRANSACTION_QUANTITY,0)) JUL,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'AUG',M.TRANSACTION_QUANTITY,0)) AUG,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'SEP',M.TRANSACTION_QUANTITY,0)) SEP,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'OCT',M.TRANSACTION_QUANTITY,0)) OCT,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'NOV',M.TRANSACTION_QUANTITY,0)) NOV,
               SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'DEC',M.TRANSACTION_QUANTITY,0)) DEC        
FROM    BU_WIP_ITEM_CONSUMPTION W,
              APPS.MTL_MATERIAL_TRANSACTIONS M
WHERE  M.INVENTORY_ITEM_ID = W.ITEM_ID AND 
                M.ORGANIZATION_ID = W.ORGANIZATION_ID AND 
                M.TRANSACTION_TYPE_ID IN (35,43) AND 
                TRANSACTION_DATE BETWEEN :FROMDATE AND :TO_DATE
GROUP BY 
                  TO_CHAR(M.TRANSACTION_DATE,'YYYY'),
                  W.ITEM_NUMBER, 
                  W.DESCRIPTION,
                  W.UOM,
                  W.PLANNER_CODE,
                  W.CATEGORY,
                  W.ITEM_ID,
                  W.ORGANIZATION_ID

SELECT W.ITEM_NUMBER PART_NO,
W.DESCRIPTION,
W.UOM,
W.PLANNER_CODE,
W.CATEGORY PO_CAT,
BU_GET_ITEM_COST_F(W.ITEM_ID,W.ORGANIZATION_ID) ITEM_COST,
TO_CHAR(TRANSACTION_DATE,'YYYY') PERIOD,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'JAN',M.TRANSACTION_QUANTITY,0)) JAN,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'FEB',M.TRANSACTION_QUANTITY,0)) FEB,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'MAR',M.TRANSACTION_QUANTITY,0)) MAR,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'APR',M.TRANSACTION_QUANTITY,0)) APR,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'MAY',M.TRANSACTION_QUANTITY,0)) MAY,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'JUN',M.TRANSACTION_QUANTITY,0)) JUN,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'JUL',M.TRANSACTION_QUANTITY,0)) JUL,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'AUG',M.TRANSACTION_QUANTITY,0)) AUG,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'SEP',M.TRANSACTION_QUANTITY,0)) SEP,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'OCT',M.TRANSACTION_QUANTITY,0)) OCT,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'NOV',M.TRANSACTION_QUANTITY,0)) NOV,
SUM(DECODE(TO_CHAR(M.TRANSACTION_DATE,'MON'),'DEC',M.TRANSACTION_QUANTITY,0)) DEC
FROM BU_WIP_ITEM_CONSUMPTION W,
APPS.MTL_MATERIAL_TRANSACTIONS M
WHERE M.INVENTORY_ITEM_ID = W.ITEM_ID AND
M.ORGANIZATION_ID = W.ORGANIZATION_ID AND
M.TRANSACTION_TYPE_ID IN (35,43) AND
TRANSACTION_DATE BETWEEN :FROMDATE AND :TO_DATE
GROUP BY
TO_CHAR(M.TRANSACTION_DATE,'YYYY'),
W.ITEM_NUMBER,
W.DESCRIPTION,
W.UOM,
W.PLANNER_CODE,
W.CATEGORY,
W.ITEM_ID,
W.ORGANIZATION_ID

 

بسيط ...شوف decode بتعمل ايه الاولي والناتج منها هاتعنل له sum

M.TRANSACTION_TYPE_ID IN (35,43) 

ودي معناها ان البيانات اللي هاتطلع لازم تكون اما 35 او 43.

ويتحزموا حزما ومجوعات عن طريق 

TO_CHAR(M.TRANSACTION_DATE,'YYYY'),
W.ITEM_NUMBER,
W.DESCRIPTION,
W.UOM,
W.PLANNER_CODE,

W.CATEGORY,
W.ITEM_ID,
W.ORGANIZATION_ID

وبالترتيب ده.

 

اريد شرح اكثر تفصيل من فضلك