A DA report "POA Dues Payment" lists quarterly dues payment remittances for active POA members with product codes like AOA, AFF, LS, and TAYE. This report is located in in the PA folder.
Data source: USR_vwPADuesRemittance
With the VPN:
1. Change the file column name to match the below format:
2. Copy and paste the spreadsheet area to a new tab and delete the original tab in the new file.
3. Save and close the spreadsheet in G:\
4. Import spreadsheet into AOA762PROD_P360.SCRATCH: SCRATCH.dbo.USR_Import_POA_Date (change import date to newly received date)
5. Used the proc USR_spUniversalImportPARemittances to fill table USR_Universal_Import_Personify -- Note: After the VPN is disabled this step will be replaced with the new method (data services?)
6. Used the proc USR_spUploadPARemittances to fill table [aoadw].[USR_Payment_Remits]
Begin Tran
declare @Year int
declare @Quarter int
declare @RequestID varchar(10)
declare @sp_name varchar(100)
declare @Membership varchar(50)
set @Year = 2023
set @Quarter = 2
set @RequestID = '24147976'
set @sp_name = OBJECT_NAME(@@PROCID)
set @Membership = 'AOA'
INSERT INTO [aoadw].[USR_Payment_Remits]
(
[MCID]
,[OrgID]
,[RemitYear]
,[RemitQuarter]
,[Membership]
,[RemitAmount]
)
Select --top 1
[MCIDPerson] --Right('00000000' + Cast(MCID as varchar(8)),8) --
,OrgID_1
,@Year
,@Quarter
,@Membership
,isnull(r.Price_1,0) as [Amount]
--select *
FROM USR_Universal_Import_Personify r
WHERE R.RequestID = @RequestID
and r.MCIDPerson is not null
and r.Pending = 'Y'
and r.ProcessedDate is null
and not exists
(
select * from aoadw.USR_Payment_Remits i
where
r.MCIDPerson = i.MCID
and i.RemitYear = @Year
and i.RemitQuarter = @Quarter
and i. Membership = @Membership
)
Update r
set Pending = 'N', ProcessedDate =getdate(), r.ProcedureImported = @sp_name
--select *
from USR_Universal_Import_Personify r
where r.RequestID = @RequestID
and r.ProcessedDate is null
commit
--rollback