|
(0003941)
|
|
user71
|
2008-02-15 12:18
(edited on: 2008-06-12 09:26) |
|
Logged In: YES
user_id=1701456
Originator: YES
i have solved this problem:
1. in generate_average_costs, i modified the pl in the cursor Cur_InOutLine. I added a "select union" of all invoice returns with lines not associated to m_inout:
FOR Cur_InOutLine IN
(SELECT M_INOUTLINE_ID, MOVEMENTQTY, M_PRODUCT_ID, MOVEMENTDATE,
AD_CLIENT_ID, AD_ORG_ID, C_BPARTNER_ID, C_INVOICELINE_ID
FROM
(SELECT M_INOUTLINE.M_INOUTLINE_ID, M_INOUTLINE.MOVEMENTQTY, M_PRODUCT.M_PRODUCT_ID, M_INOUT.MOVEMENTDATE,
M_INOUTLINE.AD_CLIENT_ID, M_INOUTLINE.AD_ORG_ID, M_INOUT.C_BPARTNER_ID, NULL AS C_INVOICELINE_ID
FROM M_INOUT, M_INOUTLINE, M_PRODUCT
WHERE M_INOUT.M_INOUT_ID=M_INOUTLINE.M_INOUT_ID
AND M_INOUTLINE.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID
AND M_PRODUCT.COSTTYPE='AV'
AND M_INOUT.ISSOTRX='N'
AND M_INOUT.PROCESSED='Y'
AND M_INOUTLINE.MOVEMENTQTY>0
AND NOT EXISTS
(SELECT 1 FROM M_COSTING WHERE M_INOUTLINE_ID=M_INOUTLINE.M_INOUTLINE_ID)
/**** new:
UNION
SELECT NULL, IL.QTYINVOICED AS MOVEMENTQTY, IL.M_PRODUCT_ID, I.DATEINVOICED AS DATEMOVEMENT,
IL.AD_CLIENT_ID, IL.AD_ORG_ID, I.C_BPARTNER_ID, IL.C_INVOICELINE_ID
FROM C_INVOICELINE IL, C_INVOICE I
WHERE IL.M_INOUTLINE_ID IS NULL
AND IL.C_INVOICE_ID = I.C_INVOICE_ID
AND IL.C_ORDERLINE_ID IS NULL
AND IL.QTYINVOICED <0
AND NOT EXISTS (SELECT 1 FROM M_COSTING WHERE C_INVOICELINE_ID=IL.C_INVOICELINE_ID))
ORDER BY M_PRODUCT_ID, MOVEMENTDATE, MOVEMENTQTY DESC) loop ****** end new***
2. in tab of costs, exists a field of invoiceLineFrom (like inoutLineFrom), but this field is hidden. I showed this field. |
|