Page 1 of 1
Database link for creditor purchase JobNo?
Posted:
Thu Sep 28, 2017 12:48 pm
by neil.interactit
Hi guys,
I'm stumped - and prepared to be embarrassed when you tell me how obvious this is! - but I can't trace the database linking.
For a given CR_BatchPayLines.CR_BatchPayLinesID, how do I traverse the database to the field(s) displayed in the JobNo column?
Cheers,
Neil.
Re: Database link for creditor purchase JobNo?
Posted:
Thu Sep 28, 2017 2:16 pm
by Mike.Sheen
CR_BatchPayLines.CR_TransID -> CR_Trans.TransID
then CR_Trans.SourceID links to different tables, depending on the CR_Trans.Source:
For example, when CR_Trans.Source == "Creditor Sourced Purchase"
then CR_Trans.SourceID -> CR_BatchTranLines.ReceiptLineID, CR_BatchTranLines.JB_JobStages_RecID -> JB_JobStages.RecID, JB_JobStages.JB_Stages_RecID -> JB_Stages.RecID and so on.
Make sense?
Re: Database link for creditor purchase JobNo?
Posted:
Thu Sep 28, 2017 4:26 pm
by neil.interactit
Almost!
I can successfully traverse to CR_BatchTranLines.JB_JobStages_RecID, but from there I can't find the (many-to-one) path to JB_Main?
Cheers,
Neil
Re: Database link for creditor purchase JobNo?
Posted:
Thu Sep 28, 2017 4:39 pm
by Scott.Pearce
JB_JobCostCentres is the missing stepping stone.
CR_BatchTranLines.JB_JobStages_RecID -> JB_JobStages.RecID, JB_JobStages.JB_JobCostCentres_RecID ->JB_JobCostCentres.RecID, JB_JobCostCentres.JobID -> JB_Main.JobID
Re: Database link for creditor purchase JobNo?
Posted:
Fri Sep 29, 2017 4:41 pm
by neil.interactit
Cool! Many thanks.
FWIW, here's my final SQL, traversing from CR_BatchPayLines to JB_Main ...
- Code: Select all
SELECT *
,ROW_NUMBER() OVER (PARTITION BY bp.LineNumber ORDER BY bp.LineNumber) AS RowNumber -- remove duplicate LineNumbers
FROM CR_BatchPayLines bp
LEFT JOIN CR_Trans tr ON bp.CR_TransID = tr.TransID
LEFT JOIN CR_BatchTranLines bt ON tr.SourceID = bt.ReceiptID
LEFT JOIN JB_JobStages js ON bt.JB_JobStages_RecID =js.RecID
LEFT JOIN JB_JobCostCentres jcc ON js.JB_JobCostCentres_RecID = jcc.RecID
LEFT JOIN JB_Main jb ON jcc.JobID = jb.JobID
WHERE tr.InvRemitNo = bt.RemitNo
AND tr.CreditorID = bt.AccountID
AND bp.CR_BatchPayID = (SELECT CR_BatchPayID FROM CR_BatchPayment WHERE BatchNo = '006970')
AND tr.Source = 'Creditor Sourced Purchase'
Thanks again,
Neil.