======Special customization======

<wrap em>Produmex Manufacturing is a legacy product and Boyum IT Solutions no longer sells new installations for it.</wrap>

=====1. User queries for validation=====
The user queries for validation should return two columns:
  * RowTy: row type can be E (error) or W (warning)
  * Msg: the error/warning description
An error message will appear in red on the bottom of the screen, and block the current process. A warning message will be displayed in a separate window, asking a confirmation from the user to continue. For more information about the message types please see: [[implementation:manufacturing:customizationtechnology#create_an_sap_user_query|Supported message types]]

Only the first row of the result is taken into account.
====1.1. User identification====
User query name: //bxtc_pdc_user_identification//

Parameters: 
  * [%0]: Action code (1 = OK)=/ 
  * [%1]: Employee ID (OHEM.empID)
  * [%9]: Terminal ID (IP address) 

====1.2. Production Operation Selection====
User query name: //bxtc_pdc_production_operation_selection//

Parameters: 
  * [%0]: Action code ; possible values are:
    * 1 = Complete/Stop
    * 2 = Break/Partial
    * 3 = Resume (only available in old web/tomcat PDC client)
    * 4 = New Op
    * 5 = Admin
    * 6 = Materials (not available yet in BX Mobile PDC client)
  * [%1]: Employee ID (OHEM.empID)
  * [%2]: Operation ID (WOR1.U_BXPBxID)
  * [%3]: Job/Setup (0 = Job, 1 = Setup)
  * [%9]: Terminal ID (IP address)

If more than one operations are selected, then parameters [%2] and [%3] are a coma-separated list of values.

====1.3. Completed/Partial Job Quantities====
User query name: //bxtc_pdc_job_quantities//

Parameters: 
  * [%0]: Action code (1 = OK)
  * [%1]: Employee ID (OHEM.empID)
  * [%2]: Operation ID (WOR1.U_BXPBxID)
  * [%3]: Completed Quantity
  * [%4]: Rejected Quantity
  * [%5]: PDC Booking Code
  * [%6]: Duration (calculated) in seconds (not supported yet)
  * [%7]: Work Center Code
  * [%9]: Terminal ID (IP address)

====1.4. Start New Operation====
User query name: //bxtc_pdc_start_operation//

Parameters:
  * [%0]: Action code (1 = OK)
  * [%1]: Employee ID (OHEM.empID)
  * [%2]: Operation ID (WOR1.U_BXPBxID)
  * [%3]: Job/Setup (0 = Job, 1 = Setup)
  * [%9]: Terminal ID (IP address)

=====2. Other user queries=====
====2.1. Product serial/batch number====
This query is used when the serial/batch selection is disabled for products by enabling the ‘Skip product serial/batch quantities screen’ option on the [[implementation:manufacturing:confguide#thin_client_2_tab|Thin client 2]] tab. 
In that case, the operation quantities can be entered without selecting batches/serial numbers, and the user query will automatically create them when the PDC booking is being processed.

User query name: //bxtc_pdc_serial_batch_products//

^ Parameters ^ Returned columns ^
|[%1]: Employee ID (OHEM.empID) \\ [%2]: Operation ID (WOR1.U_BXPBxID) \\ [%3]: PDC Booking Code \\ [%4]: Product Code (OITM.ItemCode) \\ [%5]: Quantity \\ [%6]: Is Rejected? (0 = Completed, 1 = Rejected) \\ [%7]: Purchase Order Line Extension Code (outsourcing only) \\ [%8]: Product Type (outsourcing only) (1 = Main Product, 2 = By-Product, 3 = Unfinished Product)| SBNum: Serial or batch number \\ Qty: Quantity|

====2.2. Material serial/batch number====
This query is used when the serial/batch selection is disabled for materials by enabling the ‘Skip material serial/batch quantities screen’ option on the [[implementation:manufacturing:confguide#thin_client_2_tab|Thin client 2]] tab. 
In that case, the operation quantities can be entered without selecting batches/serial numbers, and the user query will automatically create them when the PDC booking is being processed.

User query name: //bxtc_pdc_serial_batch_materials//

^ Parameters ^ Returned columns ^
|[%1]: Employee ID (OHEM.empID) \\ [%2]: Operation ID (WOR1.U_BXPBxID) \\ [%3]: PDC Booking Code \\ [%4]: Material Code (OITM.ItemCode) \\ [%5]: Quantity \\ [%7]: Purchase Order Line Extension Code| SBNum: Serial or batch number \\ Qty: Quantity|

====2.3. PTM Log====
User query name: //bxtc_pdc_ptm_log_query// 

Example query:\\
<code>
EXECUTE sp_executesql  N'
BEGIN

DECLARE @RowNum INT, @IsStarted VARCHAR(1), @StartDate VARCHAR(20)

SET NOCOUNT ON

CREATE TABLE #EmpLog (RowNum INT, IsStarted VARCHAR(1), StartDate VARCHAR(50), EndDate VARCHAR(50), Employee INT)
INSERT INTO #EmpLog SELECT ROW_NUMBER() OVER(ORDER BY U_BXPStamO) AS RowNum, U_BXPActn AS IsStarted, U_BXPStamO AS StartDate, NULL AS EndDate, U_BXPEmpID AS Employee
FROM [dbo].[@BXPATTLOG]
WHERE U_BXPEmpID = 1

DECLARE log_cursor CURSOR FOR
SELECT RowNum, IsStarted, StartDate
FROM #EmpLog

OPEN log_cursor
FETCH NEXT FROM log_cursor
INTO @RowNum, @IsStarted, @StartDate;
WHILE @@FETCH_STATUS = 0
BEGIN
                IF @IsStarted = ''N''
                BEGIN
                                UPDATE #EmpLog SET EndDate = @StartDate WHERE RowNum = @RowNum - 1
                END
                FETCH NEXT FROM log_cursor
                INTO @RowNum, @IsStarted, @StartDate;
END
CLOSE log_cursor;
DEALLOCATE log_cursor;

SELECT
CAST(LEFT(StartDate, 8) AS DATE) AS Date,
CAST(LEFT(StartDate, 8) AS DATE) AS StartDate,
CAST(LEFT(RIGHT(StartDate, 6), 4) AS INT) AS StartTime,
CAST(LEFT(EndDate, 8) AS DATE) AS EndDate,
CAST(LEFT(RIGHT(EndDate, 6), 4) AS INT) AS EndTime,
NULL AS BreakTime,
NULL AS JobTime,

DATEDIFF(MINUTE, 
CAST(LEFT(StartDate, 8) AS DATETIME)
+ CAST(DATEADD(HOUR, (CAST(SUBSTRING(StartDate, 9, 2) AS INT)),
DATEADD(MINUTE, (CAST(SUBSTRING(StartDate, 11, 2) AS INT)),
DATEADD(SECOND, CAST(SUBSTRING(StartDate, 13, 2) AS INT), CAST(''00:00:00'' AS TIME(3))))) AS DATETIME), 
CAST(LEFT(EndDate, 8) AS DATETIME)
+ CAST(DATEADD(HOUR, (CAST(SUBSTRING(EndDate, 9, 2) AS INT)),
DATEADD(MINUTE, (CAST(SUBSTRING(EndDate, 11, 2) AS INT)),
DATEADD(SECOND, CAST(SUBSTRING(EndDate, 13, 2) AS INT), CAST(''00:00:00'' AS TIME(3))))) AS DATETIME)) AS WorkTime,

CASE WHEN DATENAME(dw, CAST(LEFT(StartDate, 8) AS DATETIME)) IN (''Saturday'', ''Sunday'') OR EXISTS (SELECT 1 FROM HLD1 WHERE StrDate = CAST(LEFT(StartDate, 8) AS DATETIME)) THEN ''N'' ELSE ''Y'' END  AS IsWorkingDay, 

NULL AS InfoText1, 
NULL AS InfoText2

FROM #EmpLog WHERE IsStarted = ''Y'' AND StartDate IS NOT NULL AND EndDate IS NOT NULL

DROP TABLE #EmpLog
END'</code>

====2.4. Workshop Monitor====
User query name: //bxtc_pdc_workshop_monitor_query// 

Example query:\\
<code>
SELECT TOP 30
                T0.Code as Code,
                T0.Name as Name,
                T1.ItemCode as ProductCode,
                T3.ItemName as ProductName,
                T0.U_BXPOpCod as OperationCode,
                T0.U_BXPOpNam as OperationName,
                T5.U_BXPPrfWC as PreferredWorkCenter,
                T5.U_BXPFeat as PreferredFeature,
                T0.U_BXPPlQty as PlannedQuantity,
                T0.U_BXPCoQty as CompletedQuantity,
                T0.U_BXPRejQt as RejectedQuantity,
                T2.IssuedQty AS IssuedQuantity,
                T1.DocEntry as DocEntry,
                T1.DocNum as DocNum,
                T2.LineNum as LineNum,
                T0.U_BXPBSetu as SetupTime,
                T0.U_BXPEDuDt as DueDate,
                T0.U_BXPPDueT as DueTime,
                T4.U_BXPPstCd AS PDCPostingCode,
                T4.U_BXPPstDt as PDCPostingDate,
                T4.U_BXPPstTm as PDCPostingTime,
                T4.U_BXPWCent as PDCWorkCenter,
                T4.Code as PDCBookingID,
                T6.firstName as EmployeeFirstName,
              T6.lastName as EmployeeLastName,
                T6.empID as EmployeeID
FROM 
                [@BXPPRODORDEROPER] T0
                INNER JOIN [OWOR] T1 ON T0.U_BXPPrODE = T1.DocEntry AND T1.[Status] <> N'L' 
                INNER JOIN WOR1 T2 ON T2.U_BXPBxID = T0.Code
                INNER JOIN OITM T3 ON T3.ItemCode = T1.ItemCode
                LEFT OUTER JOIN [@BXPPDCBOOKING] T4 ON T4.U_BXPPrOOI = T0.Code AND T4.Code IN
                  (SELECT TX.Code FROM [@BXPPDCBOOKING] TX WHERE TX.U_BXPIsUnd = 'N' AND TX.U_BXPPrOOI = T0.Code AND
                    TX.U_BXPPstDt = (SELECT MAX(U_BXPPstDt) FROM [@BXPPDCBOOKING] TX1 WHERE TX1.U_BXPIsUnd = 'N' AND TX1.U_BXPPrOOI = T0.Code) AND
                    TX.U_BXPPstTm = (SELECT MAX(U_BXPPstTm) FROM [@BXPPDCBOOKING] TX2 WHERE TX2.U_BXPIsUnd = 'N' AND TX2.U_BXPPrOOI = T0.Code AND TX2.U_BXPPstDt = TX.U_BXPPstDt))
                LEFT OUTER JOIN [@BXPPRODORDERREQU] T5 ON T5.U_BXPPrOOI = T0.Code AND T5.U_BXPResTy = 1
                LEFT OUTER JOIN OHEM T6 ON T6.empID = T4.U_BXPEmpID
WHERE 
                T0.U_BXPPlQty > T0.U_BXPCoQty + T0.U_BXPRejQt AND 
                T0.U_BXPCoQty + T0.U_BXPRejQt > 0  AND 
                T0.U_BXPIsOuS = 'N' 
ORDER BY 
                T0.U_BXPEndDt DESC, 
                T0.U_BXPEndTm DESC
</code>

When using the example query, operations with completed jobs are listed where the booked quantity is less than the planned quantity and there is at least one booking on it. Outsourced operations are not displayed.

====2.5. Personal duration factor for multiple PDC bookings====
This query is used when reporting a completion (either partial or complete) for more than one running jobs at the same time. In that case, the machine duration will be the full duration for all PDC bookings, however the person duration will be split among the PDC bookings according to the logic defined in this user query.

User query name: //bxtc_pdc_multiple_pdc_person_duration_factor// 

^ Parameters ^ Returned columns ^
| [%1]: Employee ID (OHEM.empID) \\ [%2]: List of operation codes, coma-separated \\ [%3]: List of work center codes, coma-separated (in the same order) \\ [%4]: List of completed quantities, coma-separated (in the same order) \\ [%5]: List of rejected quantities, coma-separated (in the same order) \\ [%9]: Terminal ID (IP address) | OpCod: operation code \\ PsDur: person duration |

//Please note: This query has a default implementation which divides the total duration by the number of PDC bookings processed together for each PDC booking.//

====2.6. Order Recommendation Custom Grouping====
This query is used to customize the auto grouping function for [[implementation:manufacturing:maketo|MTO planning]].

The query runs when:
  * If you click on Auto-Group button on the [[implementation:manufacturing:maketo#group_recommendations_form|Group Recommendations form]]
  * Order recommendations are being created and the 'Auto Group' option is set to true on the [[implementation:manufacturing:confguide#mto_tab|MTO tab]] of Produmex Manufacturing settings

User query name: //BXPPS_MTO_QueryNonGroupableItems//

Parameters:
  * [%0]: MTO scenario code

Example query: 
<code>SELECT Code FROM [@BXPMTOORDRSOLREF] 
WHERE U_BXPMTOSc = '[%0]' AND U_BXPItmCd IN (
       SELECT OITM.ItemCode
       FROM OITM, itt1 
       WHERE  oitm.itemcode = itt1.father and (itt1.code='ITEM01' or itt1.code='ITEM02' or  itt1.code='ITEM03') )
</code>