I have just enough knowledge to be dangerous to myself and my employer.
I have a sql query which I have added to the command and create the necessary parameter. However, when I try to find the field it states they are not mapped. And I do not have the option to map them. I can place other sql commands in and received data. I am using someonelse orginal report and modifying report to look at the financial group and different field to calculate ARChg. I could not figure how to filter on two different date source without having to us the query.
Do you see anything that would be prohibiting me from get my table fields?
Please Help!!!
--Accounts Receivable Summary by Financial Group report
DECLARE @ARChg TABLE (fgc CHAR(10), archg MONEY NOT NULL)
INSERT INTO @ARChg
(fgc,
archg)
SELECT a.financialgroup_id AS fgc,
Isnull(SUM(Isnull(cpt.invoicecpt_feeamount, 0)), 0) AS archg
FROM lkup_financialgroup a
JOIN bill_invoice bip
ON ( a.financialgroup_id = bip.financialgroup_id )
AND bip.invoice_recordstate = 0
AND bip.invoice_id <> '0000000000'
LEFT JOIN bill_invoicecpt cpt
ON ( bip.invoice_id = cpt.invoice_id )
AND ( cpt.invoicecpt_recordstate = 0
OR cpt.invoicecpt_recordstate IS NULL )
AND cpt.invoicecpt_postdate < {?Start Date}
AND cpt.invoicecpt_id <> '0000000000'
WHERE a.financialgroup_recordstate = 0
AND a.financialgroup_id <> '0000000000'
GROUP BY a.financialgroup_id
----------
DECLARE @ARPay TABLE (fgc CHAR(10), arpay MONEY NOT NULL)
INSERT INTO @ARPay
(fgc,
arpay)
SELECT a.financialgroup_id AS fgc,
Isnull(SUM(Isnull(bp.payment_payment, 0)) +
SUM(Isnull(bp.payment_adjustment, 0)) + SUM
(Isnull(bp.payment_adjustment1, 0)), 0) AS arpay
--same as Pay_Adj_Total
FROM lkup_financialgroup a
JOIN bill_invoice bip
ON ( a.financialgroup_id = bip.financialgroup_id )
AND bip.invoice_recordstate = 0
AND bip.invoice_id <> '0000000000'
JOIN "TopsData"."dbo".bill_payment bp
ON ( bip.invoice_id = bp.invoice_id )
AND bp.payment_dateposted < {?Start Date}
AND bp.payment_recordstate = 0
AND bp.payment_id <> '0000000000'
WHERE a.financialgroup_recordstate = 0
AND a.financialgroup_id <> '0000000000'
GROUP BY a.financialgroup_id
----------
INSERT INTO @ARChg
(fgc,
archg)
SELECT fgc,
arpay
FROM @ARPay p
WHERE NOT EXISTS (SELECT * FROM @ARChg WHERE fgc = p.fgc)
----------
DECLARE @Chg TABLE (fgc CHAR(10), chg MONEY NOT NULL)
INSERT INTO @Chg
(fgc,
chg)
SELECT a.financialgroup_id AS fgc,
Isnull(SUM(Isnull(cpt.invoicecpt_feeamount, 0)), 0) AS chg
FROM lkup_financialgroup a
JOIN bill_invoice bip
ON ( a.financialgroup_id = bip.financialgroup_id )
AND bip.invoice_recordstate = 0
AND bip.invoice_id <> '0000000000'
JOIN bill_invoicecpt cpt
ON ( bip.invoice_id = cpt.invoice_id )
AND cpt.invoicecpt_postdate >= {?Start Date}
AND cpt.invoicecpt_postdate < Dateadd(DAY, 1, {?End Date})
AND ( cpt.invoicecpt_recordstate = 0
OR cpt.invoicecpt_recordstate IS NULL )
AND cpt.invoicecpt_id <> '0000000000'
WHERE a.financialgroup_recordstate = 0
AND a.financialgroup_id <> '0000000000'
GROUP BY a.financialgroup_id
----------
DECLARE @Pay_Pat TABLE (fgc CHAR(10), pay_pat MONEY NOT NULL, adj_pat MONEY NOT NULL)
INSERT INTO @Pay_Pat
(fgc,
pay_pat,
adj_pat)
SELECT a.financialgroup_id AS fgc,
SUM(Isnull(bp.payment_payment, 0)) AS pay_pat,
SUM(Isnull(bp.payment_adjustment, 0)) + SUM(
Isnull(bp.payment_adjustment1, 0))
AS adj_pat
FROM lkup_financialgroup a
JOIN bill_invoice bip
ON ( a.financialgroup_id = bip.financialgroup_id )
AND bip.invoice_recordstate = 0
AND bip.invoice_id <> '0000000000'
JOIN bill_payment bp
ON ( bip.invoice_id = bp.invoice_id )
AND bp.payment_dateposted >= {?Start Date}
AND bp.payment_dateposted < Dateadd(DAY, 1, {?End Date})
AND bp.payment_recordstate = 0
AND bp.payment_id <> '0000000000'
WHERE bp.payment_paymentby = 'P'
AND a.financialgroup_recordstate = 0
AND a.financialgroup_id <> '0000000000'
GROUP BY a.financialgroup_id,
bp.payment_paymentby
----------
DECLARE @Pay_Ins TABLE (fgc CHAR(10), pay_ins MONEY NOT NULL, adj_ins MONEY NOT NULL)
INSERT INTO @Pay_Ins
(fgc,
pay_ins,
adj_ins)
SELECT a.financialgroup_id,
SUM(Isnull(bp.payment_payment, 0)) AS pay_ins,
SUM(Isnull(bp.payment_adjustment, 0)) + SUM(
Isnull(bp.payment_adjustment1, 0))
AS adj_ins
FROM lkup_financialgroup a
JOIN bill_invoice bip
ON ( a.financialgroup_id = bip.financialgroup_id )
AND bip.invoice_recordstate = 0
AND bip.invoice_id <> '0000000000'
JOIN bill_payment bp
ON ( bip.invoice_id = bp.invoice_id )
AND bp.payment_dateposted >= {?Start Date}
AND bp.payment_dateposted < Dateadd(DAY, 1, {?End Date})
AND bp.payment_recordstate = 0
AND bp.payment_id <> '0000000000'
WHERE bp.payment_paymentby = 'I'
AND a.financialgroup_recordstate = 0
AND a.financialgroup_id <> '0000000000'
GROUP BY a.financialgroup_id,
bp.payment_paymentby
----------
SELECT lf.financialgroup_id AS fgc,
lf.financialgroup_description AS fgc_name,
Isnull(s1.archg, 0) AS archg,
Isnull(s2.arpay, 0) AS arpay,
Isnull(s3.chg, 0) AS charges,
Isnull(p2.pay_pat, 0) AS pay_pat,
Isnull(p2.adj_pat, 0) AS adj_pat,
Isnull(i2.pay_ins, 0) AS pay_ins,
Isnull(i2.adj_ins, 0) AS adj_ins,
Isnull(s2.arpay, 0) AS 'Pmts and Adjs Total'
FROM @ARChg s1
LEFT JOIN @ARPay s2
ON ( s2.fgc = s1.fgc )
LEFT JOIN (SELECT p.fgc
FROM @ARChg g
LEFT JOIN @ARPay p
ON ( p.fgc = g.fgc )) s
ON ( s.fgc = s1.fgc )
LEFT JOIN @Chg s3
ON ( s3.fgc = s1.fgc )
LEFT JOIN (SELECT c.fgc
FROM @ARChg g
LEFT JOIN @Chg c
ON ( c.fgc = g.fgc )) sc
ON ( sc.fgc = s1.fgc )
LEFT JOIN (SELECT p1.fgc
FROM @ARChg g
LEFT JOIN @Pay_Pat p1
ON ( g.fgc = p1.fgc )) sp1
ON ( s1.fgc = sp1.fgc )
LEFT JOIN (SELECT i1.fgc
FROM @ARChg i
LEFT JOIN @Pay_Ins i1
ON ( i.fgc = i1.fgc )) sp2
ON ( s1.fgc = sp2.fgc )
LEFT JOIN @Pay_Pat p2
ON s1.fgc = p2.fgc
LEFT JOIN @Pay_Ins i2
ON s1.fgc = i2.fgc
LEFT JOIN lkup_financial lf
ON ( lf.financialgroup_id = s1.fgc )
ORDER BY lf.financialgroup_description
IP Logged
