--ASN
select UpdatedTime,Id,IntegrationDetail,IsParseSuccess,IsFailed,ErrorDetail,Note from
attunity_RPRO_Web.dbo.tbRproProcIn
where IntegrationType='ASN'
and ErrorDetail<>'SUCCESSFUL'
and ErrorDetail not like '%FILE_IGNORED_BASED_ON_BUSINESS_RULES%'
and IsDeleted=0
-- and Note like 'OrderQty%'
-- and Note not like '%113015766%'
order by IntegrationDetail desc
-- -- Run the above query and give the results to Kenney to populate the PO numbers.
-- Whatever are the non DS POs they need to updated with following query (anything starting with 96 sequence are the DS POs.)
update Attunity_RPro_Web..tbRproProcIn set IsDeleted=1, note= note + ' Non-Ecomm'
where id in (11036841, 11036840, 11036839, 11036838, 11036837, 11036836, 11036835, 11036834, 11036833, 11036832, 11036830, 11036829, 11036828, 11036827, 11036826, 11036825, 11036824, 11036823, 11036822, 11036821, 11036820, 11036819, 11036818, 11036817, 11036816, 11036815, 11036814, 11036813, 11036812, 11036811, 11036810, 11036809, 11036808, 11036807, 11036806, 11036805, 11036804, 11036803, 11036802, 11036801, 11036800, 11036799, 11036798, 11036797, 11036796, 11036795, 11036794, 11036793, 11036792, 11036791)
-- for remaining one starting with 96
-- if the error column says deadlock (those will need to be re processed)
--
--Steps to reprocess ASN. DO THIS WHEN THERE ARE NO ENTRIES IN PROCIN TABLE AS WE NEED TO UPDATE MANUALLY A FEW RECORDS
--Ensure that the shipment is not processed in OD and there is no voucher in RPRO
--#1: Goto 10.34.85.71 and folder D:\INTEGRATIONS\EDI_Incoming\856ASN -- search with ASN file in all folders and pick it up from teh processed folder and cut the file and put it back in the incoming folder and rename it to just .dat
--#2: Find the ASN from the date and drop it back to Incoming folder. ensure to drop with proper file extension.
--#3: run the update Attunity_RPro_Web..tbRproProcIn set IsDeleted=1, note= note + ' To be ReProcessed' where id in ()
--#4: wait for a new entry to show in procin table with same sh filename. use query: select * from Attunity_RPro_Web..tbRproProcIn with (nolock) where IntegrationDetail in ('SH74183489097.dat')
--$5: It takes about 5-10mins to pick up, as soon as it is picked up, you should OD shipment is processed and a new voucher is generated in RTP.
select * from
attunity_RPRO_Web.dbo.tbRproProcIn
-- where id in (8952006)
where integrationdetail LIKE 'SH74326456138.dat%'
update Attunity_RPro_Web..tbRproProcIn set IsDeleted=1, note= note + ' To be ReProcessed'
where id in (11727684)
update Attunity_RPro_Web..tbRproProcIn set IsDeleted=1, note= note + ' Non-Ecomm'
where id in (11036841, 11036840, 11036839, 11036838, 11036837, 11036836, 11036835, 11036834, 11036833, 11036832, 11036830, 11036829, 11036828, 11036827, 11036826, 11036825, 11036824, 11036823, 11036822, 11036821, 11036820, 11036819, 11036818, 11036817, 11036816, 11036815, 11036814, 11036813, 11036812, 11036811, 11036810, 11036809, 11036808, 11036807, 11036806, 11036805, 11036804, 11036803, 11036802, 11036801, 11036800, 11036799, 11036798, 11036797, 11036796, 11036795, 11036794, 11036793, 11036792, 11036791)
update Attunity_RPro_Web..tbRproProcIn set IsDeleted=1, note= note + ' ManuallyProcessed'
where id in (10736451,10736450,10736449,10736448,10736447,10736446,10736445,10736444,10736458,10736457,10736456,10736455,10736454,10736453,10736452,10736436,10733871,10733868,10721089,10705609,10716383,10679570,10662139)
Extract the list from the above SQL and share with Kenney.
Kenney will populate the PO numbers against all records
Filter on ones starting with 96
search the ASN number from the row in SPS portal
get the order number and go to OD
Open order
check the SKU on it
Open related shipment
if not processed re process it uisng to be processed query