project QadFinancials > class TConCheck > method TechnicalValidation10APMatchingPO
Description
PO Receipts accounts with Receipts
Parameters
| iiCompanyID | input | integer | |
| iiPeriodID | input | integer | |
| iiSelectedYearPeriod | input | integer | |
| itFromDate | input | date | |
| itToDate | input | date | |
| oiErrorNumbers | output | integer | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tconcheck.p)
/* ================================================================================= */
/* This is the method for PO Receipts accounts with Receipts */
/* ================================================================================= */
assign oiReturnStatus = -98.
PUT STREAM sTechOut unformatted " " skip.
PUT STREAM sTechOut unformatted vcPrefix2 + "START Validation of PO Receipts accounts with Receipts (" + STRING (itFromDate) + ")" skip.
find first company where company.company_id = iiCompanyID no-lock no-error.
assign viCurrDomainID = company.domain_id.
find first domains where domains.Domain_ID = viCurrDomainID no-lock no-error.
assign vcCurrDomain = domains.DomainCode.
IPR_APMATCHING_Check:
do:
/* init variables */
for each APMatching no-lock
where APMatchingStatus = {&APMATCHINGSTATUS-FINISHED}
and APMatchingDate >= itFromDate
and APMatchingDate <= itToDate
and APMatching.Company_ID = iiCompanyID,
first CInvoice no-lock
where CInvoice.CInvoice_ID = APMatching.CInvoice_ID,
first Journal of CInvoice no-lock,
each APMatchingLn of APMatching no-lock
where APMatchingLn.APMatchingLnIsPvodFinished = yes:
assign
viAPMatchingLnRecordCount = viAPMatchingLnRecordCount + 1
vlRecordExists = no /* Check for pvo_mstr records*/
vcInvoiceNbr = string(CInvoicePostingYear, "9999")
+ "/" + trim(string(JournalCode, "x(8)"))
+ string(CInvoiceVoucher, "999999999").
/*
for each pvo_mstr no-lock
where pvo_domain = APMatchingLn.PvoDomain
and pvo_id = APMatchingLn.PvoID:
assign
viMatchingPvoMstrRecordCount = viMatchingPvoMstrRecordCount + 1
vlRecordExists = yes.
end.
*/
CREATE BUFFER vhPvoMstrBuffer FOR TABLE "pvo_mstr" .
CREATE QUERY vhPvoMstrQuery.
vhPvoMstrQuery:SET-BUFFERS(vhPvoMstrBuffer).
vhPvoMstrQuery:QUERY-PREPARE("FOR EACH pvo_mstr where pvo_domain = " + quoter(APMatchingLn.PvoDomain) + " AND
pvo_id = " + string(APMatchingLn.PvoID)+ " NO-LOCK").
vhPvoMstrQuery:QUERY-OPEN().
vhPvoMstrQuery:GET-FIRST().
DO WHILE NOT vhPvoMstrQuery:QUERY-OFF-END:
vlRecordExists = yes.
viMatchingPvoMstrRecordCount = viMatchingPvoMstrRecordCount + 1.
vhPvoMstrQuery:GET-NEXT().
END.
vhPvoMstrQuery:QUERY-CLOSE.
DELETE OBJECT vhPvoMstrBuffer no-error.
DELETE OBJECT vhPvoMstrQuery no-error.
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0071) No pvo_mstr table record exists for APMatchingLn.PvoDomain = " + APMatchingLn.PvoDomain
+ " AND APMatchingLn.PvoID = " + string(APMatchingLn.PvoID) skip.
assign viMissingPvoMstrRecordCount = viMissingPvoMstrRecordCount + 1.
end.
else do:
assign vlRecordExists = no.
/* Check for pvod_det records*/
/* for each pvod_det no-lock
where pvod_det.pvod_domain = APMatchingLn.PvoDomain
and pvod_det.pvod_ID = APMatchingLn.PvoID
and pvod_det.pvod_id_line = APMatchingLn.PvodLineID:
*/
CREATE BUFFER vhPvodDetBuffer FOR TABLE "pvod_det" .
CREATE QUERY vhPvodDetQuery.
vhPvodDetQuery:SET-BUFFERS(vhPvodDetBuffer).
vhPvodDetQuery:QUERY-PREPARE("FOR EACH pvod_det where pvod_domain = " + quoter(APMatchingLn.PvoDomain) + " AND
pvod_id = " + string(APMatchingLn.PvoID)+ " AND
pvod_id_line = " + string(APMatchingLn.PvodLineID) + " NO-LOCK").
vhPvodDetQuery:QUERY-OPEN().
vhPvodDetQuery:GET-FIRST().
DO WHILE NOT vhPvodDetQuery:QUERY-OFF-END:
assign
viMatchingPvodDetRecordCount = viMatchingPvodDetRecordCount + 1
vlRecordExists = yes.
/* Check that the invoice_id field has been set correctly*/
if vhPvodDetBuffer::pvod_cinvoice_id <> APMatching.CInvoice_ID then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0072) Inconsistent field values for APMatchingLn.PvoDomain = " + APMatchingLn.pvodomain
+ " AND Invoice Number = " + vcInvoiceNbr
+ " AND APMatchingLn.pvoID = " + string(APMatchingLn.pvoID)
+ " AND APMatchingLn.pvodLineID = " + string(APMatchingLn.pvodLineID)
+ ": APMatchingLn.CInvoice_ID = " + string(APMatching.CInvoice_ID)
+ " BUT pvod_det.pvod_cinvoice_id = " + string(vhPvodDetBuffer::pvod_cinvoice_id) skip.
end.
vhPvodDetQuery:GET-NEXT().
END. /* for each pvod_det no-lock */
vhPvodDetQuery:QUERY-CLOSE.
DELETE OBJECT vhPvodDetBuffer no-error.
DELETE OBJECT vhPvodDetQuery no-error.
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0073) No pvod_det table record exists for APMatchingLn.PvoDomain = " + APMatchingLn.PvoDomain
+ " AND APMatchingLn.PvoID = " + string(APMatchingLn.PvoID)
+ " AND APMatchingLn.PvodLineID = " + string(APMatchingLn.PvodLineID) skip.
assign viMissingPvodDetRecordCount = viMissingPvodDetRecordCount + 1.
end.
end. /* else do: */
/* Check for vph_hist record with vph_pvod_id_line = 0*/
assign vlRecordExists = no.
/* for first vph_hist no-lock
where vph_domain = APMatchingLn.pvodomain
and vph_ref = vcInvoiceNbr
and vph_pvo_id = APMatchingLn.pvoid
and vph_pvod_id_line = 0:
*/
CREATE BUFFER vhVphHistBuffer FOR TABLE "vph_hist" .
CREATE QUERY vhVphHistQuery.
vhVphHistQuery:SET-BUFFERS(vhVphHistBuffer).
vhVphHistQuery:QUERY-PREPARE("FOR Each vph_hist where vph_domain = " + quoter(APMatchingLn.PvoDomain) + " AND
vph_ref = " + quoter(vcInvoiceNbr) + " AND
vph_pvo_id = " + string(APMatchingLn.PvoID)+ " AND
vph_pvod_id_line = 0 NO-LOCK").
vhVphHistQuery:QUERY-OPEN().
vhVphHistQuery:GET-FIRST().
IF NOT vhVphHistQuery:QUERY-OFF-END THEN
assign
viMatchingVphHistRecordCount = viMatchingVphHistRecordCount + 1
vlRecordExists = yes.
vhVphHistQuery:QUERY-CLOSE().
DELETE OBJECT vhVphHistBuffer no-error.
DELETE OBJECT vhVphHistQuery no-error.
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0074) No vph_hist table record (with vph_pvod_id_line = 0) exists for APMatchingLn.PvoDomain = " + APMatchingLn.pvodomain
+ " AND Invoice Number = " + vcInvoiceNbr
+ " AND APMatchingLn.pvoID = " + string(APMatchingLn.pvoID) skip.
assign viMissingVphHistRecordCount = viMissingVphHistRecordCount + 1.
end.
/* Check for vph_hist records with vph_pvod_id_line = APMatchingLn.PvodLineID*/
assign vlRecordExists = no.
/*
for first vph_hist no-lock
where vph_domain = APMatchingLn.pvodomain
and vph_ref = vcInvoiceNbr
and vph_pvo_id = APMatchingLn.pvoid
and vph_pvod_id_line = APMatchingLn.PvodLineID:
*/
CREATE BUFFER vhVphHistBuffer FOR TABLE "vph_hist" .
CREATE QUERY vhVphHistQuery.
vhVphHistQuery:SET-BUFFERS(vhVphHistBuffer).
vhVphHistQuery:QUERY-PREPARE("FOR Each vph_hist where vph_domain = " + quoter(APMatchingLn.PvoDomain) + " AND
vph_ref = " + quoter(vcInvoiceNbr) + " AND
vph_pvo_id = " + string(APMatchingLn.PvoID)+ " AND
vph_pvod_id_line = " + string(APMatchingLn.PvodLineID) + " NO-LOCK").
vhVphHistQuery:QUERY-OPEN().
vhVphHistQuery:Get-first().
IF NOT vhVphHistQuery:QUERY-OFF-END THEN DO:
assign
viMatchingVphHistRecordCount = viMatchingVphHistRecordCount + 1
vlRecordExists = yes.
/* Check that the invoice_id field has been set correctly*/
if vhVphHistBuffer::vph_cinvoice_id <> APMatching.CInvoice_ID then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0072) Inconsistent field values for APMatchingLn.PvoDomain = " + APMatchingLn.pvodomain
+ " AND Invoice Number = " + vcInvoiceNbr
+ " AND APMatchingLn.pvoID = " + string(APMatchingLn.pvoID)
+ " AND APMatchingLn.pvodLineID = " + string(APMatchingLn.pvodLineID)
+ ": APMatchingLn.CInvoice_ID = " + string(APMatching.CInvoice_ID)
+ " BUT vph_hist.vph_cinvoice_id = " + string(vhVphHistBuffer::vph_cinvoice_id) skip.
end.
vhVphHistQuery:GET-NEXT().
end. /* for first vph_hist no-lock */
vhVphHistQuery:QUERY-CLOSE().
DELETE OBJECT vhVphHistBuffer no-error.
DELETE OBJECT vhVphHistQuery no-error.
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0075) No vph_hist table record exists for APMatchingLn.PvoDomain = " + APMatchingLn.pvodomain
+ " AND Invoice Number = " + vcInvoiceNbr
+ " AND APMatchingLn.pvoID = " + string(APMatchingLn.pvoID)
+ " AND APMatchingLn.pvodLineID = " + string(APMatchingLn.pvodLineID) skip.
assign viMissingVphHistRecordCount = viMissingVphHistRecordCount + 1.
end.
end. /* for each APMatching no-lock */
/*
for each pvod_det no-lock
where pvod_cinvoice_id > 0 and
pvod_trans_date >= Period.PeriodStartDate and
pvod_trans_date <= Period.PeriodEndDate and
pvod_domain = vcCurrDomain,
first po_mstr
where po_mstr.po_nbr = pvod_det.pvod_order and
po_domain = vcCurrDomain,
first si_mstr
where po_mstr.po_site = si_mstr.si_site and
si_domain = vcCurrDomain,
first Company
where Company.CompanyCode = si_mstr.si_entity and
Company.Company_ID = iiCompanyID:
*/
CREATE BUFFER vhPvodDet1Buffer FOR TABLE "pvod_det".
CREATE BUFFER vhPoMstrBuffer FOR TABLE "po_mstr".
CREATE BUFFER vhSiMstrBuffer FOR TABLE "si_mstr".
CREATE BUFFER vhCompanyBuffer FOR TABLE "Company".
CREATE BUFFER vhPoDetMstrBuffer FOR TABLE "pod_det".
CREATE QUERY vhMultiTableQuery.
vhMultiTableQuery:SET-BUFFERS(vhPvodDet1Buffer,vhPoMstrBuffer,vhPoDetMstrBuffer).
vhMultiTableQuery:QUERY-PREPARE("FOR EACH pvod_det no-lock where pvod_cinvoice_id > 0 AND
pvod_trans_date >= " + string(itFromDate) + " AND
pvod_trans_date <= " + string(itToDate) + " AND
pvod_domain = " + quoter(vcCurrDomain) + ",
FIRST po_mstr no-lock where po_mstr.po_nbr = pvod_det.pvod_order AND
po_mstr.po_domain = " + quoter(vcCurrDomain) + ",
FIRST pod_det no-lock where pod_domain = " + quoter(vcCurrDomain) + " AND
pod_nbr = pvod_order and pod_line = pvod_order_line").
vhMultiTableQuery:QUERY-OPEN().
vhMultiTableQuery:GET-FIRST().
DO WHILE NOT vhMultiTableQuery:QUERY-OFF-END:
assign vlFoundSiteEntity = yes.
CREATE QUERY vhMultiTableQuery2.
vhMultiTableQuery2:SET-BUFFERS(vhSiMstrBuffer,vhCompanyBuffer).
if vhPoMstrBuffer::po_site = ? or vhPoMstrBuffer::po_site = "":U
then do:
vhMultiTableQuery2:QUERY-PREPARE("for each si_mstr NO-LOCK where " + quoter(vhPoDetMstrBuffer::pod_po_site) + " = si_mstr.si_site AND si_domain = " +
quoter(vcCurrDomain) + ", first Company where Company.CompanyCode = si_mstr.si_entity and Company.Company_ID = " + string(iiCompanyID) + " no-lock").
vhMultiTableQuery2:QUERY-OPEN().
vhMultiTableQuery2:GET-FIRST().
DO WHILE NOT vhMultiTableQuery2:QUERY-OFF-END:
assign vlFoundSiteEntity = yes.
vhMultiTableQuery2:GET-NEXT().
END.
end.
else do:
vhMultiTableQuery2:QUERY-PREPARE("for each si_mstr where " + quoter(vhPoMstrBuffer::po_site) + " = si_mstr.si_site and si_domain = " +
quoter(vcCurrDomain) + ", first Company no-lock where Company.CompanyCode = si_mstr.si_entity and Company.Company_ID = " + string(iiCompanyID)).
vhMultiTableQuery2:QUERY-OPEN().
vhMultiTableQuery2:GET-FIRST().
DO WHILE NOT vhMultiTableQuery2:QUERY-OFF-END:
assign vlFoundSiteEntity = yes.
vhMultiTableQuery2:GET-NEXT().
END.
end.
vhMultiTableQuery2:QUERY-CLOSE().
DELETE OBJECT vhMultiTableQuery2 no-error.
if vlFoundSiteEntity = yes
then do:
assign
viPvodDetRecordCount = viPvodDetRecordCount + 1
vlRecordExists = no.
for each APMatching no-lock
where APMatching.APMatchingStatus = {&APMATCHINGSTATUS-FINISHED}
and APMatching.CInvoice_ID = vhPvodDet1Buffer::pvod_cinvoice_id:
assign
viMatchingAPMatchingForPvodDet = viMatchingAPMatchingForPvodDet + 1
vlRecordExists = no.
for each APMatchingLn of APMatching no-lock
where APMatchingLn.PvoDomain = vhPvodDet1Buffer::pvod_domain
and APMatchingLn.PvoID = vhPvodDet1Buffer::pvod_id
and APMatchingLn.APMatchingLnIsPvodFinished = yes:
assign
viMatchingAPMatchingLnForPvodDe = viMatchingAPMatchingLnForPvodDe + 1
vlRecordExists = yes.
end. /* for each APMatchingLn of APMatching no-lock */
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0076) No APMatchingLn table record exists for "
+ " pvod_domain = " + vhPvodDet1Buffer::pvod_domain
+ " AND pvod_id = " + string(vhPvodDet1Buffer::pvod_id)
+ " AND pvod_id_line = " + string(vhPvodDet1Buffer::pvod_id_line)
+ " AND pvod_cinvoice_id = " + string(vhPvodDet1Buffer::pvod_cinvoice_id) skip.
assign viMissingAPMatchingLnForPvodDet = viMissingAPMatchingLnForPvodDet + 1.
end.
assign vlRecordExists = yes.
end. /* for each APMatching no-lock */
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0077) No APMatching table record with APMatchingStatus = " + {&APMATCHINGSTATUS-FINISHED} + " exists for "
+ " pvod_domain = " + vhPvodDet1Buffer::pvod_domain
+ " AND pvod_id = " + string(vhPvodDet1Buffer::pvod_id)
+ " AND pvod_id_line = " + string(vhPvodDet1Buffer::pvod_id_line)
+ " AND pvod_cinvoice_id = " + string(vhPvodDet1Buffer::pvod_cinvoice_id) skip.
assign viMissingAPMatchingForPvodDet = viMissingAPMatchingForPvodDet + 1.
end.
end. /* if vlFoundSiteEntity = yes */
vhMultiTableQuery:GET-NEXT().
end. /* for each pvod_det no-lock */
vhMultiTableQuery:QUERY-CLOSE().
if valid-handle (vhPvodDet1Buffer) THEN DELETE OBJECT vhPvodDet1Buffer no-error.
if valid-handle (vhPoMstrBuffer) THEN DELETE OBJECT vhPoMstrBuffer no-error.
if valid-handle (vhSiMstrBuffer) THEN DELETE OBJECT vhSiMstrBuffer no-error.
if valid-handle (vhCompanyBuffer) THEN DELETE OBJECT vhCompanyBuffer no-error.
if valid-handle (vhPoDetMstrBuffer ) THEN DELETE OBJECT vhPoDetMstrBuffer no-error.
DELETE OBJECT vhMultiTableQuery no-error.
CREATE BUFFER vhVphHist1Buffer FOR TABLE "vph_hist".
CREATE BUFFER vhPoMstrBuffer FOR TABLE "po_mstr".
CREATE BUFFER vhSiMstrBuffer FOR TABLE "si_mstr".
CREATE BUFFER vhCompanyBuffer FOR TABLE "Company".
CREATE BUFFER vhPoDetMstrBuffer FOR TABLE "pod_det".
CREATE QUERY vhMultiTableQuery.
vhMultiTableQuery:SET-BUFFERS(vhVphHist1Buffer,vhPoMstrBuffer,vhPoDetMstrBuffer).
vhMultiTableQuery:QUERY-PREPARE("FOR EACH vph_hist no-lock where vph_cinvoice_id > 0 AND
vph_inv_date >= " + string(itFromDate) + " AND
vph_inv_date <= " + string(itToDate) + " AND
vph_domain = " + quoter(vcCurrDomain) + ",
FIRST po_mstr no-lock where po_mstr.po_nbr = vph_hist.vph_nbr AND
po_mstr.po_domain = " + quoter(vcCurrDomain) + ",
FIRST pod_det no-lock where pod_domain = " + quoter(vcCurrDomain) + " AND
pod_nbr = vph_nbr and pod_line = vph_pvod_id_line").
vhMultiTableQuery:QUERY-OPEN().
vhMultiTableQuery:GET-FIRST().
vphHistLoop:
DO WHILE NOT vhMultiTableQuery:QUERY-OFF-END:
assign vlFoundSiteEntity = no.
CREATE QUERY vhMultiTableQuery2.
vhMultiTableQuery2:SET-BUFFERS(vhSiMstrBuffer,vhCompanyBuffer).
if vhPoMstrBuffer::po_site = ? or vhPoMstrBuffer::po_site = "":U
then do:
vhMultiTableQuery2:QUERY-PREPARE("for each si_mstr where " + quoter(vhPoDetMstrBuffer::pod_po_site) + " = si_mstr.si_site and si_domain = " +
quoter(vcCurrDomain) + ", first Company no-lock where Company.CompanyCode = si_mstr.si_entity and Company.Company_ID = " + string(iiCompanyID)).
vhMultiTableQuery2:QUERY-OPEN().
vhMultiTableQuery2:GET-FIRST().
DO WHILE NOT vhMultiTableQuery2:QUERY-OFF-END:
assign vlFoundSiteEntity = yes.
vhMultiTableQuery2:GET-NEXT().
END.
end.
else do:
vhMultiTableQuery2:QUERY-PREPARE("for each si_mstr where " + quoter(vhPoMstrBuffer::po_site) + " = si_mstr.si_site and si_domain = " +
quoter(vcCurrDomain) + ", first Company no-lock where Company.CompanyCode = si_mstr.si_entity and Company.Company_ID = " + string(iiCompanyID)).
vhMultiTableQuery2:QUERY-OPEN().
vhMultiTableQuery2:GET-FIRST().
DO WHILE NOT vhMultiTableQuery2:QUERY-OFF-END:
assign vlFoundSiteEntity = yes.
vhMultiTableQuery2:GET-NEXT().
END.
end.
vhMultiTableQuery2:QUERY-CLOSE().
DELETE OBJECT vhMultiTableQuery2 no-error.
if vlFoundSiteEntity = yes
then do:
assign
viVphHistRecordCount = viVphHistRecordCount + 1
vlRecordExists = no.
for each APMatching no-lock
where APMatching.CInvoice_ID = vhVphHist1Buffer::vph_cinvoice_id
and APMatching.APMatchingStatus = {&APMATCHINGSTATUS-FINISHED}:
assign
viMatchingAPMatchingForVphHist = viMatchingAPMatchingForVphHist + 1
vlRecordExists = no.
/*There will be no APMatchingLn record for vph_hist records where vph_pvod_id_line = 0*/
if vhVphHist1Buffer::vph_pvod_id_line = 0 then do:
assign viVphHistZeroLine = viVphHistZeroLine + 1.
vhMultiTableQuery:GET-NEXT().
next vphHistLoop.
end.
for each APMatchingLn of APMatching no-lock
where APMatchingLn.PvoDomain = vhVphHist1Buffer::vph_domain
and APMatchingLn.PvoID = vhVphHist1Buffer::vph_pvo_id
and APMatchingln.PvodLineID = vhVphHist1Buffer::vph_pvod_id_line:
assign
viMatchingAPMatchingLnForVphHis = viMatchingAPMatchingLnForVphHis + 1
vlRecordExists = yes.
end.
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0078) No APMatchingLn table record exists for "
+ "vph_domain = " + vhVphHist1Buffer::vph_domain
+ " AND vph_ref = " + vhVphHist1Buffer::vph_ref
+ " AND vph_pvo_id = " + string(vhVphHist1Buffer::vph_pvo_id)
+ " AND vph_pvod_id_line = " + string(vhVphHist1Buffer::vph_pvod_id_line)
+ " AND vph_cinvoice_id = " + string(vhVphHist1Buffer::vph_cinvoice_id) skip.
assign viMissingAPMatchingLnForVphHist = viMissingAPMatchingLnForVphHist + 1.
end.
assign vlRecordExists = yes.
end. /* for each APMatching no-lock */
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0079) No APMatching table record with APMatchingStatus = " + {&APMATCHINGSTATUS-FINISHED} + " exists for "
+ " vph_domain = " + vhVphHist1Buffer::vph_domain
+ " AND vph_ref = " + vhVphHist1Buffer::vph_ref
+ " AND vph_pvo_id = " + string(vhVphHist1Buffer::vph_pvo_id)
+ " AND vph_pvod_id_line = " + string(vhVphHist1Buffer::vph_pvod_id_line)
+ " AND vph_cinvoice_id = " + string(vhVphHist1Buffer::vph_cinvoice_id) skip.
assign viMissingAPMatchingForVphHist = viMissingAPMatchingForVphHist + 1.
end.
end. /* if vlFoundSiteEntity = yes */
vhMultiTableQuery:GET-NEXT().
end. /* for each vph_hist no-lock */
vhMultiTableQuery:QUERY-CLOSE().
if valid-handle (vhVphHist1Buffer) THEN DELETE OBJECT vhVphHist1Buffer no-error.
if valid-handle (vhPoMstrBuffer) THEN DELETE OBJECT vhPoMstrBuffer no-error.
if valid-handle (vhSiMstrBuffer) THEN DELETE OBJECT vhSiMstrBuffer no-error.
if valid-handle (vhCompanyBuffer) THEN DELETE OBJECT vhCompanyBuffer no-error.
if valid-handle (vhPoDetMstrBuffer ) THEN DELETE OBJECT vhPoDetMstrBuffer no-error.
DELETE OBJECT vhMultiTableQuery no-error.
/*
for each ieh_hist no-lock
where ieh_cinvoice_id > 0 and
ieh_inv_date >= Period.PeriodStartDate and
ieh_inv_date <= Period.PeriodEndDate and
ieh_domain = vcCurrDomain,
first si_mstr
where si_mstr.si_site = ieh_site and
si_domain = vcCurrDomain,
first Company
where Company.CompanyCode = si_mstr.si_entity and
Company.Company_ID = iiCompanyID:
assign
viIehHistRecordCount = viIehHistRecordCount + 1
vlRecordExists = no.
for each APMatching no-lock
where APMatching.CInvoice_ID = ieh_cinvoice_id
and APMatching.APMatchingStatus = "FINISHEDAPMATCHING":
assign
viMatchingAPMatchingForIehHist = viMatchingAPMatchingForIehHist + 1
vlRecordExists = yes.
end.
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0080) No APMatching table record with APMatchingStatus = " + {&APMATCHINGSTATUS-FINISHED} + " exists for ieh_hist.ieh_cinvoice_id = "
+ string(ieh_hist.ieh_cinvoice_id) skip.
assign viMissingAPMatchingForIehHist = viMissingAPMatchingForIehHist + 1.
end.
end. /* for each ieh_hist no-lock */
*/
/*FINS-4921*/
define variable vhIehHist as handle no-undo.
define variable qhIehHist as handle no-undo.
create query qhIehHist in widget-pool "non-persistent".
create buffer vhIehHist for table "ieh_hist" in widget-pool "non-persistent".
CREATE BUFFER vhSiMstrBuffer FOR TABLE "si_mstr".
CREATE BUFFER vhCompanyBuffer FOR TABLE "Company".
qhIehHist:set-buffers(vhIehHist,vhSiMstrBuffer,vhCompanyBuffer).
qhIehHist:query-prepare("for each ieh_hist no-lock where ieh_cinvoice_id > 0 and
ieh_inv_date >= " + string(itFromDate) + " and
ieh_inv_date <= " + string(itToDate) + " and
ieh_domain = " + quoter(vcCurrDomain) + " ,
first si_mstr no-lock where si_mstr.si_site = ieh_hist.ieh_site and
si_domain = " + quoter(vcCurrDomain) + " ,
first Company where company.CompanyCode = si_mstr.si_entity and
Company.Company_ID = " + string(iiCompanyID) + " no-lock").
qhIehHist:query-open.
qhIehHist:get-first().
repeat while not qhIehHist:query-off-end:
assign
viIehHistRecordCount = viIehHistRecordCount + 1
vlRecordExists = no.
for each APMatching no-lock
where APMatching.CInvoice_ID = vhIehHist::ieh_cinvoice_id
and APMatching.APMatchingStatus = "FINISHEDAPMATCHING":
assign
viMatchingAPMatchingForIehHist = viMatchingAPMatchingForIehHist + 1
vlRecordExists = yes.
end.
if not vlRecordExists then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0080) No APMatching table record with APMatchingStatus = " + {&APMATCHINGSTATUS-FINISHED} + " exists for ieh_hist.ieh_cinvoice_id = "
+ string(vhIehHist::ieh_cinvoice_id) skip.
assign viMissingAPMatchingForIehHist = viMissingAPMatchingForIehHist + 1.
end.
qhIehHist:get-next().
end.
qhIehHist:query-close.
if valid-handle (vhSiMstrBuffer) THEN DELETE OBJECT vhSiMstrBuffer no-error.
if valid-handle (vhCompanyBuffer) THEN DELETE OBJECT vhCompanyBuffer no-error.
delete object qhIehHist no-error.
assign qhIehHist = ?.
delete object vhIehHist no-error.
/* Pvo_mstr.cinvoice_id field should be non zero if all its pvod_det records have their
cinvoice_id field populated*/
/* for each pvo_mstr no-lock
where pvo_cinvoice_id > 0 and
pvo_domain = vcCurrDomain,
first po_mstr
where po_mstr.po_nbr = pvo_mstr.pvo_order and
po_domain = vcCurrDomain,
first si_mstr
where po_mstr.po_site = si_mstr.si_site and
si_domain = vcCurrDomain,
first Company
where Company.CompanyCode = si_mstr.si_entity and
Company.Company_ID = iiCompanyID:
*/
CREATE BUFFER vhPvoMstrBuffer FOR TABLE "pvo_mstr".
CREATE BUFFER vhPoMstrBuffer FOR TABLE "po_mstr".
CREATE BUFFER vhSiMstrBuffer FOR TABLE "si_mstr".
CREATE BUFFER vhCompanyBuffer FOR TABLE "Company".
CREATE QUERY vhMultiTableQuery.
vhMultiTableQuery:SET-BUFFERS(vhPvoMstrBuffer,vhPoMstrBuffer,vhSiMstrBuffer,vhCompanyBuffer).
vhMultiTableQuery:QUERY-PREPARE("FOR EACH pvo_mstr no-lock where pvo_cinvoice_id > 0 AND
pvo_trans_date >= " + string(itFromDate) + " AND
pvo_trans_date <= " + string(itToDate) + " AND
pvo_domain = " + quoter(vcCurrDomain) + ",
FIRST po_mstr no-lock where po_mstr.po_nbr = pvo_mstr.pvo_order AND
po_mstr.po_domain = " + quoter(vcCurrDomain) + ",
FIRST si_mstr no-lock where si_mstr.si_site = po_mstr.po_site AND
si_mstr.si_domain = " + quoter(vcCurrDomain) + ",
FIRST Company where CompanyCode = si_mstr.si_entity and
Company_ID = " + string(iiCompanyID) + " NO-LOCK").
vhMultiTableQuery:QUERY-OPEN().
vhMultiTableQuery:GET-FIRST().
DO WHILE NOT vhMultiTableQuery:QUERY-OFF-END:
CREATE BUFFER vhPvodDetBuffer FOR TABLE "pvod_det".
CREATE QUERY vhPvodDetQuery.
vhPvodDetQuery:SET-BUFFERS(vhPvodDetBuffer).
vhPvodDetQuery:QUERY-PREPARE("FOR EACH pvod_det where pvod_domain = " + quoter(vhPvoMstrBuffer::pvo_domain) + " and
pvod_cinvoice_id = 0 and
pvod_id = " + string(vhPvoMstrBuffer::pvo_id) + " no-lock " ).
vhPvodDetQuery:QUERY-OPEN().
vhPvodDetQuery:GET-FIRST().
if NOT vhPvodDetQuery:QUERY-OFF-END
/*if can-find(first pvod_det no-lock
where pvod_domain = pvo_domain
and pvod_id = pvo_id
and pvod_cinvoice_id = 0) */
then do:
assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix +
"** (0081) pvo_mstr.cinvoice_id field is non zero but not all child pvod_det records have the cinvoice_id field set"
+ " for record with pvo_mstr.pvo_domain = " + vhPvoMstrBuffer::pvo_domain + " and pvo_id = " + string(vhPvoMstrBuffer::pvo_id) skip.
end.
vhPvodDetQuery:QUERY-CLOSE().
DELETE OBJECT vhPvodDetQuery no-error.
DELETE OBJECT vhPvodDetBuffer no-error.
vhMultiTableQuery:GET-NEXT().
end.
vhMultiTableQuery:Query-close.
if valid-handle (vhPvoMstrBuffer) THEN DELETE OBJECT vhPvoMstrBuffer no-error.
if valid-handle (vhPoMstrBuffer) THEN DELETE OBJECT vhPoMstrBuffer no-error.
if valid-handle (vhSiMstrBuffer) THEN DELETE OBJECT vhSiMstrBuffer no-error.
if valid-handle (vhCompanyBuffer) THEN DELETE OBJECT vhCompanyBuffer no-error.
DELETE OBJECT vhMultiTableQuery no-error.
/************************************************************************/
/* Log summary results */
/************************************************************************/
PUT STREAM sTechOut unformatted vcPrefix + "Summary results:" skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in Financials table APMatchingLn = " + string(viAPMatchingLnRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table APMatchingLn found in pvo_mstr = " + string(viMatchingPvoMstrRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table APMatchingLn not found in pvo_mstr = " + string(viMissingPvoMstrRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table APMatchingLn found in pvod_det = " + string(viMatchingPvodDetRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table APMatchingLn not found in pvod_det = " + string(viMissingPvoMstrRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table APMatchingLn found in table vph_hist = " + string(viMatchingVphHistRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table APMatchingLn not found in vph_hist = " + string(viMissingVphHistRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in Operations table pvod_det = " + string(viPvodDetRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table pvod_det found in table APMatching = " + string(viMatchingAPMatchingForPvodDet) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table pvod_det not found in table APMatching = " + string(viMissingAPMatchingForPvodDet) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table pvod_det found in table APMatchingLn = " + string(viMatchingAPMatchingLnForPvodDe) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table pvod_det not found in table APMatchingLn = " + string(viMissingAPMatchingLnForPvodDet) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in Operations table vph_hist = " + string(viVphHistRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records with vph_hist.vph_pvod_id_line equals zero = " + string(viVphHistZeroLine) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table vph_hist found in table APMatching = " + string(viMatchingAPMatchingForVphHist) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table vph_hist not found in table APMatching = " + string(viMissingAPMatchingForVphHist) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table vph_hist found in table APMatchingLn = " + string(viMatchingAPMatchingLnForVphHis) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table vph_hist not found in table APMatchingLn = " + string(viMissingAPMatchingLnForVphHist) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in Operations table ieh_hist = " + string(viIehHistRecordCount) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table ieh_hist found in table APMatching = " + string(viMatchingAPMatchingForIehHist) skip.
PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table ieh_hist not found in table APMatching = " + string(viMissingAPMatchingForIehHist) skip.
end. /* IPR_APMATCHING_Check: */
PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation PO Receipts accounts with Receipts." skip.
if oiReturnStatus = -98
then assign oiReturnStatus = 0.