$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
0

Differentiat between receipt from production and return component

Apr 05, 2017 at 07:49 AM

19

avatar image

I already read the discussion with reference "

Method/Query to identify receipt from production is from return component or normal receipt from production"

. However, since the thread is closed, am opening a new thread.

Tries the solution proposed by Kennedy, it works excellent. But still does not serve my purpose, because:

We create a Special Production Order for each customer to handle replacement material.

We issue some items for replacement to customers.

When the old item is received back, we enter Return Component.

To avoid these entries interfering with MRP, we delete the production order row after material is received.

In that case, the query does not find issues item in production order and return component does not reflect in result set.

I tried below logic, but then it results in multiple return component entries in result set.

Any solution will be highly appreciated.

Anil

--Linked Issue to Production Orders
--Table used
--OWOR T0 Production Orders Document
--IGE1 T1 Issue for Production Rows
--OITM T2 Item Master
--OITM T3 Item Master
--OIGE T4 Issue for Production Document


SELECT T0.[ItemCode] Prd_Item,
T2.[ItemName] Prd_Item_Name,
'Issue ' Tran_Type,
T4.[DocDate] Txn_Date,
T1.[ItemCode] Iss_Item,
T3.[ItemName] Iss_Item_Name,
T1.[Quantity] Iss_Qty,
T1.[LineTotal] Iss_Value,
'#' Mark_1,
T1.[U_Instructed_By],
T1.U_Issue_Reasion,
T1.U_Cust_End_cnt,
'#' Mark_2,
T0.[DocEntry] Prd_Intrnl_No,
T0.[DocNum] Prd_No,
T0.[Series] Prd_Series,
T0.[PostDate] Prd_Date,
T0.[CardCode] Cust_Code,
T0.[Project] Prd_Project,
'..' ,
T4.[DocEntry] Iss_Intrnl_No,
T4.[DocNum] Iss_No,
T4.[DocType] Iss_Type,
T4.[Project] Iss_Project,
'...',
T1.[DocEntry] Iss_Intrnl_No,
T1.[LineNum] Iss_Line,
T1.[TargetType] Iss_Trgt_Type,
T1.[TrgetEntry] Iss_Trgt_Doc_Intrnl_No,
T1.[BaseEntry] Iss_Base_Intrnl_No,
T1.[BaseType] Iss_Base_Type,
T1.[BaseRef] Iss_Base_Ref,
T1.[BaseLine] Iss_Base_Row

FROM OWOR T0 INNER JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode],
IGE1 T1 INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]
INNER JOIN OIGE T4 ON T1.[DocEntry] = T4.[DocEntry]
WHERE T0.[ItemCode] LIKE '[%0]%' AND
T0.DOCENTRY = T1.[BaseEntry]


UNION ALL
--Table Used
--Linked Return Components to Production Orders
--Tables used
--OWOR T0 Production Orders Document
--IGN1 T1 Receipt from Production Rows
--OITM T2 Item Master
--OITM T3 Item Master
--OIGN T4 Receipt from Production Document
--WOR1 T5 Production Order Rows


SELECT T0.[ItemCode] Prd_Item,
T2.[ItemName] Prd_Item_Name,
'Return' TranType,
T4.[DocDate] Txn_Date,
T1.[ItemCode] Ret_Item,
T3.[ItemName] Ret_Item_Name,
(T1.[Quantity] * -1) Ret_Qty,
(T1.[LineTotal] * -1) Ret_Value,
'#' Mark_1,
T1.[U_Instructed_By],
T1.U_Issue_Reasion,
T1.U_Cust_End_cnt,
'#' Mark_2,
T0.[DocEntry] Prd_Intrnl_No,
T0.[DocNum] Prd_No,
T0.[Series] Prd_Series,
T0.[PostDate] Prd_Date,
T0.[CardCode] Cust_Code,
T0.[Project] Prd_Project,
'..',
T4.DocEntry Ret_Intrnl_No,
T4.[DocNum] Ret_No,
T4.[DocType] Ret_Type,
T4.[Project] Ret_Project,
'...',
T1.[DocEntry] Iss_Row_Intrnl_No,
T1.[LineNum] Ret_Line,
T1.[TargetType] Ret_Trgt_Type,
T1.[TrgetEntry] Iss_Trgt_Intrnl_No,
T1.[BaseEntry] Ret_Base_Intrnl_No,
T1.[BaseType] Ret_Base_Type,
T1.[BaseRef] Ret_Base_No,
T1.[BaseLine] Iss_Base_Row

FROM OWOR T0 INNER JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode]
INNER JOIN WOR1 T5 ON T0.[DocEntry] = T5.[DocEntry],
IGN1 T1 INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]
INNER JOIN OIGN T4 ON T1.[DocEntry] = T4.[DocEntry]
WHERE T1.[ItemCode] != T0.[ItemCode] AND
T1.[BaseEntry] = T0.[Docentry] AND
T0.[ItemCode] LIKE '[%0]%'

ORDER BY T0.[ITEMCODE], T1.ITEMCODE, T4.[DOCDATE]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers