def ajax_getStockList(self,
request,
frmName,
fldName=None,
msgType=ViewCapture.FORM_MSG_TYPE_NONE,
msg=None):
fs, form, flds, part = self.processInit(request, frmName)
q = """ select STM.stm_auto_key as "stm_auto_key"
, STM.serial_number as "serialNumber"
, to_char(STM.exp_date, 'yyyy-mm-dd') as "expDate"
, STM.receiver_number as "receiverNumber"
, STM.original_po_number as "poNum"
, to_char(STM.qty_oh) as "qtyOH"
, to_char(STM.qty_reserved) as "qtyRes"
, to_char(STM.qty_available) as "qtyAvail"
, WHS.warehouse_code as "warehouseCode"
, LOC.location_code as "locationCode"
, nvl(PCC.condition_code, '-') as "conditionCode"
, to_char(STM.unit_cost) as "unitCost"
, to_char(STM.rec_date, 'yyyy-mm-dd') as "recDate"
, STM.ctrl_number as "ctrlNo"
, STM.ctrl_id as "ctrlId"
from stock STM
, location LOC
, warehouse WHS
, part_condition_codes PCC
where STM.whs_auto_key = WHS.whs_auto_key (+)
and STM.loc_auto_key = LOC.loc_auto_key (+)
and STM.pcc_auto_key = PCC.pcc_auto_key (+)
and STM.pnm_auto_key = :pnm_auto_key
and STM.historical_flag = 'F'
"""
recs, fldNames = dbFetchAll(None, q, part, asDict=True)
return self.processFini(request, fs, form, payload=recs)
def get_workOrderStatusList(self, fs):
q = """ select wos_auto_key as "wos_auto_key"
, description as "description"
from wo_status
order by description
"""
recs, fldNames = dbFetchAll(None, q, fs, asDict=True)
return recs
def get_getWarehouseCodes(self, fs):
q = """ select whs_auto_key as "seq"
, warehouse_code as "code"
, description as "desc"
from warehouse
order by sequence
"""
recs, fldNames = dbFetchAll(None, q, fs, asDict=True)
if not recs:
raise ServerDbError("AQC-0001", "No part warehouse codes were found. Please relay this message to a system administrator.")
return recs
def get_partConditionCodes(self, fs):
q = """ select pcc_auto_key as "seq"
, condition_code as "code"
, description as "desc"
from part_condition_codes
order by sequence
"""
recs, fldNames = dbFetchAll(None, q, fs, asDict=True)
if not recs:
raise ServerDbError("AQC-0001", "No part condition codes were found. Please relay this message to a system administrator.")
return recs
def ajax_fld_partOrStock(self,
request,
frmName,
fldName=None,
msgType=ViewCapture.FORM_MSG_TYPE_NONE,
msg=None):
fs, form, flds, fldIdx, fld, data = self.processInitFld(
request, frmName, fldName)
# is it a control No/Id
whereClause = None
ctrlNo = None
ctrlId = None
if re.match('[0-9]{12}$', data):
ctrlNo = data[:6]
ctrlId = data[6:]
elif re.match('[0-9]{1,6}[,. ][0-9]{1,6}$', data):
ctrlNo, ctrlId = re.split('[,. ]', data)
if ctrlNo:
q = """ select STM.pnm_auto_key
from stock STM
where STM.ctrl_number = :ctrlNo
and STM.ctrl_id = :ctrlId """
row, fldNames = dbFetchOne(None, q,
dict(ctrlNo=ctrlNo, ctrlId=ctrlId))
if row:
fs['pnm_auto_key'], = row
whereClause = "PNM.pnm_auto_key = :pnm_auto_key"
if not whereClause:
whereClause = "1=2 "
if re.match('[0-9]+$', data): # it's all numeric
whereClause += " or PNM.pnm_auto_key = {0} ".format(data)
whereClause += " or PNM.pn_stripped like '{1}{0}{1}' ".format(
dbQuote(data), '%')
elif re.search('[\w]',
data): # it has at least one alphanumeric character
whereClause += " or upper(PNM.pn_stripped) like upper('{1}{0}{1}') ".format(
re.sub('[\W_]+', '', data), '%')
whereClause += " or upper(PNM.description) like upper('{1}{0}{1}') ".format(
re.sub('[\W_]+', '', data), '%')
q = """ with cq as ( select CQD.pnm_auto_key
, CQD.unit_price
, CQD.entry_date
from cq_detail CQD
, parts_master PNM
where CQD.pnm_auto_key = PNM.pnm_auto_key
and ({whereClause})
and CQD.cqd_auto_key = ( select max(cqd_auto_key)
from cq_detail
where pnm_auto_key = PNM.pnm_auto_key
)
),
so as ( select SOD.pnm_auto_key
, SOD.unit_price
, SOD.entry_date
from so_detail SOD
, parts_master PNM
where SOD.pnm_auto_key = PNM.pnm_auto_key
and ({whereClause})
and SOD.sod_auto_key = ( select max(sod_auto_key)
from so_detail
where pnm_auto_key = PNM.pnm_auto_key
)
)
select pnm_auto_key as "pnm_auto_key"
, pn as "pn"
, description as "desc"
, qty_oh as "qtyOH"
, qty_reserved as "qtyRes"
, qty_available as "qtyAvail"
, salePrice as "salePrice"
, saleDate as "saleDate"
, quotePrice as "quotePrice"
, quoteDate as "quoteDate"
from ( select PNM.pnm_auto_key
, PNM.pn
, PNM.description
, to_char(PNM.qty_oh) as qty_oh
, to_char(PNM.qty_reserved) as qty_reserved
, to_char(PNM.qty_available) as qty_available
, nvl(to_char(SO.unit_price), '-') as salePrice, nvl(to_char(SO.entry_date, 'yyyy-mm-dd'), '-') as saleDate
, nvl(to_char(CQ.unit_price), '-') as quotePrice, nvl(to_char(CQ.entry_date, 'yyyy-mm-dd'), '-') as quoteDate
from parts_master PNM
, so SO
, cq CQ
where 1=1
and PNM.pnm_auto_key = SO.pnm_auto_key (+)
and PNM.pnm_auto_key = CQ.pnm_auto_key (+)
and ({whereClause})
order by pn
)
where rownum < 100
""".format(whereClause=whereClause)
recs, fldNames = dbFetchAll(None, q, fs, asDict=True)
if not recs:
fld['value'] = data
fld['msgType'] = self.FLD_MSG_TYPE_INVALID
fld['msg'] = "No Part Found"
return self.processFini(request, fs, form)
fld['value'] = data
fld['msgType'] = self.FLD_MSG_TYPE_VALID
fld['msg'] = ""
self.fldsAppend(False, flds, self.createField_done())
self.fldsAppend(False, flds, self.createField_print())
self.fldsAppend(False, flds, self.createField_partList(listItems=recs))
return self.processFini(request, fs, form)
def get_workOrderList(self, fs, recentOnly=False, wildCard=None):
whereClause = ""
if wildCard: #match workOrders by scanned-task, workOrder, or partNumber
whereClause = "and ( 1=2 "
if re.match('[0-9]+[SCsc]$', wildCard): # could be a task capture
whereClause += " or WOT.wot_auto_key = {0} ".format(
wildCard[:-1])
elif re.match('[0-9]+$', wildCard): # it's all numeric
whereClause += " or WOO.woo_auto_key = {0} ".format(wildCard)
whereClause += " or PNM.pnm_auto_key = {0} ".format(wildCard)
whereClause += " or WOO.si_number like '{1}{0}{1}' ".format(
dbQuote(wildCard), '%')
whereClause += " or PNM.pn_stripped like '{1}{0}{1}' ".format(
dbQuote(wildCard), '%')
elif re.search(
'[\w]',
wildCard): # it has at least one alphanumeric character
whereClause += " or upper(WOO.si_number) like upper('{1}{0}{1}') ".format(
re.sub('[\W_]+', '', wildCard), '%')
whereClause += " or upper(PNM.pn_stripped) like upper('{1}{0}{1}') ".format(
re.sub('[\W_]+', '', wildCard), '%')
whereClause += " or upper(PNM.description) like upper('{1}{0}{1}') ".format(
re.sub('[\W_]+', '', wildCard), '%')
whereClause += ")"
if recentOnly:
whereClause += " and recent.woo_auto_key is not null"
q = """
with recentTasks as ( -- all direct tasks for user within one day of most recent activity
select distinct wot_auto_key
from wo_task_labor
where (
( start_time is not null
and stop_time is null
)
or
start_time > ( select max(start_time)
from wo_task_labor
where sysur_auto_key = :sysur_auto_key
and delete_date is null
) - 1
)
and delete_date is null
)
, recentIndirect as ( -- all indirect tasks for user within one day of most recent activity
select distinct wot_curr as wot_auto_key
from wo_barcode_labor
where log_time_curr > ( select max(log_time_curr)
from wo_barcode_labor
where sysur_curr = :sysur_auto_key
) - 1
)
, recentWorkOrder as ( -- workorders related to the recent direct and indirect tasks
select distinct woo_auto_key
from wo_task
where wot_auto_key in (select wot_auto_key from recentTasks)
or wot_auto_key in (select wot_auto_key from recentIndirect)
)
, symptom as (
select distinct WSL.woo_auto_key as WSL_woo_auto_key
, first_value(DBMS_LOB.substr(WSL.notes, 512) ignore nulls) over
(partition by WSL.woo_auto_key order by WSL.sequence, WSL.wsl_auto_key) as WSL_firstNote
from wo_symptom_list WSL
)
select distinct --needed as parts of where clause are optional
WOO.woo_auto_key as "woo_auto_key"
, WOO.si_number as "si_number"
, PNM.pn as "partNumber"
, PNM.description as "partDesc"
, nvl(symptom.WSL_firstNote, '') as "symptom"
, nvl(WOS.wos_auto_key, 0) as "wos_auto_key"
, nvl(WOS.description, 'Pending') as "wos_statusDesc"
from wo_operation WOO
, wo_task WOT
, parts_master PNM
, wo_status WOS
, recentWorkOrder recent
, symptom symptom
where WOO.woo_auto_key = WOT.woo_auto_key
and WOO.pnm_auto_key = PNM.pnm_auto_key (+)
and WOO.wos_auto_key = WOS.wos_auto_key (+)
and WOO.woo_auto_key = recent.woo_auto_key (+)
and WOO.woo_auto_key = symptom.WSL_woo_auto_key (+)
and WOO.open_flag = 'T'
{whereClause}
order by WOO.woo_auto_key
""".format(whereClause=whereClause)
recs, fldNames = dbFetchAll(None, q, fs, asDict=True)
return recs
def get_stockTiList(self, fs):
whereClause = ""
if fs.get('wot_auto_key'):
whereClause += " and (WOT.wot_auto_key = :wot_auto_key)"
if fs.get('partOrStockFilter'):
filter = fs.get('partOrStockFilter')
whereClause += " and (1=2"
# is it a control No/Id
ctrlNo = None
ctrlId = None
if re.match('[0-9]{12}$', filter):
ctrlNo = filter[:6]
ctrlId = filter[6:]
elif re.match('[0-9]{1,6}[,. ][0-9]{1,6}$', filter):
ctrlNo, ctrlId = re.split('[,. ]', filter)
if ctrlNo:
whereClause += " or (STM.ctrl_number = {} and STM.ctrl_id = {})".format(
ctrlNo, ctrlId)
if re.match('[0-9]+$', filter): # it's all numeric
whereClause += " or PNM.pnm_auto_key = {0} ".format(filter)
whereClause += " or PNM.pn_stripped like '{1}{0}{1}' ".format(
dbQuote(filter), '%')
elif re.search(
'[\w]',
filter): # it has at least one alphanumeric character
whereClause += " or upper(PNM.pn_stripped) like upper('{1}{0}{1}') ".format(
re.sub('[\W_]+', '', filter), '%')
whereClause += " or upper(PNM.description) like upper('{1}{0}{1}') ".format(
re.sub('[\W_]+', '', filter), '%')
whereClause += ")"
# ref: QC_WP_PKG.SPS_WP_STOCK_ACTIVITY
q = """ select stm_auto_key as "stm_auto_key"
, sti_auto_key as "sti_auto_key"
, to_char(tiQty) as "tiQty"
, activity as "activity"
, stockLine as "stockLine"
, partNum as "partNum"
, partDesc as "partDesc"
, partCond as "partCond"
, condLevel as "condLevel"
, tiType as "tiType"
, taskSeq as "taskSeq"
, taskDesc as "taskDesc"
, taskCode as "taskCode"
, serialNum as "serialNum"
from ( select STI.stm_auto_key
, STI.sti_auto_key
, case
when STI.ti_type = 'T'
and nvl(STM.qty_available,0) + nvl((select sum(qty_reserved)
from stock_reservations
where wob_auto_key = WOB.wob_auto_key),0)
< nvl(STI.qty,0) - nvl(STI.qty_reverse,0)
then
nvl(STM.qty_available,0) + nvl((select sum(qty_reserved)
from stock_reservations
where wob_auto_key = WOB.wob_auto_key),0)
else
nvl(STI.qty,0) - nvl(STI.qty_reverse,0)
end as tiQty
, QC_WP_PKG.get_wob_activity(WOB.activity) as activity
, STM.stock_line as stockLine
, PNM.pn as partNum
, PNM.description as partDesc
, PCC.condition_code as partCond
, WOB.cond_level as condLevel
, DECODE(STI.ti_type, 'I', 'Issue', 'T', 'Turn-In') as tiType
, WOT.sequence as taskSeq
, QC_WO_PKG2.get_taks_descr(WOB.wot_auto_key) as taskDesc
, WTM.description as taskCode
, STM.serial_number as serialNum
from wo_bom WOB
, wo_task WOT
, wo_task_master WTM
, wo_status WOS
, part_condition_codes PCC
, stock_ti STI
, parts_master PNM
, stock STM
where 1=1
and WOB.wot_auto_key = WOT.wot_auto_key (+)
and WOT.wtm_auto_key = WTM.wtm_auto_key (+)
and WOT.wos_auto_key = WOS.wos_auto_key (+)
and WOB.pcc_auto_key = PCC.pcc_auto_key (+)
and WOB.wob_auto_key = STI.wob_auto_key
and STI.stm_auto_key = STM.stm_auto_key (+)
and STM.pnm_auto_key = PNM.pnm_auto_key (+)
--
and WOB.woo_auto_key = :woo_auto_key
and ( (NVL(WOS.status_type,'Pending') not in ('Closed','Cancel','Defer'))
or (WOS.closed_allow_inv = 'T' and QC_SC_PKG.get_has_access(20441/*qsICFuncStockMaterClosed*/) = 'T'))
and STI.ti_type <> 'R'
{whereClause}
order by partNum
, taskSeq
, tiType
)
where tiQty > 0
and rownum < 100
""".format(whereClause=whereClause)
recs, fldNames = dbFetchAll(None, q, fs, asDict=True)
return recs
